excel vlookup in VBA functioning sometimes

[SCOTTWHITTAKER2333]

I am having an issue with a vlookup function being used in VBA on a userform. What is supposed to happen is: when the user opens the userform, a userform_activate code should lookup the most recent product # and select that product number on a list box. Then there are sevral vlookup functions that are supposed to change the caption of 4 labels on the userform baced on a hidden table on the sheet using the product number as the search criteria. The problem I am having is that the vlookups are only working sometimes. It seems to be completely random when they might work or not. I even tried adding a 1 second wait time to see if that would help and it did not. When the userform opens the listbox item is selected but the vlookups dont always function. I am using excel 2003.
Here is what my code currently looks like:

(Also note that you will see the listboxes are labeled as ComboBox in the code. This is because they were originaly designed as ComboBoxes but I changed them a little while ago and just gave them the combobox 's name to avoid needing to chang other code.)

Private Sub UserForm_Activate()
On Error Resume Next
If Range("J5").Value <> "" Then
Me.ComboBox1.Value = Range("J5").Value
Application.Wait Now + TimeValue("00:00:01")
Me.ComboBox2.Value = Range("G5").Value
Application.Wait Now + TimeValue("00:00:01")
Me.TextBox18.Value = Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 2, False)
Me.Label17.Caption = Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 6, False)
Me.TextBox15.Value = VBA.Format(0 - (Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 7, False)), "0.00")
Me.TextBox16.Value = VBA.Format(0 - (Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 8, False)), "0.00")
ElseIf Range("H5").Value <> "" Then
Me.ComboBox1.Value = Range("H5").Value
Application.Wait Now + TimeValue("00:00:01")
Me.ComboBox2.Value = Range("G5").Value
Application.Wait Now + TimeValue("00:00:01")
Me.TextBox18.Value = Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 2, False)
Me.Label17.Caption = Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 6, False)
Me.TextBox15.Value = VBA.Format(0 - (Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 7, False)), "0.00")
Me.TextBox16.Value = VBA.Format(0 - (Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 8, False)), "0.00")
ElseIf Range("J4").Value <> "" Then
Me.ComboBox1.Value = Range("J4").Value
Application.Wait Now + TimeValue("00:00:01")
Me.ComboBox2.Value = Range("G4").Value
Application.Wait Now + TimeValue("00:00:01")
Me.TextBox18.Value = Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 2, False)
Me.Label17.Caption = Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 6, False)
Me.TextBox15.Value = VBA.Format(0 - (Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 7, False)), "0.00")
Me.TextBox16.Value = VBA.Format(0 - (Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 8, False)), "0.00")
ElseIf Range("H4").Value <> "" Then
Me.ComboBox1.Value = Range("H4").Value
Application.Wait Now + TimeValue("00:00:01")
Me.ComboBox2.Value = Range("G4").Value
Application.Wait Now + TimeValue("00:00:01")
Me.TextBox18.Value = Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 2, False)
Me.Label17.Caption = Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 6, False)
Me.TextBox15.Value = VBA.Format(0 - (Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 7, False)), "0.00")
Me.TextBox16.Value = VBA.Format(0 - (Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 8, False)), "0.00")
ElseIf Range("J3").Value <> "" Then
Me.ComboBox1.Value = Range("J3").Value
Application.Wait Now + TimeValue("00:00:01")
Me.ComboBox2.Value = Range("G3").Value
Application.Wait Now + TimeValue("00:00:01")
Me.TextBox18.Value = Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 2, False)
Me.Label17.Caption = Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 6, False)
Me.TextBox15.Value = VBA.Format(0 - (Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 7, False)), "0.00")
Me.TextBox16.Value = VBA.Format(0 - (Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 8, False)), "0.00")
Else
Me.ComboBox1.Value = Range("H3").Value
Application.Wait Now + TimeValue("00:00:01")
Me.ComboBox2.Value = Range("G3").Value
Application.Wait Now + TimeValue("00:00:01")
Me.TextBox18.Value = Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 2, False)
Me.Label17.Caption = Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 6, False)
Me.TextBox15.Value = VBA.Format(0 - (Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 7, False)), "0.00")
Me.TextBox16.Value = VBA.Format(0 - (Application.VLookup(ComboBox1.Value + 0, Worksheets("sheet1").Range("A79:H201"), 8, False)), "0.00")
End If
End Sub

Any suggestions would be wonderful.

 

[AlphaFrog]

I don't know what the problem is but if you use On Error Resume Next, that would hide any errors that could occur in the code. It's generally not good practice to ignore errors like that.

My guess is the VLookup can't find a match for some reason and its error is just ignored. I doubt it's a timing issue so you wouldn't need to use application.wait

Here's your code cleaned up a bit. I don't know if it would fix your problem.

Private Sub UserForm_Activate()

    Dim Found As Range, cell As Range, c As Variant

    With Worksheets("Sheet1")

        Set cell = .Range("H3") 'Default
        For Each c In Array("J5", "H5", "J4", "H4", "J3")
            If .Range(c) <> "" Then Set cell = .Range(c): Exit For
        Next c

        Me.ComboBox1.Value = cell.Value
        Me.ComboBox2.Value = .Range("G" & cell.Row).Value

        Set Found = .Range("A79:A201").Find(cell.Value, , xlValues, xlWhole, xlByRows, xlNext, False)
        If Not Found Is Nothing Then
            Me.TextBox18.Value = Found.Offset(, 1).Value
            Me.Label17.Caption = Found.Offset(, 5).Value
            Me.TextBox15.Value = Format(0 - Found.Offset(, 6).Value, "0.00")
            Me.TextBox16.Value = Format(0 - Found.Offset(, 7).Value, "0.00")
        Else
            Me.TextBox18.Value = "No Match"
            Me.Label17.Caption = "No Match"
            Me.TextBox15.Value = "No Match"
            Me.TextBox16.Value = "No Match"
        End If

    End With
    
End Sub

 

[SCOTTWHITTAKER2333]

Thanks, that worked great!!

 

[AlphaFrog]

You're welcome.