finding corresponding row
 

In Sheet A of data, in Column B, I have a year (say, 2003). For each row
(and year) in Sheet A, I'm trying to get the macro to find the row of the
corresponding (identical) year on Sheet B, then copy the appropriate data
box for that row, and enter it into an equation with a data box from the
initial row in Sheet A. I'm having a difficult time figuring how how to get
the macro to do that recognition process, and then to capture the right
cell. Can anyone please help with this? Thanks.

Best,
Luke

finding corresponding row
 

Hi,
This should get you started. You may need to change 'MatchRng' to
correspond to the column with the year data.

Pass the Year from SheetA as the parameter . If no match is found it
returns 0.

e.g Call Findrow(Range("B3").value)

HTH

Function FindRow(ByVal MatchVal As Integer)
Dim Lastrow As Integer, mRow As Integer, Matchrng As Range, c As Variant

With Worksheets("SheetB")
Lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
Set Matchrng = .Range("b2:B" & Lastrow) ' Change as required
Set c = Matchrng.Find(MatchVal, LookIn:=xlValues) ' Change as required
If c Is Nothing Then
MsgBox MatchVal & " not found"
FindRow = 0
Else
FindRow = c.Row
End If
End With

End Function

"Lucas Tate" wrote:

In Sheet A of data, in Column B, I have a year (say, 2003). For each row
(and year) in Sheet A, I'm trying to get the macro to find the row of the
corresponding (identical) year on Sheet B, then copy the appropriate data
box for that row, and enter it into an equation with a data box from the
initial row in Sheet A. I'm having a difficult time figuring how how to get
the macro to do that recognition process, and then to capture the right
cell. Can anyone please help with this? Thanks.

Best,
Luke

finding corresponding row
 

Sorry ..

Should be MyRow=FindRow(Range("B3").value)


"Toppers" wrote:

Hi,
This should get you started. You may need to change 'MatchRng' to
correspond to the column with the year data.

Pass the Year from SheetA as the parameter . If no match is found it
returns 0.

e.g Call Findrow(Range("B3").value)

HTH

Function FindRow(ByVal MatchVal As Integer)
Dim Lastrow As Integer, mRow As Integer, Matchrng As Range, c As Variant

With Worksheets("SheetB")
Lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
Set Matchrng = .Range("b2:B" & Lastrow) ' Change as required
Set c = Matchrng.Find(MatchVal, LookIn:=xlValues) ' Change as required
If c Is Nothing Then
MsgBox MatchVal & " not found"
FindRow = 0
Else
FindRow = c.Row
End If
End With

End Function

"Lucas Tate" wrote:

In Sheet A of data, in Column B, I have a year (say, 2003). For each row
(and year) in Sheet A, I'm trying to get the macro to find the row of the
corresponding (identical) year on Sheet B, then copy the appropriate data
box for that row, and enter it into an equation with a data box from the
initial row in Sheet A. I'm having a difficult time figuring how how to get
the macro to do that recognition process, and then to capture the right
cell. Can anyone please help with this? Thanks.

Best,
Luke