Automate a macro - help please!

Posted by Frank Krumm on November 26, 2001 1:03 PM

I have a macro that I use to evaluate a list of text entries. I run the macro for each entry and the results are placed into another cell. The list of entries is growing rapidly and doing it one-by-one is far from efficient.

Please help me to develop the macro that will let me run the whole list, evaluate each entry, and record the results. For example, if "ABC" and "DEF" are entries, and they evaluate to true, then I would like each of these entries to be placed into individual cells.

Here is the code I have so far:

Dim S
S = Selection.Address
'eval formula'
Range(S).Offset(1,0).Select

Thanks

Posted by Juan Pablo on November 26, 2001 1:12 PM

You normally don't have to "select" or activate anything... for example this macro:

Sub Test1()
Range("A1").Select
While ActiveCell &LT;&GT; ""
ActiveCell.Offset(1).Select
Wend
End Sub

would be MUCH more slower than this one:

Sub Test2()
Dim i as Long
i = 1
While Cells(i,1)&LT;&GT;""
i=i+1
Wend
End Sub

Basically because Excel doesn't have to update the screen, select the cell, and do a lot of stuff. Instead it just uses counters (i), and can run a lot faster, in fact, with the second one you wouldn't need to turn ScreenUpdating Off, like the first one (Which would help as for speed matters..).

Hope this makes some sense

Juan Pablo

End Sub

Posted by Frank Krumm on November 26, 2001 1:17 PM

Thank you Juan Pablo, I will try both and and also try to learn from both. Thanks again.