CODEWITHSUNDEEP
vba
Jonathan
Jonathan

Reputation: 315

VBA, For Each Loop, incrementing offset by 1 column

I have a for each loop, how can I incrementally increase my cells output by one column? Currently this just writes to one cell in output.

Sub quarterly()

Dim result As String

rev = Sheets("fin").Range("B105:F105")

For Each x In rev:

    result = Replace(x, "M", "")
    Sheets("output").Cells(2, 2) = result

Next


End Sub

Upvotes: 0

Views: 477

Answers (2)

DisplayName
DisplayName

Reputation: 13386

if your "target" range is empty then you could use End(xlToLeft) and step one cell right at any cell writing :

Sub quarterly()    
    Dim x, rev

    rev = Sheets("fin").Range("B105:F105").Value        
    With Sheets("output")
        For Each x In rev            
            .Cells(2, .Columns.Count).End(xlToLeft).Offset(, 1) = Replace(x, "M", "")
        Next
    End With    
End Sub

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152465

You can use a counter:

Sub quarterly()

dim j as long
j = 0

dim rev as range
rev = workSheets("fin").Range("B105:F105")

dim x as variant
For Each x In rev
    Dim result As String
    result = Replace(x, "M", "")
    Sheets("output").Cells(2, 2+j) = result
    j=j+1
Next


End Sub

Upvotes: 3

Related Questions