Variable in Range VBA

user_12767247 0
Viewed 40

Is it possible to use a variable in Range?

I have some cells called test1, test2, etc

Range("test1") works like Range("A1"), but Range(variable) or Range("variable") does not work.

I want to set a variable cellName and when I loop through test1, test2, the cells called test1, and test2, does something.

Dim cellName as String
Dim rng As Range, cell As Range

Set rng = Range("C5:C8")

For each cell In rng
  cellName = cell.Value
  Worksheets("Sheet2").Range("cellName").Value = "Good"
Next cell
vba excel
2 Answers

Try:

Sub test()
Dim cellName As String
Dim rng As Range

For Each rng In Range("C5:C8")
  cellName = rng.Value
  Worksheets("Hoja1").Range(cellName).Value = "Good"
Next rng

Set rng = Nothing


End Sub

enter image description here

enter image description here

user_91998280

If you want to modify cells in C5:C8 you just need to do :

Dim cellName as String
Dim rng As Range, cell As Range

Set rng = Range("C5:C8")

For each cell In rng
  cell = "Good"
Next cell
user_50771680
Related