Solved: Setting Cell Value = #N/A

[ajrob]

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]

[Paul_Hossler]

I wasn't sure which cells you wanted to make #N/A


[VBA]
Cells(1, 1).Value = CVErr(xlErrNA)
[/VBA]


Paul

[ajrob]

Actually, I want to set the variable BkgTot = #n/a when one of the three columns is empty. In my code, can I substitute:

BkgTot = CVErr(xlErrNA)
for
BkgTot = 0

[Paul_Hossler]

If you Dim-ed BkgTot as a variant, you should be able to assign an Error to it

I don't know about the effects to the rest of your macro

I was just looking at ...

I want to write #n/a to the cell in the separate sheet so that it doesn't get picked up in the chart.

Paul

[ajrob]

Thanks, this worked!

[mdmackillop]

Hi Ajrob,
When you post code, please select it and click the green VBA button to format it as shown.
Regards
MD