if statement compare inputbox to cell.value

Question

Sub Botão34_Clique()
Dim x As String
Dim z As Integer

InputBox "FIND PRODUCT", "TYPE" = x
For z = 2 To 143
     If Range("B" & z).Value = x Then
        MsgBox Range("B" & z)
    End If
Next z
End Sub

Hi guys, I don't understand why the above code is not working. The user's input will be compared with an existing product list. When the input is equal to an existing product, the msgbox should indicate the cell in which it is present. Unfortunately, I don't understand why the statement Range("B" & z).Value = x doesn't work...

Any ideas?

Answer 1

You don't write the result of the InputBox into the variable x. What you do is to call the InputBox with 2 parameters.

The first parameter ("FIND PRODUCT") is the text that is shown in the input box as label.

The second parameter is used as form caption. You pass the expression "TYPE" = x as second parameter, and VBA will happily compare the string "TYPE" with the content of the variable x, resulting in either True or (most likely) False. So if you carefully look at the input box window, you will see the string "False" as caption.

Now the user enters something into the box and presses the "OK"-button. The entered value is returned, but you don't assign it to anything and the value is send to the big Computer Nirvana.

What you want is to assign the value to x. The correct syntax for this is

x = InputBox("FIND PRODUCT", "TYPE")

Note that in VBA, you have to put parenthesis around the list of parameters when you are calling a function and use the return value.