CODEWITHSUNDEEP
excelvba
Tariq Khalaf
Tariq Khalaf

Reputation: 89

loop and select next cell excel macro

Hello hope all is well :) having trouble figuring out how to loop and select next cell so when a cell in the range h3:z3 is empty it would stop :)

and what it is doing is selecting the value from h3 pasteing in b3 runs a another macro which gives an order number in e3 which is then copied and pasted in h4 then it would go to the next cell in I3 paste in b3 copy result from e3 and paste in I4 and do the same

thanks 

For Each cell In Range("H3:Z3")

If IsEmpty(cell.Value) Then Exit For
'select ammount and place in lookup
Range("H3").Select
Selection.Copy
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

' fill in order numbers
bulkON_Click

'select order and past under postcode
Range("E3").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Selection.Copy
Range("H4").Select
ActiveSheet.Paste

Loop

Upvotes: 0

Views: 39931

Answers (1)

David Zemens
David Zemens

Reputation: 53663

There's a lot that I would probably change in this code. This should get you started.

For starters, a For loop requires a Next statement, not a Loop (which is used for Do blocks. Also, you should avoid copying/pasting at all costs, in favor of writing values directly to the destination cell(s).

I assume that cells "B3" and "E3" are constant, and you are iterating over cells in H3:Z3 and computing some value to put in corresponding cell in H4:Z4. 

For Each Cell In Range("H3:Z3")

    If Cell.Value = vbNullString Then Exit For
    'select ammount and place in lookup
    Range("B3").Value = Cell.Value  '<< no need to "copy & paste", just write the value directly
    ' fill in order numbers
    bulkON_Click

    'insert the value under postcode
    ' this OFFSET refers to the cell 1 row below the "Cell"
    Cell.Offset(1, 0).Value = Range("E3").Value

Next

Upvotes: 2

Related Questions