I have a macro which does two parts:
1. Runs through each row of my table and checks if the row is a match to some entries by the user. It then counts and displays the number of matches:
2. The next step is that for each line that is a match, the macro actually changes a value in that table row of a specified column. Currently it repeats much of number 1:
The thing is, I need these two to be able to be run differently so that the user can see how many records are impacted without going to the next step and changing the values. I feel there must be a more efficient way as I'm checking every row twice. Can an Array store each row number of a match and then use that in #2 - and would that be more efficient? My table could have up to 60,000-70,000 rows, so I also wasn't sure how many values an array can handle.
1. Runs through each row of my table and checks if the row is a match to some entries by the user. It then counts and displays the number of matches:
Code:
For i = 1 to MyTable.Rows.Count
'If row is a match
Counter = Counter + 1
Next i
Msgbox "x lines match your criteria"
2. The next step is that for each line that is a match, the macro actually changes a value in that table row of a specified column. Currently it repeats much of number 1:
Code:
For i = 1 to MyTable.Rows.Count
'If row is a match
Change value of ListColumn
Next i
Msgbox "x lines match your criteria"
The thing is, I need these two to be able to be run differently so that the user can see how many records are impacted without going to the next step and changing the values. I feel there must be a more efficient way as I'm checking every row twice. Can an Array store each row number of a match and then use that in #2 - and would that be more efficient? My table could have up to 60,000-70,000 rows, so I also wasn't sure how many values an array can handle.
Last edited: