CODEWITHSUNDEEP
excelvbafor-loop
Paul Omeara
Paul Omeara

Reputation: 3

How to run loop downward until cells to the right are empty - VBA excel

I am running a loop that enters a formula, copies it, then goes to the next line, checks if the 2 cells to the right are empty, if they are, then do nothing. If they contain anything at all, then paste formula (word "true" in below example) from B10.

However it pastes the formula, then stops. It doesn't keep looping down until the cells to the right are empty?

Any idea's on how to fix this would be appreciated!

Sub Macro1()

Range("B10").Select
ActiveCell.Value = "TRUE"
Range("B10").Select
Selection.Copy

Range("B11").Select

Dim cell As Range
For Each cell In ActiveSheet.Range("B11:B150")

If IsEmpty(cell.Offset(0, 3).Value) = True Then
cell.Value = ""

Else: ActiveSheet.Paste

End If

Next cell

End Sub

Upvotes: 0

Views: 57

Answers (1)

urdearboy
urdearboy

Reputation: 14580

This should do... notice that you do not need .Select

Sub Test()

Dim ws as Worksheet: Set ws = ThisworkBook.Sheets("Sheet1")

Dim myCell as Range

For Each myCell in ws.Range("B10:B150")
    If myCell.Offset(,1) <> "" and myCell.offset(,2) <> "" Then
        myCell = "TRUE"
    End If
Next myCell

End Sub

Upvotes: 1

Related Questions