Type mismatch error

[dsusercs]

Hi,

I have a file from which I'm extracting the lines that start with "#### STAGE:" and then to extract the string that is after "#### STAGE:". When I run the below VBA code, I get an error as "Type mismatch". I tried to resolve it but couldn't crack. Any help would be really appreciated.

Sub test()
Dim fn As String, txt As String, a() As String
Dim i As Long, ii As Long, iii As Long, n As Long, x, y

fn = "E:\DSTest\CIGNA\IMAGE_CHG_CAP_LKP.dsx"
delim = vbTab
temp = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
x = Split(temp, vbCrLf)

ReDim a(1 To UBound(x) + 1, 1 To 100)
For i = 0 To UBound(x)
If InStr(1, x(i), "#### STAGE: ", 1) > 0 Then
n = n + 1: y = Split(x(i), delim)
For ii = 0 To UBound
a(n, ii + 1) = y(ii)
End If
Next

If n > 0 Then
b = Mid(a, 13, 1) --> This line gives the error
Sheets(1).Cells(1).Resize(n, 100).Value = b
End If
End Sub

If I run the above without "b = Mid(a, 13, 1)" it runs fine.

 

[RoryA]

a is an array so you can't take the 13th letter of it. You have to loop if you need to manipulate all the array elements.

 

[dsusercs]

Thanks for your clarification RoryA. Would you mind giving the code? I have written the above with the help of my friend who is not available now. I have to get the content that follows "#### STAGE: " i.e. starting from 13th position for each row that is fetched.

 

[RoryA]

It's hard to be sure since there is no way that the code you posted would run at all since it wouldn't compile, but probably something like:

If n > 0 Then
for i = lbound(a, 1) to ubound(a, 1)
a(i, 1) = mid(a(i, 1), 13)
next i
Sheets(1).Cells(1).Resize(n, 100).Value = a
End If

 

[dsusercs]

It worked just perfectly fine RoryA. Thanks for your immediate help