VBA - Assigning a variable to a dynamic cell value

[raivyne]

I am working in Microsoft Excel 2010 and attempting to code part a larger macro where I need to take the A cell value of one worksheet and filter another worksheet for that value. The hitch is that i need to do this multiple times over a dynamic number of rows. (Once I work out the autofilter portion, I will be doing more with each iteration of the loop).

I keep getting a compile error (Object Required) on the variable that i am attempting to assign to the current cell value (Red Text below).

Dim anum As String
Dim tick As String

Set R1 = Intersect(sh1.Range("F:F"), sh1.UsedRange)   'this will be used later in the loop
Set R2 = Intersect(sh4.Range("A:B"), sh4.UsedRange)

For rc2 = R2.Count To 2 [B][COLOR=#800080]Step [/COLOR][/B]-1
    
    [B][COLOR=#ff0000]Set anum = Cells(rc2, 1).Value
[/COLOR][/B] 
    sh1.Select
    ActiveSheet.Range("F1").AutoFilter Field:=6, Criteria1:=anum
    
Next

I have also tried removing the variable element, using the below code (resulting in a Run-time '1004': Method 'Range' of object '_Worksheet' failed - on the line in Red below):

Set R1 = Intersect(sh1.Range("F:F"), sh1.UsedRange)
Set R2 = Intersect(sh4.Range("A:A"), sh4.UsedRange)

For rc2 = R2.Count To 2 Step -1
    
    sh1.Activate
[COLOR=#ff0000][B] ActiveSheet.Range("F1").AutoFilter Field:=6, Criteria1:=sh4.Range(rc2, 1).Value
[/B][/COLOR] 
Next

i'm sure it doesn't like that i'm trying to call a value from the non-active sheet... but i am at a complete loss as to how to get around this issue.

I have also tried assigning anum As Long and As Integer, but that does not work either.

Can anyone tell me the correct way to assign a variable to the value of the A cell in the current Step of a For... Next... loop?

Thank you in advance.

 

[NeonRedSharpie]

You don't use 'Set' for a string so that is probably your issue.

 

[raivyne]

I also tried assigning anum As Long and As Integer, but I am still getting the same error. I mentioned that above, but it was at the bottom.

 

[NeonRedSharpie]

Have you tried simply removing the word "set"?

 

[raivyne]

No, but I will now... I'll post back if it doesn't work. Thanks!

 

[raivyne]

Well, I feel enormously foolish right now. Thanks so much. I've been racking my brain on this for a week now... I should have posted sooner, but I try to avoid bothering people with my VBA inadequacies... Thanks again!

 

[NeonRedSharpie]

Come on back whenever you have a question. It's why my F5 key is slowly breaking.