CODEWITHSUNDEEP
vbaexcelloops
David Newman
David Newman

Reputation: 23

VBA loop through empty columns

I am stuck on how to complete a loop with a certain result.

I have attached a screen shot of what i basically want to happen.

The VBA would start off in B1 i want the VBA to loop through empty columns to the right until it gets to any character/string(the result can appear in any cell to the right of B1,and is a variable word.) Once it finds a cell with any characters in, i want to copy it and move it to B5. I have the following code, and it nearly does what i want apart from the fact it just keeps looping.

Code:

Sub looptillnotempty()
    Dim notempty As Boolean
    notempty = False
    Do Until notempty
        If IsEmpty(ActiveCell) = True Then
            ActiveCell.Offset(0, 1).Select
        Else
            ActiveCell.Copy
            Range("C5").PasteSpecial Paste:=xlPasteFormulas
        End If
    Loop
    Range("C8").Select
End Sub

What I am trying to accomplish::

enter image description here

Upvotes: 2

Views: 181

Answers (2)

user3598756
user3598756

Reputation: 29421

You can go like follows

Sub looptillnotempty()
  With ActiveSheet.Range("B1").End(xlToRight)
      If .Value <> "" Then ActiveSheet.Range("B5").Formula = .Formula
  End With
  Range("C8").Select
End Sub

Just choose which cell to copy found cell formula into, since in your question text you wrote "B5", in your code example you wrote "C5" and in the screenshot it seems to be "A5"...

Upvotes: 2

Abhinav
Abhinav

Reputation: 2085

You can exit the loop with Exit Do as follows:

Sub looptillnotempty()
    Dim notempty As Boolean
    notempty = False
    Do Until notempty
        If IsEmpty(ActiveCell) = True Then
            ActiveCell.Offset(0, 1).Select
        Else
            ActiveCell.Copy
            Range("B5").PasteSpecial Paste:=xlPasteFormulas
            Exit Do
        End If
    Loop
    Range("C8").Select
End Sub

Upvotes: 2

Related Questions