MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

looping in excel

  Asked By: Cameron    Date: Jan 14    Category: MS Office    Views: 997

I am trying to search in one worksheet (column B) for all the values that is
below a certain value, and then search the value related to it (same row in
column A) in another worksheet (there are more than one of these in the second
worksheet) and copy these cells to a third worksheet. My worksheet in excel is
to big to use a normal loop - it takes to long. Are there any other way that I
can do - by for example use an array?



3 Answers Found

Answer #1    Answered By: Bellona Lopez     Answered On: Jan 14

If you could sort both sheets by the column  you're checking, your loop
wouldn't need to cover the whole range, would it? That would give you a
lot more speed. But I thnk once you have your value  from the first
worksheet (if it's sorted, then a simple Do...While should do the
trick), you shouldn't need to loop  through the second; a simple .Find
command should do it more easily and far, far more quickly.

Answer #2    Answered By: Jonathan Harrison     Answered On: Jan 14

I meant to say - since you'll have multiple matches - follow the .Find
with a Do...While wrapped around a .FindNext.

Answer #3    Answered By: Gerald Cruz     Answered On: Jan 14

whenever I deal with big  (long) XLS tables I use Filters rather than loops,
which is extremly fast. Additionally, disabling screen updating will
significantly improve execution speed. In your case it looks like the
autofilter's "custom" function (or the advanced filters) should work great.

Here is an extract of some code I use in one of my macros using the custom

Rows(1).Autofilter Field:=TRSC, Criteria1:=">=19", Operator:=xlAnd, _
Range(Cells(2, PQAC), Cells(2, PQAC).End(xlDown)).Select
Selection.Replace What:="*", Replacement:="9999999999", LookAt:=xlWhole

This filters the data in column  TRSC for values  between "19" and "27". Then I
select the column PQAC and replace it's value  by number "9999999999". rather
than replacing the value, you could just copy  the selection to a new worksheet.
You may need to select the entire column in your case though.

Didn't find what you were looking for? Find more on looping in excel Or get search suggestion and latest updates.