Add Cells error
 

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

Add Cells error CORRECTION
 

I copied the wrong line, should be

shtnew.Cells(i, j).Value = v

as corrected below

On 4/5/09 10:18 AM, in article
, "Gene Augustin"
wrote:

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, j).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

Add Cells error
 

I'd add a couple of msgboxes to see the values of the cell and the counter:

msgbox k
msgbox cells(k,1).text
then maybe use:
v = Cells(k, 1).text 'v= Data Value




Gene Augustin wrote:

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

--

Dave Peterson