G
Gene Augustin
MAC Powerbook G4, OS 10.5.6
Office 2004, Excel 2004 Version 11.5.3
In a macro,I have assigned values:
i = 1
j = 1
v = D02/28/2008
shtnew = Sheets("IIF")
The following gives runtime error 91, although it works in another macro
with identical definitions.
shtnew.Cells(i, colnum).Value = v
Here is the entire macro
Sub QifTo8Col()
Dim Shtcount As Long, v As String
Dim shtold As Worksheet, shtnew As Worksheet
Dim colID As String, coldet As Long, colnum As Long
'Rename and add sheet
Shtcount = Sheets.Count
Sheets(1).Name = "QIF"
Set shtold = Sheets("QIF")
If Shtcount = 1 Then
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(2).Name = "IIF"
Set shtnew = Sheets("IIF")
'Else
End If
shtold.Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
coldet = 0 'identify missing columns C N M
i = 1 ' new row number
j = 0 ' new column number from 1 to between 5 and 7
For k = 1 To n 'For this row in raw data
v = Cells(k, 1) 'v= Data Value
If v <> "^" Then 'part of a group,
colexp = 0
colID = Left(v, 1) 'D, T, C, N, P, M, L, ^
Select Case colID
Case "D"
j = 1
'ColExp= 0
Case "T"
j = 2
'ColExp= 1
Case "C"
j = 3
'ColExp= 2
Case "N"
j = 4
Case "P"
j = 5
Case "M"
j = 6
Case "L"
j = 7
' Case Else
End Select
colexp = j - 1
coldet = coldet + 2 ^ colexp
shtnew.Cells(i, j).Value = v
' j = j + 1
Else
coldet = 127 - coldet
Select Case coldet
Case 123 'no C
Sht.newCells(i, 3) = "CX"
Case 119 'No N
Sht.newCells(i, 4) = "N"
Case 95 'No M
Sht.newCells(i, 6) = "MNo Memo"
Case 115 'No CN
Sht.newCells(i, 3) = "CX"
Sht.newCells(i, 4) = "N"
Case 91 'No CM
Sht.newCells(i, 3) = "CX"
Sht.newCells(i, 6) = "MNo Memo"
Case 83 'No CNM
Sht.newCells(i, 3) = "CX"
Sht.newCells(i, 4) = "N"
Sht.newCells(i, 6) = "MNo Memo"
Case 87 'No NM
Sht.newCells(i, 4) = "N"
Sht.newCells(i, 6) = "MNo Memo"
End Select
coldet = 0
i = i + 1
j = 0
End If
Next
End Sub
Office 2004, Excel 2004 Version 11.5.3
In a macro,I have assigned values:
i = 1
j = 1
v = D02/28/2008
shtnew = Sheets("IIF")
The following gives runtime error 91, although it works in another macro
with identical definitions.
shtnew.Cells(i, colnum).Value = v
Here is the entire macro
Sub QifTo8Col()
Dim Shtcount As Long, v As String
Dim shtold As Worksheet, shtnew As Worksheet
Dim colID As String, coldet As Long, colnum As Long
'Rename and add sheet
Shtcount = Sheets.Count
Sheets(1).Name = "QIF"
Set shtold = Sheets("QIF")
If Shtcount = 1 Then
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(2).Name = "IIF"
Set shtnew = Sheets("IIF")
'Else
End If
shtold.Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
coldet = 0 'identify missing columns C N M
i = 1 ' new row number
j = 0 ' new column number from 1 to between 5 and 7
For k = 1 To n 'For this row in raw data
v = Cells(k, 1) 'v= Data Value
If v <> "^" Then 'part of a group,
colexp = 0
colID = Left(v, 1) 'D, T, C, N, P, M, L, ^
Select Case colID
Case "D"
j = 1
'ColExp= 0
Case "T"
j = 2
'ColExp= 1
Case "C"
j = 3
'ColExp= 2
Case "N"
j = 4
Case "P"
j = 5
Case "M"
j = 6
Case "L"
j = 7
' Case Else
End Select
colexp = j - 1
coldet = coldet + 2 ^ colexp
shtnew.Cells(i, j).Value = v
' j = j + 1
Else
coldet = 127 - coldet
Select Case coldet
Case 123 'no C
Sht.newCells(i, 3) = "CX"
Case 119 'No N
Sht.newCells(i, 4) = "N"
Case 95 'No M
Sht.newCells(i, 6) = "MNo Memo"
Case 115 'No CN
Sht.newCells(i, 3) = "CX"
Sht.newCells(i, 4) = "N"
Case 91 'No CM
Sht.newCells(i, 3) = "CX"
Sht.newCells(i, 6) = "MNo Memo"
Case 83 'No CNM
Sht.newCells(i, 3) = "CX"
Sht.newCells(i, 4) = "N"
Sht.newCells(i, 6) = "MNo Memo"
Case 87 'No NM
Sht.newCells(i, 4) = "N"
Sht.newCells(i, 6) = "MNo Memo"
End Select
coldet = 0
i = i + 1
j = 0
End If
Next
End Sub