Vb6 Excel Set Range NumberFormat 2

[Trob70]

Set Excelapp = CreateObject("Excel.Application")

Set ExcelWorkbook = Excelapp.Workbooks.Add 'Then 1 2 and 3 Sheets
Set ExcelSheet = ExcelWorkbook.Worksheets(1)
ExcelSheet.Name = "All"
Set ExcelSheet2 = ExcelWorkbook.Worksheets(2)
ExcelSheet2.Name = "A"
Set ExcelSheet3 = ExcelWorkbook.Worksheets(3)
ExcelSheet3.Name = "O"






Dim xSheets As Integer

For xSheets = 1 To 3 ' Main Loop.>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

With ExcelWorkbook.Worksheets(xSheets)


Excelapp.Range("D13000").Select
Excelapp.Selection.NumberFormat = "$#,##0.00"
Excelapp.Range("E1:E3000").Select
Excelapp.Selection.NumberFormat = "$#,##0.00"
Excelapp.Range("F1:F3000").Select
Excelapp.Selection.NumberFormat = "$#,##0.00"
Excelapp.Range("G1:E3000").Select
Excelapp.Selection.NumberFormat = "$#,##0.00"
Excelapp.Range("H1:F3000").Select
Excelapp.Selection.NumberFormat = "$#,##0.00"

'The above works but only formats the 1st Sheet

???how do i use the folowing code to format Sheets 1 , 2 ,3 ...object does not support thsi property or method

.Range("D13000").Select
.Selection.NumberFormat = "$#,##0.00"


next

End With



Regards Robert

 

[strongm]

Assuming that there is other work being done within the With loop

[blue]For xSheets = 1 To 3 ' Main Loop.>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    With ActiveWorkbook.Worksheets(xSheets)
        .Range("D1:H3000").NumberFormat = "$#,##0.00"
    End With
Next[/blue]

But if you are not doing any other work in the With loop, then you could go with

[blue]For xSheets = 1 To 3 ' Main Loop.>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
    ActiveWorkbook.Worksheets(xSheets).Range("D1:H3000").NumberFormat = "$#,##0.00"
Next[/blue]

 

[HughLerwill]

Try something like;

Option Explicit
Private Sub Command1_Click()

With CreateObject("Excel.Application")
 
    .SheetsInNewWorkbook = 3
    With .Workbooks.Add
        .sheets(1).Name = "All"
        .sheets(2).Name = "A"
        .sheets(3).Name = "0"
    End With
    Dim sh As Object
    For Each sh In .activeworkbook.sheets
        sh.Range("D1:D3000").NumberFormat = "$#,##0.00"
        sh.Range("D1:D3").interior.colorindex = sh.Index   
        sh.Range("E1:E3000").NumberFormat = "$#,##0.00"
        sh.Range("E1:E3").interior.colorindex = sh.Index + 1
        sh.Range("F1:F3000").NumberFormat = "$#,##0.00"
        sh.Range("F1:F3").interior.colorindex = sh.Index + 2
        sh.Range("G1:G3000").NumberFormat = "$#,##0.00"
        sh.Range("G1:G3").interior.colorindex = sh.Index + 3
        sh.Range("H1:H3000").NumberFormat = "$#,##0.00"
        sh.Range("H1:H3").interior.colorindex = sh.Index + 4
    Next
    .Visible = True
 End With
 
End Sub

Where the lines like 'sh.Range("D13").interior.colorindex = sh.Index' are just for entertainment, delete them when you have it working.

 

[Trob70]

Strongm.. Thanks again for your help.


HughLerwill ..The other day i was wondering how to use as object in this situation
Thanks for the tip much appreciated.


Regards Robert