How to use a variable in the address of the Range method

Question

How can I set variable in Range? This is my code:

Sub Makro1()
    Dim value As String
    ThisWorkbook.Sheets("Arkusz1").Activate
    ThisWorkbook.Sheets("Arkusz1").Range("R3").Select
    value = ThisWorkbook.Sheets("Arkusz1").Range("R3").value
    ThisWorkbook.Worksheets("Arkusz1").Range("C:value").Select '<--- Here is the BUG
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Please read this [answer on how to avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1), you do not need those select/activate 99% of the time. (In fact, it is bad practice) — Raymond Wu, Sep 22 '21 at 12:33
Hello Raymond, thanks for the informations. I promise I will :) — mean, Sep 23 '21 at 05:44

score 0 · Answer 1 · answered Sep 22 '21 at 12:21

0

Untested

Consider:

Sub Makro1()

Dim valuee As String

ThisWorkbook.Sheets("Arkusz1").Activate
ThisWorkbook.Sheets("Arkusz1").Range("R3").Select

valuee = ThisWorkbook.Sheets("Arkusz1").Range("R3").value

ThisWorkbook.Worksheets("Arkusz1").Range("C" & valuee).Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
End Sub

answered Sep 22 '21 at 12:21

Gary's Student

95,722
10
59
99

ThisWorkbook.Worksheets("Arkusz1").Range("C" & valuee).Select <--------- Here is Bug again. I don't have any ideas how to correct it. – mean Sep 23 '21 at 05:43

score 0 · Accepted Answer · answered Sep 22 '21 at 14:43

0

For ranges the format is: startColumn & StartRow & ":" & EndColumn & EndRow EndColumn and EndRow are optional is not specified then they will be the same as Startcolumn and StartRow for example to reference a range from column A, row 1 to Column D, Row 20 use:

sAddress = "A1:D20"

answered Sep 22 '21 at 14:43

Alfredo Holguin

41
2

Thanks for informations. But I have a question. How can I declare variable in this range? – mean Sep 23 '21 at 05:40
for example you would use: sAddress = "A1:D20" ThisWorkbook.Sheets("Arkusz1").Range(sAddress).Select is the same as: ThisWorkbook.Sheets("Arkusz1").Range("A1:D20").Select or: sAddress = "R3" ThisWorkbook.Sheets("Arkusz1").Range(sAddress).Select is the same as: ThisWorkbook.Sheets("Arkusz1").Range("R3").Select if this helps you, mark it as thumps up, thanks – Alfredo Holguin Sep 23 '21 at 14:04

How to use a variable in the address of the Range method

2 Answers2