Macro to find specific row on database/worksheet

[Ingo]

I don 't have any VBA knowdledge, therefore I'm using a simple macro somebody
wrote to add a user form to my worksheet. The macro looks up the next empty
row on a pre-defined worksheet and writes the user input into a pre-defined
column of the empty row. It looks like this:

'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'copy the data to the database
With ws
.Cells(lRow, 2).Value = Me.cboPeriod.Value

Now I'd like to change the macro so that instead of finding the first empty
row in the database it should find the specific row where it finds let 's say
the text "year" in column 1 and then write back the user input into column 2
of the same row. Thanks for your help!

 

[Ron de Bruin]

Try this for column A in the sheet named "Sheet1"

Sub Find_First()
Dim FindString As String
Dim Rng As Range

FindString = "year"

With Sheets("Sheet1").Range("A:A")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Rng.Offset(0, 1).Value = Me.cboPeriod.Value
Else
MsgBox "Nothing found"
End If
End With
End Sub

 

[Rick Rothstein \(MVP - VB\)]

First off, if you want us to tell you how to change your existing macro, you
should post all of its code (so we will know if something we do "steps" on
another part of your code or not). Second, what if the word "year" appears
more than once in Column A... Do you want each one of their entries in
Column B replaced? Only the first one? Only the last one? What if there is
already an entry in Column B for the cell in Column A with the word "year"
in it... Do you want it overwritten? Or only put the entry in if Column B's
cell is blank?

Rick

 

[Ingo]

Hi Ron,

thanks for your help, this macro does exactly what I wanted.

Hi Rick,

thanks for the hints. I will try to specify more detailed what I'm looking
for.