CODEWITHSUNDEEP
vbaexcel
Rob
Rob

Reputation: 85

Until Loop Checking a Cell Isn't Empty in VBA?

Currently I have code that checks if the cell to the right of the first ")" found is empty and if it isn't, it shifts the "A" column down. I can't seem to figure out how to make this keep running until the cell to the right of the paren is empty. Any help would be appreciated. Thanks.

Sub SeekParen()
    Dim C As Range, wheree As Range, whatt As String
    whatt = ")"
    Set C = Range("A1:A10")
    Set wheree = C.Find(what:=whatt, after:=C(1)).Offset(0, 1)
    If Not IsEmpty(wheree.Address(0, 0)) Then
        Range("A2").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    End If
End Sub

Upvotes: 0

Views: 101

Answers (1)

Sgdva
Sgdva

Reputation: 2800

Does this work?

Sub SeekParen()
Dim C As Range, wheree As Range
Dim whatt As String
Dim TotalCycle As Long, CounterCycle As Long
whatt = ")"
Set C = Range("A1:A10")

Set wheree = C.Find(what:=whatt, after:=C(1)).Offset(0, 1)
TotalCycle = Application.WorksheetFunction.CountIf(C, whatt)
For CounterCycle = 1 To TotalCycle
If wheree.Value <> "" Then
Range("A2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Set wheree = C.Find(what:=whatt, after:=C(wheree.Row)).Offset(0, 1)
Else
Exit For
End If
Next CounterCycle
End Sub

Upvotes: 1

Related Questions