Button to display next record

curious_dan

New Member
Joined
Mar 14, 2016
Messages
9
Hi,

I have raw data in a sheet named 'Data'. I have in a different sheet named 'Finance', a search box in cell AF32. Once I type in my search box an item code, XLOOKUP then finds that in column F withinin my 'Data' sheet and information is then shown relating to that row of data. I now want to add a button that when clicked, changes the item code in the AF32 search box to the next record in the 'Data' sheet.

For example, I type dmyc01 in my search box. Data is then shown in other cells below relating to dmyc01. Instead of typing dmyc02, I want a button next to the search box to change the search in AF32 to the next available record in the row below dymc01. This will NOT always be in numerical order. 'dymc03' may not exist so it should just display the next row's item code in the 'Data' sheet, which might then be dmyc17.

How do I do this in Excel?

Thanks!!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Does this do what you want: I am assuming that you know how to add a Button and attach the code...

VBA Code:
Sub FindNext()
   
    Dim wsF As Worksheet: Set wsF = Worksheets("Finance")
    Dim wsD As Worksheet: Set wsD = Worksheets("Data")
    Dim fnd As String, str As String

    fnd = Range("AF32")
    str = wsD.Range("F:F").Find(fnd).Offset(1, 0).Address
    wsF.Range("AF32") = wsD.Range(str).Value

End Sub
 
Upvote 0
Does this do what you want: I am assuming that you know how to add a Button and attach the code...

VBA Code:
Sub FindNext()
 
    Dim wsF As Worksheet: Set wsF = Worksheets("Finance")
    Dim wsD As Worksheet: Set wsD = Worksheets("Data")
    Dim fnd As String, str As String

    fnd = Range("AF32")
    str = wsD.Range("F:F").Find(fnd).Offset(1, 0).Address
    wsF.Range("AF32") = wsD.Range(str).Value

End Sub
It worked an absolute treat. Thank you so much!!

Would you be able to provide a code for the exact same but the previous record instead please?

I thought it might be just switching the 1, 0 to 0, 1 but nope.


EDIT- Solved it. -1. Thank you again!!
 
Upvote 0
You are welcome, I was happy to help. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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