• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 756
  • Last Modified:

Need to clear contents of an Excel cell that has a value of $0.00 using Microsoft Access VBA!

Please note code below used to send a SQL set of data to Microsoft Excel's object.
I need to be able to add one more step  - if any value in columns AR - AZ comes out to $0.00 (which in reality is =SUM(AI2:AP2) go ahead and do a Clear Contents on that cell so it is blank - I need to do it for that entire range of columns and rows - every row under columns AR - AZ.

What syntax or statement I can add using Access VBA that would allow me to do that?

Thank you sincerely in advance.

Stephen

 Set xlApp = CreateObject("Excel.Application")
  'Opens Excel TEMPlate always from the C:\ to reduce traffic
  Set xlBook = xlApp.Workbooks.Open(sFile)
  
  Set xlSheet = xlBook.Worksheets(1)
  xlSheet.Activate
  xlSheet.Application.Visible = True
  
  'sSQL = "SELECT * FROM TEMP"
  
  If sRange = "" Then sRange = "A2"
  
  Set rs = Application.CurrentProject.Application.CurrentDb.OpenRecordset(sSQLExcel)
  With xlSheet
    '.Range("C1").Value = Date
    '.Range("A5").copyfromrecordset rs
    .Range(sRange).copyfromrecordset rs
    '.Name = strSheetName
  End With
  
On Error GoTo Err_Skip_Columns

  xlSheet.Sheets(1).Columns("AI").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AJ").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AK").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AL").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AM").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AN").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AO").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AP").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AQ").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AR").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AS").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AT").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AU").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AV").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AW").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AX").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AY").TextToColumns DataType:=1
  xlSheet.Sheets(1).Columns("AZ").TextToColumns DataType:=1
  
  
Err_Skip_Columns:
  wb.Sheets(1).Range("AI:AZ").NumberFormat = "$#,##0.00"
  

Open in new window

0
stephenlecomptejr
Asked:
stephenlecomptejr
  • 4
  • 4
1 Solution
 
Rgonzo1971Commented:
Hi,

You could try this format

wb.Sheets(1).Range("AR:AZ").NumberFormat = "$#,##0.00;$-#,##0.00;"""""
or clear contents

For Each c In Intersect(wb.Sheets(1).UsedRange, wb.Sheets(1).Range("AR:AZ"))
    If c.Value = 0 Then
        c.ClearContents
    End If
Next


Regards
0
 
stephenlecomptejrAuthor Commented:
When I try :
 wb.Sheets(1).Range("AR:AZ").NumberFormat = "$#,##0.00;$-#,##0.00;"""""

I get a object doesn't support this property or method.

When I try the second one instead:

For Each c in Intersect

I get a sub or function not defined with it highlighting Intersect.  Please note this uses late binding because some may have different version of Microsoft Excel.
0
 
Rgonzo1971Commented:
Hi,

pls try

For Each c in xlApp.Intersect
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
stephenlecomptejrAuthor Commented:
Also should c be an integer?
0
 
Rgonzo1971Commented:
Maybe the format could be

wb.Sheets(1).Range("AR:AZ").NumberFormat = "$#,##0.00;$-#,##0.00;"
0
 
Rgonzo1971Commented:
c is an Excel range
0
 
stephenlecomptejrAuthor Commented:
The first line worked for me.  My problem was I was using xlSheet. versus xlBook. as his code indicates wb for Workbook.
0
 
stephenlecomptejrAuthor Commented:
Thanks again Rgonzo1971 - I appreciate it.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now