Drop down or Combo Box Help with VBA code and formula

[mwheelock]

I am trying to create a drop down menu that will go to the specific
cell where that same text is in a worksheet when that text is selected
from the dropdown menu(combobox). I made a combobox menu from the
Forms menu not the Control. The text I am trying to find with this
macro is a list of countries in column B that starts row 2. In
Columns C,D, E there are surcharges that start in row 2 and continue a
number of rows down depending on the country. This format repeats
itself for some 146 countries on down the spreadsheet in terms of
rows. Is there a formula that can jump to or go to each country's
cell location on the spread sheet from the dropdown menu so the user
doesn't have to scroll through 2000 rows to find the right country?
e.g. Brazil is B2, Paraguay-B24, Venezuela-B46. Thanks.

Matty

 

[Toppers]

try: macro is attached to your combobox and G1 is the link cell for your
combobox. I have assumed your combox input range is in column A.

Sub DropDown1_Change()
ActiveWindow.ScrollRow = 1
nrow = Application.Match(Cells(Range("G1") + 1, "A"), Range("B:B"), 0)
ActiveWindow.ScrollRow = nrow

End Sub

HTH

 

[mwheelock]

try: macro is attached to your combobox and G1 is the link cell for your
combobox. I have assumed your combox input range is in column A.

Sub DropDown1_Change()
ActiveWindow.ScrollRow = 1
nrow = Application.Match(Cells(Range("G1") + 1, "A"), Range("B:B"), 0)
ActiveWindow.ScrollRow = nrow

End Sub

HTH






- Show quoted text -

the last statement, ActiveWindow.ScrollRow = nrow, is giving an
error: nrow = error2042, when I drag the cursor over it. I can't
tell if it works yet.

 

[Toppers]

You will get an "nrow" error if it cannot match the country in the drop down
with those in column B.

If still get errors, post sample w/sheet to:

toppers <at> NOSPAMjohntopley.fsnet.co.uk

Remove NOSAPAM

Try:

Sub DropDown1_Change()
ActiveWindow.ScrollRow = 1
nrow = Application.Match(Cells(Range("G1") + 1, "A"), Range("B:B"), 0)
If not iserror(nrow) then
ActiveWindow.ScrollRow = nrow
Else
Msgbox Cells(range("G1")+1,"A") & " not found"
end if

 

[mwheelock]

You will get an "nrow" error if it cannot match the country in the drop down
with those in column B.

If still get errors, post sample w/sheet to:

toppers <at> NOSPAMjohntopley.fsnet.co.uk

Remove NOSAPAM

Try:

Sub DropDown1_Change()
ActiveWindow.ScrollRow = 1
nrow = Application.Match(Cells(Range("G1") + 1, "A"), Range("B:B"), 0)
If not iserror(nrow) then
ActiveWindow.ScrollRow = nrow
Else
Msgbox Cells(range("G1")+1,"A") & " not found"
end if






- Show quoted text -

That works! You can ignore my e-mail if you like. Thanks so much for
taking the time with this! I sincerely appreciate it.