Find ROW # and GoTo that ROW

[SirPopsALot]

Hello,
I am new to VBA and need help!

I have a price list with over 4000 items. I have it set up with the top 11 rows frozen. There I enter a part number into cell R1 and it pulls that items price up and calculates my markup ETC. That all works fine. But I also need to find out the row number that the part number from R1 is in and go to that row is I can compare that item with the ones above and below it. There are 22 rows displayed below my frozen 11 rows and I would like for the part numbers row that I enter into R1 be displayed in the middle of that 22 displayed rows. The part numbers are located in D12:D4083.

 

[Caleeco]

Hey SirPopsALot,

The formula below should return the ROW number of the entry in Cell R1

=MATCH(R1,D1:D29,0)

I couldnt quite understand the last part of your request. Could you share an image with the expected output?

Kind regards
Caleeco

 

[SirPopsALot]

The Part Number data is between cell D12 and D4100 so your formula is 11 rows off.
I attached a picture of my spreadsheet to this reply.
After I find the ROW number with your formula I want Excel to display that ROW and the 10 rows above and 10 rows below it so I can compare prices with other items that are similar to the item I listed in R1.

 

[fhqwgads]

i'm not to sure how MATCH works exactly but if =MATCH(R1,D1:D29,0) is giving you the row number off by 11 rows then maybe try

=HYPERLINK("#"&MATCH(R1,D1:D29,0)-11&":"&MATCH(R1,D1:D29,0)-11,"link")

 

[SirPopsALot]

OK, I got the right row. Now how do I get the spreadsheet to go to that row and display it?

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this Worksheet_Change event code. Every time you enter (or remove) a part number in cell R1, the bottom part of your screen should display the region you are interested in (or return to the top of the list if R1 is cleared or an invalid part is entered).

To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by changing R1
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rPart As Range
  Dim sPart As String
  Dim lScrollRow As Long
  
  If Not Intersect(Target, Range("R1")) Is Nothing Then
    sPart = Range("R1").Value
    lScrollRow = 12
    If Len(sPart) > 0 Then
      Set rPart = Range("D12:D" & Rows.Count).Find(What:=sPart, LookAt:=xlWhole)
      If Not rPart Is Nothing Then
        lScrollRow = rPart.Row - 10
        If lScrollRow < 12 Then lScrollRow = 12
      End If
    End If
    ActiveWindow.ScrollRow = lScrollRow
  End If
End Sub

 

[SirPopsALot]

This works prefect! Thank you so much!

 

[ysplace]

Hi

I have a similar issue and cannot seem to get anything to work. I have spent days trying to find a solution.

I have a single sheet called "Main Loan Section" with 8 columns A thru H.
Total rows 40. Row 1 has a freeze. Row 2 contains the the combo box. Row 3 blank line
Active X Combo box linked to B2 and list range B4:B37

Goal:
When a selection is made in the from the dropdown list, I want to automatically go to that row.

Can you help?

 

[SirPopsALot]

ysplace, if no one replies soon I would recommend starting you a new post of your own. I hope you get your answer! May God Bless You Daily!

 

[Peter_SSs]

This works prefect! Thank you so much!

Great. Thanks for letting us know.



@ ysplace

Row 1 has a freeze. Row 2 contains the the combo box.

Goal:
When a selection is made in the from the dropdown list, I want to automatically go to that row.

That seem a little unusual to me. Doesn't it mean that as soon as you scroll to the relevant row your ComboBox disappears and you would manually have to scroll back up to make it reappear before making another selection? Would it be feasible to put the ComboBox in row 1 instead so that it is always visible or put the freeze below row 2 instead?

In any case try this code for the current set-up. I have assumed that your ComboBox is called ComboBox1

Private Sub ComboBox1_Change()
  Dim rPart As Range
  Dim sPart As String
  Dim lScrollRow As Long
  
  sPart = Range("B2").Value
  lScrollRow = 2
  If Len(sPart) > 0 Then
    Set rPart = Range("B4:B47").Find(What:=sPart, LookAt:=xlWhole)
    If Not rPart Is Nothing Then
      lScrollRow = rPart.Row
    End If
  End If
  ActiveWindow.ScrollRow = lScrollRow
End Sub