johnkrenkel
New Member
- Joined
- Apr 2, 2019
- Messages
- 11
Just looking for a little guidance. I have a macro that functions perfect with the exception that it is slow. I added a minor piece into the macro that counts everytime there is a string match (partial string match really)
The macro uses two FOR EACH statements
For Each Cel in Rng
str1 = cel.value
For Each cel2 in rng2
str2 = cel2.value
if instr(1, str1, str2) > 0 then
i = i+1
end if
Count.value = i
next cel2
next cel
This method seems to take forever because it is taking 1 cell, then looking down the list of sheet 2 cells and finding and counting matches. There has to be a faster method but while I am getting good at For loops, I am thinking a With loop might work better or maybe even something else.
Thanks all, looking forward to seeing some responses.
The macro uses two FOR EACH statements
For Each Cel in Rng
str1 = cel.value
For Each cel2 in rng2
str2 = cel2.value
if instr(1, str1, str2) > 0 then
i = i+1
end if
Count.value = i
next cel2
next cel
This method seems to take forever because it is taking 1 cell, then looking down the list of sheet 2 cells and finding and counting matches. There has to be a faster method but while I am getting good at For loops, I am thinking a With loop might work better or maybe even something else.
Thanks all, looking forward to seeing some responses.