Advice for additional code on userform

[ipbr21054]

Hi,
I currently have the code shown.

Private Sub UserForm_Initialize()
Dim i As Long, LastRow As Long
LastRow = Sheets("GRASS").Range("A" & Rows.Count).End(xlUp).Row
If Me.ComboBox1.ListCount = 0 Then
For i = 3 To LastRow
Me.ComboBox1.AddItem Sheets("GRASS").Cells(i, "A").Value
Next i
End If
ComboBox1.Value = "SELECT NAME"
ComboBox1.SetFocus
End Sub

Private Sub ComboBox1_Change()
Dim i As Long, LastRow As Long
LastRow = Sheets("GRASS").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Sheets("GRASS").Cells(i, "A").Value = (Me.ComboBox1) Or _
Sheets("GRASS").Cells(i, "A").Value = Val(Me.ComboBox1) Then
Me.TextBox1 = Sheets("GRASS").Cells(i, "C").Value
Me.TextBox2 = Sheets("GRASS").Cells(i, "E").Value
Me.TextBox3 = Sheets("GRASS").Cells(i, "I").Value
Me.TextBox4 = Sheets("GRASS").Cells(i, "G").Value
Me.TextBox5 = Sheets("GRASS").Cells(i, "O").Value
Me.TextBox6 = Format(Sheets("GRASS").Cells(i, "K").Value, "£#,##0.00")
Me.TextBox7 = Sheets("GRASS").Cells(i, "M").Value
Me.TextBox8 = Sheets("GRASS").Cells(i, "Q").Value
Me.TextBox9 = Sheets("GRASS").Cells(i, "U").Value
Me.TextBox10 = Format(Sheets("GRASS").Cells(i, "S").Value, "£#,##0.00")
Me.TextBox11 = Sheets("GRASS").Cells(i, "W").Value
Me.TextBox12 = Sheets("GRASS").Cells(i, "Y").Value
Exit For
End If
Next
End Sub

I would like a code that would check my worksheet GRASS in column S for a value.
If a value is present then when the customer is selected from the drop down list TextBox10 will show that value BUT is the cell is empty then TextBox 10 would show £0.00

I think i can do it for 1 cell but not sure how to do it as there are multiple cells.

Thanks

 

[sykes]

This SHOULD do what you've asked, but TEST on a COPY of your work first!

Private Sub ComboBox1_Change()

Dim i As Long, LastRow As Long
LastRow = Sheets("GRASS").Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
    
        With Sheets("GRASS")
                If .Cells(i, "A").Value = (Me.ComboBox1) Or _
                .Cells(i, "A").Value = Val(Me.ComboBox1) Then
                Me.TextBox1 = .Cells(i, "C").Value
                Me.TextBox2 = .Cells(i, "E").Value
                Me.TextBox3 = .Cells(i, "I").Value
                Me.TextBox4 = .Cells(i, "G").Value
                Me.TextBox5 = .Cells(i, "O").Value
                Me.TextBox6 = Format(.Cells(i, "K").Value, "£#,##0.00")
                Me.TextBox7 = .Cells(i, "M").Value
                Me.TextBox8 = .Cells(i, "Q").Value
                Me.TextBox9 = .Cells(i, "U").Value
                
                    With .Cells(i, "S")
                        If Not IsEmpty(.Value) Then Me.TextBox10.Value = Format(.Value, "£#,##0.00"): GoTo nxt_line
                           Me.TextBox10.Value = "£0.00"
                    End With
nxt_line:                 Me.TextBox11 = .Cells(i, "W").Value
                Me.TextBox12 = .Cells(i, "Y").Value
    Exit For
            End If
    Next
        End With
End Sub

 

[ipbr21054]

This line pops up with a compile error.

With .Cells(i, "S")

 

[sykes]

Try, without the second With statement...

Private Sub ComboBox1_Change()

Dim i As Long, LastRow As Long

LastRow = Sheets("GRASS").Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow

    
        With Sheets("GRASS")

                If .Cells(i, "A").Value = (Me.ComboBox1) Or .Cells(i, "A").Value = Val(Me.ComboBox1) Then
                Me.TextBox1 = .Cells(i, "C").Value
                Me.TextBox2 = .Cells(i, "E").Value
                Me.TextBox3 = .Cells(i, "I").Value
                Me.TextBox4 = .Cells(i, "G").Value
                Me.TextBox5 = .Cells(i, "O").Value
                Me.TextBox6 = Format(.Cells(i, "K").Value, "£#,##0.00")
                Me.TextBox7 = .Cells(i, "M").Value
                Me.TextBox8 = .Cells(i, "Q").Value
                Me.TextBox9 = .Cells(i, "U").Value

                    If Not IsEmpty(.Cells(i, "S").Value) Then Me.TextBox10.Value = Format(.Cells(i, "S").Value, "£#,##0.00"): GoTo nxt_line

                Me.TextBox10.Value = "£0.00"
nxt_line:       Me.TextBox11 = .Cells(i, "W").Value
                Me.TextBox12 = .Cells(i, "Y").Value

    Exit For
            End If
    Next
        End With
End Sub

 

[ipbr21054]

The Next at the end now gives Next without For

 

[sykes]

...Should be there, now...

Private Sub ComboBox1_Change()

Dim i As Long, LastRow As Long

LastRow = Sheets("GRASS").Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow

    
        With Sheets("GRASS")

            If .Cells(i, "A").Value = (Me.ComboBox1) Or .Cells(i, "A").Value = Val(Me.ComboBox1) Then
                Me.TextBox1 = .Cells(i, "C").Value
                Me.TextBox2 = .Cells(i, "E").Value
                Me.TextBox3 = .Cells(i, "I").Value
                Me.TextBox4 = .Cells(i, "G").Value
                Me.TextBox5 = .Cells(i, "O").Value
                Me.TextBox6 = Format(.Cells(i, "K").Value, "£#,##0.00")
                Me.TextBox7 = .Cells(i, "M").Value
                Me.TextBox8 = .Cells(i, "Q").Value
                Me.TextBox9 = .Cells(i, "U").Value

                    If Not IsEmpty(.Cells(i, "S").Value) Then Me.TextBox10.Value = Format(.Cells(i, "S").Value, "£#,##0.00"): GoTo nxt_line

                Me.TextBox10.Value = "£0.00"
                
nxt_line:      Me.TextBox11 = .Cells(i, "W").Value
               Me.TextBox12 = .Cells(i, "Y").Value
    
    Exit For
            End If
            
          End With
    Next
        
End Sub

 

[ipbr21054]

That worked.

Have a nice day & thanks very much for this

 

[sykes]

That's great news Ian! Thanks for the feedback.
Sorry it took a while, but I was trying to reproduce your set-up, without having to emulate all of the textboxes, which meant it was never fully tested on your actual model until you ran it yourself.