I have some code that sections off like pieces of data and then adds a total
to the cell to the right one column and down 1 row from the last row of data.
When it hits the first empty line of the spreadsheet I'm getting a run time
error 13 type mismatch error. I'm new to code in excel and salvaged this
from code done by a predecessor. I'm not sure how to fix the problem.

Dim QtyTot As Integer


Range("C5").Select
Columns("c").NumberFormat = "0"

Do
QtyTot = 0


Do

QtyTot = ActiveCell.Value + QtyTot
Selection.Offset(1, 0).Select
If IsEmpty(ActiveCell) Then Exit Do
Loop
Selection.Offset(-0, 1).Select
ActiveCell.Value = QtyTot
Selection.Offset(1, -1).Select
If IsEmpty(ActiveCell) Then Exit Do
Loop
Range("a1").Select
Calculate

Hi glenda

try following sub:
------------------------------------------------------------------------------
Sub glenda()

Dim tot As Integer
'maybe put double instead of integer
'if your numbers have decimal points

For i = 1 To Worksheets("sheet2").Cells(65536, 1).End(xlUp).Row
'sheets2 is the name of the sheet you want to do that calculation
'it has to be replaced in the whole sub
'change the 1 in Cells(65536,1) to the column you want to check for
'your numbers to calculate 1 = A, 2 = B and so on
If Worksheets("sheet2").Cells(i, 1).Value = "" Then
Worksheets("sheet2").Cells(i - 1, 2).Value = tot
'change the 2 in Cells(i-1,2) to the column you want to enter
'the calculated values.
tot = 0
Else
tot = tot + Worksheets("sheet2").Cells(i, 1).Value
End If
Next i

Worksheets("sheet2").Cells(i - 1, 2).Value = tot
columns("c").numberformat = "0"

End Sub
------------------------------------------------------------------------------

Hope that helps, otherwise just ask.

Carlo

"Glenda" wrote:

I have some code that sections off like pieces of data and then adds a total
to the cell to the right one column and down 1 row from the last row of data.
When it hits the first empty line of the spreadsheet I'm getting a run time
error 13 type mismatch error. I'm new to code in excel and salvaged this
from code done by a predecessor. I'm not sure how to fix the problem.

Dim QtyTot As Integer


Range("C5").Select
Columns("c").NumberFormat = "0"

Do
QtyTot = 0


Do

QtyTot = ActiveCell.Value + QtyTot
Selection.Offset(1, 0).Select
If IsEmpty(ActiveCell) Then Exit Do
Loop
Selection.Offset(-0, 1).Select
ActiveCell.Value = QtyTot
Selection.Offset(1, -1).Select
If IsEmpty(ActiveCell) Then Exit Do
Loop
Range("a1").Select
Calculate

Hi glenda,

Carlo is right in that an integer can only hold a number in the range
of -32,768 to 32,767. However, I think that the actual error indicates a
value that cannot be converted to a numeric such as an alpha character.

Other than that, the code you have does work. You could use the following to
identify the invalid data:-

Dim QtyTot As Integer

Range("C5").Select
Columns("c").NumberFormat = "0"

Do
QtyTot = 0


Do
'Following code will find invalid cell
If Not IsNumeric(ActiveCell) Then
MsgBox "Cell " & ActiveCell.Address & " Invalid"
Exit Sub
End If

QtyTot = ActiveCell.Value + QtyTot
Selection.Offset(1, 0).Select
If IsEmpty(ActiveCell) Then Exit Do
Loop
Selection.Offset(-0, 1).Select
ActiveCell.Value = QtyTot
Selection.Offset(1, -1).Select
If IsEmpty(ActiveCell) Then Exit Do
Loop
Range("a1").Select
Calculate


Regards,

OssieMac

Regards,

OssieMac

"Carlo" wrote:

Hi glenda

try following sub:
------------------------------------------------------------------------------
Sub glenda()

Dim tot As Integer
'maybe put double instead of integer
'if your numbers have decimal points

For i = 1 To Worksheets("sheet2").Cells(65536, 1).End(xlUp).Row
'sheets2 is the name of the sheet you want to do that calculation
'it has to be replaced in the whole sub
'change the 1 in Cells(65536,1) to the column you want to check for
'your numbers to calculate 1 = A, 2 = B and so on
If Worksheets("sheet2").Cells(i, 1).Value = "" Then
Worksheets("sheet2").Cells(i - 1, 2).Value = tot
'change the 2 in Cells(i-1,2) to the column you want to enter
'the calculated values.
tot = 0
Else
tot = tot + Worksheets("sheet2").Cells(i, 1).Value
End If
Next i

