Hi,
I am sure that this is a simple answer but after a lot of googling and reading through threads I still cannot get it to work. I am trying to search through a number of ranges in a loop and populate an multi-array if the range includes the value I am looking for. The next part all works ok but stuck with the first part of the loop to pickup the ranges.
I have tried different ways of doing it but I seem to be getting a run-time '1004' error on picking up the search range
The issue is:
I have tried the following but get runtime error '424' object required:
The following works but then I cannot loop through:
I am guessing that it is something to do with the mix of text and numbers but I cannot get it working with converting.
As I am new to vba so I am very prone to simple errors, therefore before I start changing it (populate multi-dimension array rather than multiple ranges) I was hoping that there is a simple answer!
I have put a simplified version below.
I am sure that this is a simple answer but after a lot of googling and reading through threads I still cannot get it to work. I am trying to search through a number of ranges in a loop and populate an multi-array if the range includes the value I am looking for. The next part all works ok but stuck with the first part of the loop to pickup the ranges.
I have tried different ways of doing it but I seem to be getting a run-time '1004' error on picking up the search range
The issue is:
VBA Code:
Set Rng_search = Range(box(x) & i)
I have tried the following but get runtime error '424' object required:
VBA Code:
Set Rng_search = box(x) & i
The following works but then I cannot loop through:
VBA Code:
Set Rng_search = Mon_r1
I am guessing that it is something to do with the mix of text and numbers but I cannot get it working with converting.
As I am new to vba so I am very prone to simple errors, therefore before I start changing it (populate multi-dimension array rather than multiple ranges) I was hoping that there is a simple answer!
I have put a simplified version below.
VBA Code:
Dim i As Long, x As Long, y As Long
Dim day_select As Variant
Dim Rng_search As Range
day_select = Array("Mon_r", "Tues_r", "Weds_r", "Thurs_r", "Fri_r", "Sat_r", "Sun_r")
'included for testing - each range is set as 'public' in another module and populated in from the activesheet before calling the next module. Also includes Wed_, Thurs_ etc.
Invoice_Num = 1
Set Mon_r1 = ActiveSheet.Range("B9:B14"): Set Mon_r2 = ActiveSheet.Range("C9:C14"): Set Mon_r3 = ActiveSheet.Range("D9:D14"): Set Mon_r4 = ActiveSheet.Range("E9:E14")
Set Mon_r5 = ActiveSheet.Range("F9:F14"): Set Mon_r6 = ActiveSheet.Range("G9:G14"): Set Mon_r7 = ActiveSheet.Range("H9:H14")
Set Tues_r1 = ActiveSheet.Range("B18:B23"): Set Tues_r2 = ActiveSheet.Range("C18:C23"): Set Tues_r3 = ActiveSheet.Range("D18:D23"): Set Tues_r4 = ActiveSheet.Range("E18:E23")
Set Tues_r5 = ActiveSheet.Range("F18:F23"): Set Tues_r6 = ActiveSheet.Range("G18:G23"): Set Tues_r7 = ActiveSheet.Range("H18:H23")
Invoice_Num = 1 'again this is set elsewhere from a [collection].count and can go up to 100.
For x = 0 To 6 'picks up the text from the day_select array
For i = 1 To 7 'for periods in the day
Set Rng_search = Range(day_select(x) & i)
y = 0 ' just for testing
For Each cell In Rng_search
If cell.Value = List(0, Invoice_Num) Then 'looks up value in an pre-populated array
y = 1 'just for testing
End If
Next cell
msgbox y 'just for testing
Next i 'move to next period, day etc.
Next x 'move to next period, day etc.