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.
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.