I wrote the following quick piece of code that sums cells on a single row, but across three consecutive columns. The sum is then written to a new cell on a separate sheet in the same workbook. This data is collected in a row, and used for charting
The list of data across columns is updated with monthly data, so eventually it goes blank. When this happens, I want to write #n/a to the cell in the separate sheet so that it doesn't get picked up in the chart.
Right now, I've set it = 0, because I can't figure this out. Help!!
[VBA]
Sub Sum_3_Mo_Bkgs()
Dim iRow As Integer
Dim xRow As Integer
Dim yCol As Integer
Dim BkgTot As Integer
iRow = 0
xRow = 38
yCol = 3
BkgTot = 0
Do Until yCol = 10
Sheets("Bkg Charts 1").Select
If IsEmpty(Cells(xRow, yCol)) Then
BkgTot = 0
ElseIf IsEmpty(Cells(xRow, yCol + 1)) Then
BkgTot = 0
ElseIf IsEmpty(Cells(xRow, yCol + 2)) Then
BkgTot = 0
Else
BkgTot = Application.WorksheetFunction.Sum(Cells(xRow, yCol), _
Cells(xRow, yCol + 1), Cells(xRow, yCol + 2))
End If
Sheets("Lists_Data").Select
Range("Y" & 35 + iRow).FormulaR1C1 = BkgTot
iRow = iRow + 1
yCol = yCol + 1
Loop
End Sub
[/VBA]