Solved: populate text box from combo box via vlookup function

[wolf.stalker]

ok, stumped again any idea why i might be getting run-Time error 1004 here? Application-defined or object-defined error?

i have looked at dozens of bits of code trying to figure out how to do this and below is a common answer. however, when i try it, i get errored out.

any suggestions?

'*******************************************************

Dim sValue As String

sValue = Application.WorksheetFunction.VLookup(cmbSite.Value, Worksheets("Data").Range("A:V"), 2, False)

txtSiteAddy.Value = sValue

[Zack Barresse]

Hi there,

It's going to have to be with either of your two objects there, cmbSite or txtSiteAddy. Ensure they are initialized to what you want. Can't really give you much more than that unless we know what those objects are, see all of the code, and know what is the purpose of what you're trying to accomplish here.

HTH

[wolf.stalker]

Hi there,

It's going to have to be with either of your two objects there, cmbSite or txtSiteAddy. Ensure they are initialized to what you want. Can't really give you much more than that unless we know what those objects are, see all of the code, and know what is the purpose of what you're trying to accomplish here.

HTH

Zack. Thanks for getting back to me. Hard to find help late at night, but i work from 2000-0800 so i usually get a lot of time to try and make stuff work. this just isn't working for me.

um, let me see if i can help you out. here is the code used to fill the combo box in the first place. it looks a bit weird they way it works, but it's necessary.

[VBA]Private Sub LoadComboBoxes(iSiteCount)
Dim iLCV As Integer
ActiveWorkbook.Worksheets("Form").cmbSite.Clear

' iSiteCount = 3301 this is the value that is passed to this procedure

temp = Application.CountA(Range("A" & (iSiteCount + 2), "A" & (iSiteCount * 2)))

' temp = 664 this is the value that winds up in temp

For iLCV = (iSiteCount + 2) To (iSiteCount + 1 + temp)
ActiveWorkbook.Worksheets("Form").cmbSite.AddItem (Sheet3.Range("B" & iLCV).Value)
Next iLCV
End Sub[/VBA]

long story short, the page has some 3300 lines of data, but some of it repeats a lot of info such as store numbers. what you do not see is that i take that 3300 lines of data, skip two rows and paste it and then go and delete all duplicate rows based on a store numbere so that i get only 1 instance of the store number to populate the combo box!

as for the combo box change event, there is nothing in it except the line that i provided you. i have changed it up a bit "playing with it" but here it is.

[VBA]Private Sub cmbSite_Change()

txtSiteAddy.Text = Application.VLookup(cmbSite.Value, Sheets("Data").Range("A:V"), 2, False)

End Sub[/VBA]

Ensure they are initialized to what you want

when the change event is fired off, the text box = "" and the of course the combo box is populated with ~1600 entries.

...know what is the purpose of what you're trying to accomplish here.

What i want is that when the combo box is changed, we do a lookup to find the address, then the city, state, etc and populate the text boxes as such. i know what i wrote "should" be correct, i just can't figure out why it's bugging.

[wolf.stalker]

ok. i gave up. went about it from a whole other direction so i guess we can mark this as "solved" :-\

[mdmackillop]

This will give you a unique list to populate a combobox. As posted here. The SpecialCells bit works with a filtered list, so you can remove it if not required.

[VBA]
Private Sub UserForm_Initialize()
ListBox1.List = ListData("A")
End Sub

Function ListData(col)
Dim d
Set d = CreateObject("Scripting.Dictionary")
Dim Rng As Range, cel As Range
Set Rng = Range(Cells(2, col), Cells(Rows.Count, col).End(xlUp)).SpecialCells(xlCellTypeVisible)
On Error Resume Next
For Each cel In Rng
d.Add cel.Value, CStr(cel)
Next
On Error GoTo 0
ListData = d.items
End Function
[/VBA]