error

[ranswert]

When I run the following code from a userform I get a 'subscript out of range
error'. What am I doing wrong?

Private Sub CommandButton1_Click()
Dim costitem As String
unprotectsheet
costitem = ComboBox1.Value
If costitem <> "" Then
Range("costitemrng").Find(what:=costitem, lookat:=x1whole,
LookIn:=x1values, searchorder:=x1bycolumns).Activate
'selectcostitem.Hide
'Editcostitem.Show
End If
selectcostitem.Hide
protectsheet
End Sub

Thanks

 

[Smallweed]

2 possibilities:
- try ComboBox1.Text instead
- have you tested for null? IsNull(ComboBox1.Value)

 

[Gary''s Student]

replace:
Range("costitemrng")
with:
Range(costitemrng)

because

costitemrng should already contain a string

 

[ranswert]

I tried changing to "costitem=combobox1.text" and I still got the same error.
How do i do the "IsNull(ComboBox1.Value)" code and what does it do?
Thanks

 

[ranswert]

I did what you said and I still got the same error. "costitemrng" is a named
range on my spreadsheet.
Any ideas on what else might be wrong? or a better way to look up a string
and select the cell that contains the string?
Thanks

 

[Smallweed]

Another way is:

Dim rng As Range (at top of Sub)
-
-
For Each rng In Range("costitemrng").Cells
If InStr(rng.Value, costitem) <> 0 Then
rng.Select
Exit For
End If
Next

 

[ranswert]

That worked
Thank You

Another way is:

Dim rng As Range (at top of Sub)
-
-
For Each rng In Range("costitemrng").Cells
If InStr(rng.Value, costitem) <> 0 Then
rng.Select
Exit For
End If
Next