代码之家 › 专栏 › 技术社区 › Selvam

使用变量表示范围内的列字母

range excel-vba vba excel

1

Selvam · 技术社区 · 7 年前

代码片段:

Set A = Workbooks("SwbA").Worksheets("SwsA").Range("A1:A10")
Set B = Workbooks("twbB").Worksheets("twsB").Range("G1:G10")
A.value = B.value

我想声明一个变量,例如

Dim col AS STRING
col = "A"

所以我可以做一些这样的事情-

Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&1:col10)

col = "A1:A10" '--> this works, for other reasons I want to manipulate Column letter only

2 回复 | 直到 7 年前

1

0

Pá´Ê 7 年前

我看不到你的形象,但你可以

 Dim Col As String
 Col = "A"
 Dim r As Range
 Set r = Range(Col & "1:" & Col & "10")

2

0

VBasic2008 7 年前

专栏文章

信或数字 . 这个 Cells 属性是合适的。

行数

Sub NumberOfRows()

  Const cW1 As String = "twbB"  ' Source Sheet or Book
  Const cW2 As String = "SwbA"  ' Target Sheet or Book

  Const cCol1 As Variant = "G"  ' Source Column Letter/Number
  Const cCol2 As Variant = "A"  ' Target Column Letter/Number
  Const cFirst As Long = 1      ' First Row
  Const cLast As Long = 10      ' Number of Rows

  Dim rngB As Range             ' Source Range
  Dim rngA As Range             ' Target Range

  Set rngB = Workbooks(cW1).Worksheets(cW1).Cells(cFirst, cCol1).Resize(cLast)
  Set rngA = Workbooks(cW2).Worksheets(cW2).Cells(cFirst, cCol2).Resize(cLast)

  rngA.Value = rngB.Value

End Sub

Sub LastRow()

  Const cW1 As String = "twbB"  ' Source Sheet or Book
  Const cW2 As String = "SwbA"  ' Target Sheet or Book

  Const cCol1 As Variant = "G"  ' Source Column Letter/Number
  Const cCol2 As Variant = "A"  ' Target Column Letter/Number
  Const cFirst As Long = 1      ' First Row
  Const cLast As Long = 10      ' Last Row

  Dim rngB As Range             ' Source Range
  Dim rngA As Range             ' Target Range

  Set rngB = Workbooks(cW1).Worksheets(cW1).Range(Cells(cFirst, cCol1), Cells(cFirst, cCol1))
  Set rngA = Workbooks(cW2).Worksheets(cW2).Range(Cells(cFirst, cCol2), Cells(cFirst, cCol2))

  rngA.Value = rngB.Value

End Sub