How to find next empty cell after a search word using cells.find

Miler13

New Member
Joined
Feb 5, 2017
Messages
43
So I am searching for a certain word, which is "Violation" and then filling in something from a combobox from a userform into that spot after "Violation." So if I enter "12/1/18" into the combobox in the userform, it will find the word "Violation" on the main page and then plug "12/1/18" right after the word "Violation." The problem I help with is, is that I have multiple areas for violation in the document. So lets say 5. If the first "Violation" has a date in it from before, it will find the next violation then put that date. Then the next time new info is put into the combobox it will find the "violation" after the one that 12/1/18 was entered into.

I hope that makes sense!

THanks
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Something like this should help:

Code:
Dim c As Range
Dim myAddress As Variant
 
With ActiveSheet.Cells
    Set c = .Find("Violation", LookIn:=xlValues, LookAt:=xlPart)
    If Not c Is Nothing Then
        myAddress = c.Address
        Do
            If c.Offset(1, 0).Value = "" Then
                Exit Do
                
            Else
                Set c = .FindNext(c)
            End If
        Loop While Not c Is Nothing And c.Address <> myAddress
    End If
End With

If c.Offset(1, 0).Value = "" Then
    MsgBox c.Offset(1, 0).Address
Else
    MsgBox "No available cells found"
End If
 
Upvote 0
Code:
With Sheets("LOP Log").Cells
    Set g = .Find("Break Time:", LookIn:=xlValues, lookat:=xlPart)
    If Not g Is Nothing Then
        myAddress4 = g.Address
        Do
            If g.Offset(0, 1).Value = "" Then
                Exit Do
            Else
            Set g = .FindNext(g)
            End If
        Loop While Not f Is Nothing And g.Address <> myAddress4
    End If
End With
If g.Offset(0, 1).Value = "" Then
    g.Offset(0, 1).Value = Me.ComboBox3.Value
    
Else
    MsgBox "No available cells found"
End If
[Code/]


So this is my code that works. But how can I make it so that if the dropdown box isn't filled out it wont go further until it is?
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

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