variable not defined

[earp_]

Hi,
I have this structure:

Sub macro()

Dim columnSheet2 As range


With ThisWorkbook
Set valueCell = .Sheets("sheet1").range("B2")
Set timeCell = .Sheets("sheet1").range("J2")
End With

If timeCell.Text <> "#N/A" Then


myHour = Hour(timeCell)
myMinute = Minute(timeCell)

'#######################
If (myHour = 5) Then
If (myMinute = 0) Then
Set columnSheet2 = A65536

...

if I run it it gives me an error here

Set columnSheet2 = A65536

saying variable not defined, why?

 

[Richard Schollar]

Hi

Where have you defined A65536? VBA is interpreting it as a variable - did you mean it to be a range reference (in which case you need to wrap it around with Range("A65536"))

 

[RoryA]

Because I guess you mean:

Set columnSheet2 = Activesheet.Range("A65536")

 

[earp_]

I put

Set columnSheet2 = A65536

because in a loop i have this

Worksheets("Sheet2").Range("AW65536").End(xlUp).Offset(1, 0).PasteSpecial xlValues

and I want to change at every loop this

Range("AW65536")

any help?

And for this i mean the value inside the " "

 

[RoryA]

You can use:

Cells(Rows.Count, "A")

or:

Cells(Rows.Count, 1)

depending on what your loop is.

 

[earp_]

Thanks but i need to put this value

A65536

inside a variable, because after i want to put that variable
here

Worksheets("Sheet2").Range("myVARIABLE").End(xlUp).Offset(1, 0).PasteSpecial xlValues

because there are severals IFs
sometimes it can be

A65536

sometimes

B65536

sometimes

AB65536

 

[RoryA]

You could do that with what I suggested, just change the column letter or number. But based on what you posted originally, you want:

Dim columnSheet2 as string
' other code here
columnSheet2 = "A65536"
'...
Worksheets("Sheet2").Range(columnSheet2).End(xlUp).Offset(1, 0).PasteSpecial xlValues

 

[earp_]

In this way it underlines

columnSheet2 =

saying "object required"
​

 

[earp_]

Sorry my error i left the word "Set"

 

[RoryA]

You have to change the declaration of columnSheet2 so it is declared as String not as a Range as you had it before. Then you do not use the word Set.