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).
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):
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.
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).
Code:
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):
Code:
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.