How to avoid loops

[niko79542]

Hi. I just learned how to use For Each...Next loops to replace If...Then loops

Since it is best to avoid loops, I am looking for a way to solve this problem using VBA, without a loop.

Book2.xlsm

There is a simple code inside.

1) Column C is a direct copy of Column B.
2) For every cell in Column B which contains '0' I need to copy the corresponding cell from Column A and paste it in Column C
3) For example: if cell B61 = 0, copy A61, paste in C61

----------------------------------------------------

Here is the code from the xlsm file. YES, there is a reason I am not just doing =IF(B1=0, C1=A1). There are about a million other things I have to do if B = 0 and I am 99% sure I cant do them using built in Excel functions.

Option Explicit

Sub macro1()

Dim i As Integer
i = 0
Dim target, cell As Range
Set target = Range("b4:b3234")

For Each cell In target
    If cell.Value = i Then
        Cells(cell.Row, 3) = Cells(cell.Row, 1)
    End If
Next cell

End Sub

Once again, looking for a faster alternative to loops. Thx, Niko

[fredlo2010]

But this is a very fast macro isn't it?

[niko79542]

@fredlo2010

This is confirmed. however my post is only an example. Theres gonna be about 10 other things I have to do when B=0, and then repeat the procedure enough times that I would benefit from a faster method.

niko

[Richard Buttrey]

Hi,

One way

Sub CopyCells()
    Dim l As Long
    l = Range("A" & Rows.Count).End(xlUp).Row
    Range("D1") = 1
    Range("D1").DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=l
    Range("A1").CurrentRegion.Sort key1:=Range("B1")
    l = WorksheetFunction.Match(1, Range("B:B"))
    Range("A" & 1 & ":A" & l).Copy Destination:=Range("C1")
    Range("A1").CurrentRegion.Sort key1:=Range("D1")
    Range("D:D").EntireColumn.Delete
End Sub

[niko79542]

@Richard Buttrey

Hi, Could you explain a bit? I am so lost after you started using column D.

Niko

[niko79542]

Haha, nvm I got it.

I'm a dummy. I used step into and figured it out.

thx Richard.

[Richard Buttrey]

Hi,

On second thoughts rather than delete the existing column D which may have data in, it may be better to first insert a new one as the first line of code

Range("D:D").Entirecolumn.Insert

then the final line of code is only deleting something the macro has added.

[fredlo2010]

Hi Richard.

You are using a filter right?

[niko79542]

Good point, Thx