PDA

View Full Version : Solved: Type mismatch error



dhananjay
12-17-2008, 07:42 AM
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...

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

Kenneth Hobs
12-17-2008, 08:19 AM
You are assigning a value in the IF. Not sure why you need bSubString.
Try:
If InStr(arr, str1) <> 0 Then ' this line gives an error..

dhananjay
12-17-2008, 07:51 PM
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..:confused2

Kenneth Hobs
12-17-2008, 08:08 PM
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.
IF InStr(arr(i), str2) <> 0 then

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

dhananjay
12-17-2008, 10:20 PM
thanks Kennenth!!
I am new to programming...But atleast i could have thought logically...

thanks a ton for your time...

mikerickson
12-17-2008, 10:29 PM
This is one way to look at the whole array, rather than element by element.
If InStr(Join(arr), str2) <> 0 Then

dhananjay
12-23-2008, 03:19 AM
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
12-23-2008, 09:09 AM
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.
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