PDA

View Full Version : Solved: Setting Cell Value = #N/A



ajrob
12-11-2009, 07:05 PM
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!!


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

Paul_Hossler
12-11-2009, 07:13 PM
I wasn't sure which cells you wanted to make #N/A



Cells(1, 1).Value = CVErr(xlErrNA)



Paul

ajrob
12-11-2009, 07:18 PM
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
12-11-2009, 07:37 PM
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
12-12-2009, 11:22 AM
Thanks, this worked!

mdmackillop
12-13-2009, 11:34 AM
Hi Ajrob,
When you post code, please select it and click the green VBA button to format it as shown.
Regards
MD