Error Invalid Qualifier

[LWhite]

I have a spread sheet with a combo box that my user will
select a value in. After that I am wanting to take that
value and search another sheet in the same workbook for
its match. Then I need to move over a coupld of cells in
the row and transfer the value of the active cell into
memory. I will finally take that value and place it back
into my first sheet of the workbook. I have the following
so far.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Private Sub CommandButton1_Click()

Dim myRng2 As Range
Dim myPart As Range
Dim myUnit As Variant
Dim myDesc As String
Dim quoteRng As String

' read the value of the combo box

myDesc = Me.ComboBox2.Value

' find the cell in sql that the desc came from
' grab the part# from the sql sheet
' grab the unit of usage

With Worksheets("SQL")
Set myRng2 = .Range("d1", .Cells
(.Rows.Count, "d").End(xlUp))
For Each myPart In myRng2.Cells
If LCase(myDesc.Value) Like LCase(myRng2) Then
Set myPart = myRng2.Offset(0, -3)
End If
Next myPart
End With


' find the row this material will be going in

' place the desc into the correct cell
' place the part# into its cell
' place the UoU into its cell


Sheets("Quote").Select
Range("J17").Select
Range("J17").Value = myDesc
Range("G17").Select
Range("G17").Value = myPart

End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Right now I get an error message when I click on the
button saying Invalid Qualifier and highlighting myDesc
in the if statement. I realize that there will only be
one exact match for the combo box value but I didn't know
how else to do this.

Can anyone help?
LWhite

 

[DMoney]

I got the same message. I was able to run the code once
myDesc had an initial value - to do that, i put ""around
the variable steped through the code until myDesc had a
value then removed the quotes from around the variable and
from then on, the problem was resolved.

HTH

If LCase(myDesc) Like LCase(myRng2) Then
Set myPart = myRng2.Offset(0, -3)
End If

 

[Bernie Deitrick]

L,

Your variable myDesc is a string variable, and as such doesn't have a.Value
property. Change

If LCase(myDesc.Value) Like LCase(myRng2) Then

to

If LCase(myDesc) Like LCase(myRng2) Then


HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Also, since your myRng2 is a range variable, it does have a Value property.
Fortunately, the Value property is the default property, so

If LCase(myDesc) Like LCase(myRng2) Then

is the same as

If LCase(myDesc) Like LCase(myRng2.Value) Then

But, of course, the latter is preferred.

HTH,
Bernie
MS Excel MVP