Solved: Type mismatch error

[dhananjay]

I know it is a very simple syntax error... but I am not getting it right...

I am getting a type mismatch error in the if statement for the following code...

[VBA]Sub separateFiles()
Dim rng As Range, arr As Variant
Dim i As Integer


Dim bSubString As Boolean
Dim str1, str2, str3 As String
Dim asmrow, prtrow, drwrow, row As Integer

str1 = "asm"
str2 = "prt"
str3 = "drw"
asmrow = 32
prtrow = 32
drwrow = 32
Set rng = Worksheets("Sheet1").Range("A32:A100")

arr = Application.Transpose(rng.Value)

For i = LBound(arr) To UBound(arr)
'asmB = InStr(arr, str1)
If bSubString = InStr(arr, str1) <> 0 Then ' this line gives an error..
Cells(asmrow, 32).Value = arr
asmrow = asmrow + 1

End If

If InStr(arr, str2) <> 0 Then

Cells(prtrow, 32).Value = arr
prtrow = prtrow + 1

End If

If InStr(arr, str3) <> 0 Then

Cells(drwrow, 32).Value = arr
drwrow = drwrow + 1

End If

Next i

End Sub[/VBA]

[Kenneth Hobs]

You are assigning a value in the IF. Not sure why you need bSubString.
Try:
[VBA]If InStr(arr, str1) <> 0 Then ' this line gives an error..
[/VBA]

[dhananjay]

Hi kenneth,

I actually tried all three types of statments before posting here...

If InStr(arr, str1) <> 0 Then ' this line gave an error..

If InStr(1, arr, str1) <> 0 Then ' this also shows an error...

If bSubString = InStr(arr, str1) <> 0 Then ' this line gives an error..

I am not able to solve to solve the puzzle..

[Kenneth Hobs]

I didn't look at it closely. You can not use InStr() on an array. I think you want to check each element so you would use "i" to get each indexed element.
e.g.
[vba]IF InStr(arr(i), str2) <> 0 then[/vba]

You will need to use "arr(i)" for other parts too.

[dhananjay]

thanks Kennenth!!
I am new to programming...But atleast i could have thought logically...

thanks a ton for your time...

[mikerickson]

This is one way to look at the whole array, rather than element by element.
[VBA]If InStr(Join(arr), str2) <> 0 Then[/VBA]

[dhananjay]

thnks mikerickson...I did not know this mehod....
But I need the code to check element wise and get the index of the occurance..
Thnks anyway spending your time...

[Kenneth Hobs]

If you are searching in a Range, you can use Find.

If you are searching in a single dimension array, you can use Match.
e.g.
[VBA]Sub ASKU()
Dim skuNumber() As Variant
Dim skuColor() As Variant
Dim i As Integer, r As Range
skuNumber = [{"A123", "A124", "A125", "B123", "B123", "B123"}]
skuColor = [{255,255,255,16711680,16711680,16711680}] 'Red=255, Blue=16711680

i = Index(skuNumber, "a123")
If i > -1 Then
Set r = Range("A1")
r.Value = skuNumber(i)
r.Font.Color = skuColor(i)
End If
End Sub

'val is not case sensitive
Function Index(vArray() As Variant, val As Variant) As Long
On Error GoTo Minus1
Index = WorksheetFunction.Match(val, WorksheetFunction.Transpose(vArray), 0)
Exit Function
Minus1:
Index = -1
End Function[/VBA]