Help with VBA loop

J

JRidge

Board Regular
Joined
Sep 10, 2013
Messages
106
Hi All,

I have this code...

Code: 
Dim rgFoundCell As Range    
Const strFindMe As String = "Select Seprations"
     
Application.ScreenUpdating = False
With Sheet5
        Set rgFoundCell = .Range("F:F").Find(what:=strFindMe)
        Do Until rgFoundCell Is Nothing
            
        Set rRange = rgFoundCell.Offset(1)
        If rRange.Value = "No" Then..............................Doing things in here

        Set rgFoundCell = .Range("F:F").FindNext        
        Loop
        End With
    
End Sub

The code is working in that it finds the first instance of strFindMe, Does everything i want it to do..

When it loops it then finds the same first instance and not the next..

Any ideas why??

Any Help would be appreciated.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Try...

Code: 
Set rgFoundCell = .Range("F:F").FindNext(rgFoundCell)

Hope this helps!
 
Upvote 0
Unless the code changes the value of the found cell, though, you'll still loop around to the same cell again.

Code: 
Sub JR()
    Const strFindMe As String = "Select Seprations"
    Dim rLook       As Range
    Dim rFind       As Range
    Dim sAddr       As String

    Set rLook = Sheet5.Range("F:F")
    Set rFind = rLook.Find(what:=strFindMe)

    If rFind Is Nothing Then
        MsgBox "Not found"

    Else
        sAddr = rFind.Address
        Do
            If rFind.Offset(1).Value = "No" Then
                ' code that does NOT destroy rFind
            End If
            Set rFind = rLook.FindNext(After:=rFind)
        Loop While rFind.Address <> sAddr
    End If
End Sub
 
Upvote 0
Wow, Just about perfect :)

@shg:

Code: 
Const strFindMe As String = "Select Seprations"    Dim rLook       As Range
    Dim rFind       As Range
    Dim sAddr       As String


    Set rLook = Sheet5.Range("F:F")
    Set rFind = rLook.Find(what:=strFindMe)


    If rFind Is Nothing Then
    Exit Sub


    Else
        sAddr = rFind.Address
        Do
            If rFind.Offset(1).Value = "No" Then
[COLOR=#ff0000]    How would i say Find next instance or next in Loop if value is "No"[/COLOR]
    
    End If

This code is perfect except that if the Value is "No" then it should Loop to find the next Instance....

Regards

JRidge
 
Upvote 0
Then change

Code: 
If rFind.Offset(1).Value = "No" Then

to

Code: 
If rFind.Offset(1).Value <> "No" Then
 
Upvote 0
Hmm... I Have:

Code: 
Const strFindMe As String = "Select Seprations"    Dim rLook       As Range
    Dim rFind       As Range
    Dim sAddr       As String


    Set rLook = Sheet5.Range("F:F")
    Set rFind = rLook.Find(what:=strFindMe)


    If rFind Is Nothing Then
       Exit Sub


    Else
        sAddr = rFind.Address
        Do
            If rFind.Offset(1).Value <> "No" Then
        
    
    End If

Then what it does if the Value is Not "No"...

Code: 
Rng = rFind.Offset(1) - 1    For k = 1 To Rng
    Set rRange = rFind.Offset(2)
        Rows(rRange.Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Next


Then at the End:

Code: 
Set rFind = rLook.FindNext(After:=rFind)        Loop While rFind.Address <> sAddr
    End If
    
End Sub

But if the Value is "No" i need it to skip the middle part and loop to the next Instance...
 
Upvote 0
The code you posted doesn't compile.

Could you explain in words and sentences what you're trying to do?
 
Upvote 0
I have this code i am working on:

It searches for the first instance of "Select Seprations" in Column F,

Once found it offsets to the cell under it.

Code: 
Sub Makro4()    Dim Rng As Integer
    Dim k As Integer
    Dim rRange As Range
    
    Const strFindMe As String = "Select Seprations"
    Dim rLook       As Range
    Dim rFind       As Range
    Dim sAddr       As String


    Set rLook = Sheet5.Range("F:F")
    Set rFind = rLook.Find(what:=strFindMe)


    If rFind Is Nothing Then
       Exit Sub


    Else
        sAddr = rFind.Address
        Do
            If rFind.Offset(1).Value <> "No" Then

If this Cell has "No" in it i would like it to find the next Instance of "Select Seprations" in column F and so on.

If the Cell Has a Number in then it Inserts that amount of rows under the cell and formats them etc.. This all works..

Code: 
Rng = rFind.Offset(1) - 1    For k = 1 To Rng
    Set rRange = rFind.Offset(2)
        Rows(rRange.Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Next
    rFind.Offset(1).Select
    ActiveCell.Offset(0, -4).Select
    Range(ActiveCell, ActiveCell.Offset(Rng, 0)).MergeCells = True
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection
        .VerticalAlignment = xlCenter
    End With
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0.................................

Once it has done this it should find the next instance of "Select Seprations" in Column F

Code: 
Set rFind = rLook.FindNext(After:=rFind)        Loop While rFind.Address <> sAddr
    End If
    
End Sub

If it finds one then goes through process again.

If it finds no more the end Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Add total row with a loop
Replies
4
Views
121
gberg
G
J
  • Solved
multiple values
Replies
2
Views
173
JOEE1979
J
F
  • Solved
Replacing multiple instances of text file lines
Replies
2
Views
72
frateg8r
F
Y
  • Solved
Error setting value of variable1 to value of variable2
Replies
2
Views
129
Yamezz
Y
A
  • Question
VBA Highlight cell if contains specific text
Replies
4
Views
113
DanteAmor
DanteAmor

Forum statistics

Threads
1,214,591
Messages
6,120,428
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top