VBA CODE - Expected End With

[glerwell]

Hi

Im trying to modify a VBA code I had.

However, I keep on getting a compile error "Expected End With". I am not that fimiliar with VBA to know whwere this "END WITH" should be placed.

Here is my code as it stands now:

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim Rng As Range

    With Worksheets("BETS")
    
        If .Range("B16").Value = "" Then
        
            Set Rng = .Range("B16")
        ElseIf .Range("B17") = "" Then
        
            Set Rng = .Range("B17")
        Else
        
            Set Rng = .Range("B16").End(xlDown).Offset(1, 0)
        End If
    
        With Rng
        
             .Offset(0, 0).Value = Me.ComboBox3.Value
            
        With .Offset(0, 1)
             .Formula = "=VLOOKUP(B" & Rng.Row & ",'BETTING BANKS'!$B$16:$O$60,14,FALSE)"
             .Value = .Value
            
             .Offset(0, 4).Value = Me.ComboBox1.Value
             .Offset(0, 5).Value = Me.ComboBox2.Value
            
        With .Offset(0, 6)
             .Formula = "=VLOOKUP(B" & Rng.Row & ",'BETTING BANKS'!$B$16:$N$60,12,FALSE)"
             .Value = .Value
End With
        End With
    End With
    Call sortselections
    Call copy_selections
    Unload Me
    add_selection.Show
End Sub

The problem has only occured since I added the lines that are like this:

With .Offset(0, 1)
             .Formula = "=VLOOKUP(B" & Rng.Row & ",'BETTING BANKS'!$B$16:$O$60,14,FALSE)"
             .Value = .Value

I will be adding more lines like the one above, so will somebody be kind enough to let me know where these "END WITH"'s should be placed in the code.

Thanks

 

[VoG]

Try

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim Rng As Range
With Worksheets("BETS")
    If .Range("B16").Value = "" Then
        Set Rng = .Range("B16")
    ElseIf .Range("B17") = "" Then
        Set Rng = .Range("B17")
    Else
        Set Rng = .Range("B16").End(xlDown).Offset(1, 0)
    End If
    With Rng
        .Offset(0, 0).Value = Me.ComboBox3.Value
        With .Offset(0, 1)
            .Formula = "=VLOOKUP(B" & Rng.Row & ",'BETTING BANKS'!$B$16:$O$60,14,FALSE)"
            .Value = .Value
            .Offset(0, 4).Value = Me.ComboBox1.Value
            .Offset(0, 5).Value = Me.ComboBox2.Value
        End With
        With .Offset(0, 6)
            .Formula = "=VLOOKUP(B" & Rng.Row & ",'BETTING BANKS'!$B$16:$N$60,12,FALSE)"
            .Value = .Value
        End With
    End With
End With
Call sortselections
Call copy_selections
Unload Me
add_selection.Show
End Sub

 

[glerwell]

Thanks VOG.

It did work but I did have a problem regarding

.Offset(0, 4).Value = Me.ComboBox1.Value
.Offset(0, 5).Value = Me.ComboBox2.Value

Only one of the values were being added to my sheet. However, I got it working by adding an "End With" before that line.

Hopefully, I can add the other lines to the code without any more assistance

Thanks