Hello, I have a following problem. I am writing a code in VBA as Excel Macro where one of the tasks is to compare a string which is in a cell (string is unknown!) with a character-letter 'D'. I tried to use InStr function as follows :

Dim Val As Integer
Set c = Application.Worksheets(1).Cells(1,1).Value
Val = InStr(c, "D", 1)
If Val = 0 Then bla bla bla 'which means that if letter D is in a
                             'string in cell a1 then...

Though I get a [run-time error '13': types mismatch] in concern to InStr function. Maybe I should declare c differently? I know that c in InStr Function must be a 'string expression' but I don't know how I should write a string to c if not by Value Property. Any suggestions? Please help!
Thanx, Daniel.

Hi,

You have used the syntax Set C, remove the Set and it works super dooper.

Cheers

Matt

might I also recommend using this:-

Dim Val As Integer
If IsEmpty(Application.Worksheets(1).Cells(1, 1).Value) Then
    c = 0
    Else
    c = Application.Worksheets(1).Cells(1, 1).Value
End If
Val = InStr(1, c, "D")

your could even slim it down a bit with ...
Dim Val As Integer
If IsEmpty(Application.Worksheets(1).Cells(1, 1).Value) Then
  Val = 0
Else
  Val = InStr(1, Application.Worksheets(1).Cells(1, 1).Value, "D")
End If
...
also are you sure that your worksheet is always going to be index item 1, could you call it by name rather than by index number?

Hi Matt, I removed Set syntax and wrote that line as:
c = Application.Worksheets(1).Cells(1, 1).Value; though the compiler shows me the very same error.
Daniel.

Not sure if you mean to only require an upper case D but if you wanted any D i.e. d or D then use this code...

Dim Val As Integer
If IsEmpty(Application.Worksheets(1).Cells(1, 1).Value) Then
  Val = 0
Else
  Val = InStr(1, UCase(Application.Worksheets(1).Cells(1, 1).Value), "D")
End If

are you still getting the error?

Hi Matt, thx much for your help. It finally works! I didn't even have to write .Value at the end. And with Set syntax it works as well. I have to write Set 'cause I need to work on c later and if I don't write Set c than an arror occurs that object is required. Anyways, thx a lot again! Daniel.