Worksheets("sheet2").Cells(i - 1, 2).Value = tot
columns("c").numberformat = "0"

End Sub
------------------------------------------------------------------------------

Hope that helps, otherwise just ask.

Carlo

"Glenda" wrote:

I have some code that sections off like pieces of data and then adds a total
to the cell to the right one column and down 1 row from the last row of data.
When it hits the first empty line of the spreadsheet I'm getting a run time
error 13 type mismatch error. I'm new to code in excel and salvaged this
from code done by a predecessor. I'm not sure how to fix the problem.

Dim QtyTot As Integer


Range("C5").Select
Columns("c").NumberFormat = "0"

Do
QtyTot = 0


Do

QtyTot = ActiveCell.Value + QtyTot
Selection.Offset(1, 0).Select
If IsEmpty(ActiveCell) Then Exit Do
Loop
Selection.Offset(-0, 1).Select
ActiveCell.Value = QtyTot
Selection.Offset(1, -1).Select
If IsEmpty(ActiveCell) Then Exit Do
Loop
Range("a1").Select
Calculate

Where I said a non numeric character, it might not be a visible character.
You might have to delete the contents of the cell and re enter it.

Regards,

OssieMac

"OssieMac" wrote:

Hi glenda,

Carlo is right in that an integer can only hold a number in the range
of -32,768 to 32,767. However, I think that the actual error indicates a
value that cannot be converted to a numeric such as an alpha character.

Other than that, the code you have does work. You could use the following to
identify the invalid data:-

Dim QtyTot As Integer

Range("C5").Select
Columns("c").NumberFormat = "0"

Do
QtyTot = 0


Do
'Following code will find invalid cell
If Not IsNumeric(ActiveCell) Then
MsgBox "Cell " & ActiveCell.Address & " Invalid"
Exit Sub
End If

QtyTot = ActiveCell.Value + QtyTot
Selection.Offset(1, 0).Select
If IsEmpty(ActiveCell) Then Exit Do
Loop
Selection.Offset(-0, 1).Select
ActiveCell.Value = QtyTot
Selection.Offset(1, -1).Select
If IsEmpty(ActiveCell) Then Exit Do
Loop
Range("a1").Select
Calculate


Regards,

OssieMac

Regards,

OssieMac

"Carlo" wrote:

Hi glenda

try following sub:
------------------------------------------------------------------------------
Sub glenda()

Dim tot As Integer
'maybe put double instead of integer
'if your numbers have decimal points

For i = 1 To Worksheets("sheet2").Cells(65536, 1).End(xlUp).Row
'sheets2 is the name of the sheet you want to do that calculation
'it has to be replaced in the whole sub
'change the 1 in Cells(65536,1) to the column you want to check for
'your numbers to calculate 1 = A, 2 = B and so on
If Worksheets("sheet2").Cells(i, 1).Value = "" Then
Worksheets("sheet2").Cells(i - 1, 2).Value = tot
'change the 2 in Cells(i-1,2) to the column you want to enter
'the calculated values.
tot = 0
Else
tot = tot + Worksheets("sheet2").Cells(i, 1).Value
End If
Next i

Worksheets("sheet2").Cells(i - 1, 2).Value = tot
columns("c").numberformat = "0"

End Sub
------------------------------------------------------------------------------

Hope that helps, otherwise just ask.

Carlo

"Glenda" wrote:

I have some code that sections off like pieces of data and then adds a total
to the cell to the right one column and down 1 row from the last row of data.
When it hits the first empty line of the spreadsheet I'm getting a run time
error 13 type mismatch error. I'm new to code in excel and salvaged this
from code done by a predecessor. I'm not sure how to fix the problem.

Dim QtyTot As Integer


Range("C5").Select
Columns("c").NumberFormat = "0"

Do
QtyTot = 0


Do

QtyTot = ActiveCell.Value + QtyTot
Selection.Offset(1, 0).Select
If IsEmpty(ActiveCell) Then Exit Do
Loop
Selection.Offset(-0, 1).Select
ActiveCell.Value = QtyTot
Selection.Offset(1, -1).Select
If IsEmpty(ActiveCell) Then Exit Do
Loop
Range("a1").Select
Calculate