Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding 1st,2nd,3rd etc | Excel Discussion (Misc queries) | |||
Finding the value of x | Excel Worksheet Functions | |||
Finding Max or Min Value | Excel Discussion (Misc queries) | |||
Finding value | Excel Discussion (Misc queries) | |||
Finding max value | Excel Programming |