Hi, in excel 2007 trying to loop through (un-fixed length) column (say C) and where row value matches (say "High"), then transfer value of cells Dx and Bx to sheet "transfer", where x is the row# where the matches are found. Assume "transfer" exists.

Do you know how to modify VBA code if we gave you a skeleton or do you need a "complete" solution written for you?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi DerbyDad; seems by way of iteration the following will do the trick: Sub test()

Dim LR As Long, i As Long

With ActiveSheet

LR = .Range("C" & Rows.Count).End(xlUp).Row

For i = 1 To LR

If .Range("C" & i).Value = "High" Then

.Range("B" & i).Copy

Sheets("Transfer").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

.Range("D" & i).Copy

Sheets("Transfer").Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

End If

Next i

End With

End Sub

As should this, but possibly in a more efficient manner. Sub UsingFind() Dim LR As Long, c As Variant With ActiveSheet LR = .Range("C" & Rows.Count).End(xlUp).Row With .Columns("C") Set c = .Find("High", lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do nxtRw = _ Sheets("Transfer").Range("B" & Rows.Count).End(xlUp).Row + 1 Sheets("Transfer").Range("B" & nxtRw) = Range("B" & c.Row) Sheets("Transfer").Range("D" & nxtRw) = Range("D" & c.Row) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstAddress End If End With End With End SubThe reason I say "more efficient" is three-fold:

By using the .Find method, the code does not have to check every cell to see if it contains "High". It simply "Finds" it.

By using nxtRw = Sheets("Transfer").Range("B" & Rows.Count).End(xlUp).Row + 1 the code only has to calculate the next open row

onceper iteration, as opposed totwiceby having to figure out the "offset" each time.By eliminating the Copy-Paste functions, it's, well, it's simply more efficient.

Granted, in such simple code, the efficiencies might not even be seen, but as a "best practice" it stills makes sense to use the most efficient methods, at least in my opinion.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

DerbyDad - much appreciated and i see your points. I am fully in favour of identifying the most efficient way and yours certainly match that description; i am grateful for your inputs and clearly even "simple" situations leaves the door open for ingenuity. :-)

Ask Your Question

Weekly Poll