searching for a combobox.value and filling in textboxes from results

[GregJG]

Hi all!

what I am trying to do is after the combobox is updated, do a searc
for the combobox.value and fill in the remaining textboxes from th
search. what i have is this.

Private Sub combobox1_AfterUpdate()

Dim rng As Range

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb = Workbooks.Open("Path")
Set rng = wb.Worksheets(1).Columns(1).find(combobox1.Value)

If rng = True Then
UserForm1.textbox1.value = ActiveCell.Offset(0, 1)
UserForm1.textbox2.value = ActiveCell.Offset(0, 2)
UserForm1.textbox3.value = ActiveCell.Offset(0, 3)
UserForm1.textbox3.value = ActiveCell.Offset(0, 4)
End If

wb.Close True
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub


I'm thinking the problem has to do with the activecell.offset
I am not getting any error message, but the boxes are not bein
filled.

can anyone help

 

[papou]

Hi GregJG
Try
If Not rng Is Nothing Then
UserForm1.textbox1.value = rng.Offset(0, 1)
UserForm1.textbox2.value = rng.Offset(0, 2)
UserForm1.textbox3.value = rng.Offset(0, 3)
UserForm1.textbox3.value = rng.Offset(0, 4)
....

HTH
Cordially
Pascal

 

[GregJG]

Thanks Papou !! It worked. Not exactly sure though.

I understand the change to rng.offset. But the

If Not rng Is Nothing Then

has me confused. not exactly sure what that is saying. Guess it will b
one to keep on my notes

Thanks again

 

[papou]

GregJG

has me confused. not exactly sure what that is saying. Guess it will be
one to keep on my notes

The find method returns Nothing when no match value is found.

HTH
Cordially
Pascal