PDA

View Full Version : [SOLVED] Using an array to hold criteria



compariniaa
07-24-2006, 07:32 AM
say I want to delete/select/whatever certain rows of cells

I have an array with 4 items. the four items are product1, product2, product3, product4

If I want to do something (let's say copy) to all of the rows containing one of those four products, how would I go about doing that?

acw
07-24-2006, 07:11 PM
Hi

See if this gets you going. It will cycle through the entries in the array, and if it finds a match, it will put DONE SOMETHING HERE in column A for that match.



Sub aaa()
Dim FindIt As Range
Dim ProdArr As Variant
ProdArr = Array("product1", "product2", "product3", "product4")
For i = LBound(ProdArr) To UBound(ProdArr)
Set FindIt = Cells.Find(what:=ProdArr(i))
If Not FindIt Is Nothing Then
firstadd = FindIt.Address
Do
Cells(FindIt.Row, 1).Value = "DONE SOMETHING HERE"
Set FindIt = Cells.FindNext(FindIt)
Loop Until FindIt.Address = firstadd
End If
Next i
End Sub



Tony

jindon
07-24-2006, 07:38 PM
Tony,

I'm afraid that your code possibly go into endless loop when the firstadd is in Col.A.

I usually do not add the line of FindIt Is Nothing, however you need it in your case,
becaseu you are changing the value of possible first found cell.


Loop Unitl FinIt.Address = firstadd Or FindIt Is Nothing

acw
07-24-2006, 09:06 PM
Jindon

Fair enough. I usually don't use the generic cells.find either, but given the lack of OP information on structure, I just gave some starting code.

Tony

compariniaa
07-25-2006, 07:09 AM
Tony, that's what I needed! Thanks, and sorry about the poor description. Coming back to it now I can see how it was a little vague.