VBA Code - Run-time error '438'

[glerwell]

Hi

Can somebody please tell me why I seem to be having a runtime error '438' error with the following code.

I have modified it from my original code but now I seem to be getting this error

Code:

Option Explicit
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.ComboBox1.Value
.Offset(0, 1).Value = Me.ComboBox2.Value
.Offset(0, 2).Value = Me.ComboBox3.Value
End With
        
With .Offset(0, 4)
.Formula = "=VLOOKUP(B" & Rng.Row & ",'BETTING BANKS'!$B$16:$O$60,14,FALSE)"
.Value = .Value
End With
        
With .Offset(0, 6)
.Formula = "=VLOOKUP(B" & Rng.Row & ",'SYSTEMS'!$B$16:$D$76,3,FALSE)"
.Value = .Value
End With
        
With .Offset(0, 8)
.Formula = "=VLOOKUP(B" & Rng.Row & ",'BETTING BANKS'!$B$16:$O$60,12,FALSE)"
.Value = .Value

With .Offset(0, 5)
.Formula = "=IF(H" & Rng.Row & "=2,""EXCHANGE"",""BOOKMAKER"")"
.Value = .Value
End With
End With
End With
Call copy_selections
Call sortselections
Unload Me
add_selection.Show
End Sub

 

[Weaver]

On which line does operation terminate?

 

[Colin Legg]

Hi,

I can't tell from your post whether you have your code indented or not, but if you don't then it would help you to see the problem if you did. Cutting out the "noise", the first problem is essentially this:

    With Worksheets("BETS")
       '..... code in here
 
        With .Offset(0, 4)

The worksheet class doesn't have an Offset property, so you get a RTE 438 error. Presumably this should be an offset from a range?

Hope that helps...

 

[glerwell]

thanks guys I got it working now, I added "With RNG" to the code before "With .Offset(0, 4)"