Consulting

Results 1 to 6 of 6

Thread: Solved: Setting Cell Value = #N/A

  1. #1
    VBAX Regular
    Joined
    Aug 2006
    Posts
    36
    Location

    Solved: Setting Cell Value = #N/A

    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]

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    I wasn't sure which cells you wanted to make #N/A


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


    Paul

  3. #3
    VBAX Regular
    Joined
    Aug 2006
    Posts
    36
    Location
    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

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    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

  5. #5
    VBAX Regular
    Joined
    Aug 2006
    Posts
    36
    Location
    Thanks, this worked!

  6. #6
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Hi Ajrob,
    When you post code, please select it and click the green VBA button to format it as shown.
    Regards
    MD
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •