vba excel - syntax for accounting Number Format

devofish

Board Regular
Joined
Dec 10, 2016
Messages
68
What is the correct syntax to number format a cell to within a loop? I have:
Code:
  dws.Range("B" & dlr).value = cell.value            
            dws.Range("C" & dlr).value = cell.Offset(0, 1).value
            dws.Range("F" & dlr).value = cell.Offset(0, 3).value
            dws.Range("G" & dlr).value = cell.Offset(0, 4).value
            dws.Range("H" & dlr).value = cell.Offset(0, 5).value
            dws.Range("I" & dlr).value = cell.Offset(0, 2).value
            dws.Range("J" & dlr).value = cell.Offset(0, 6).value
I need to have cell.Offset(0, 5).value to be in accounting number format. Can someone educate me on the correct syntax. I've tried:
Code:
Format(cell.Offset(0, 5).value, "($*#,##0.00_);_($*(#,##0.00);_($*””_””??_);_(@_)")
The code does not throw an error, but it does not output the correct result. Many thanks for any help.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,929
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Use the cell's Numberformat property:

Code:
cell.Offset(0, 5).numberformat = "($*#,##0.00_);_($*(#,##0.00);_($*""_""??_);_(@_)")
 

devofish

Board Regular
Joined
Dec 10, 2016
Messages
68
Thanks Rory. Where does this go in my scheme since this snippet is part of a loop?
Code:
For Each cell In tbl.DataBodyRange.Columns(2).Cells
        If cell <> "" Then            chkStr = cell.value
            If Not ServiceFound(chkStr) Then
                If dws.Range("B17").value = "" Then
                    dlr = 17
                Else
                    Set CodeCell = dws.Range("B:B").Find(What:="iCode", after:=dws.Range("B16"), LookAt:=xlWhole)
                    dlr = CodeCell.End(xlUp).Row + 1
                    dws.Rows(dlr).Insert
                    BottomCell.Offset(-4).EntireRow.Delete
                End If                
            dws.Range("B" & dlr).value = cell.value
            dws.Range("C" & dlr).value = cell.Offset(0, 1).value
            dws.Range("F" & dlr).value = cell.Offset(0, 3).value
            dws.Range("G" & dlr).value = cell.Offset(0, 4).value
            dws.Range("H" & dlr).value = cell.Offset(0, 5).value
            dws.Range("I" & dlr).value = cell.Offset(0, 2).value
            dws.Range("J" & dlr).value = cell.Offset(0, 6).value
            End If
        End If
Next cell
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,929
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Oh, I see, you want the other cell formatted. Change this:

Code:
dws.Range("H" & dlr).value = cell.Offset(0, 5).value

to this:

Code:
With dws.Range("H" & dlr)
.value = cell.Offset(0, 5).value
.numberformat = "($*#,##0.00_);_($*(#,##0.00);_($*""_""??_);_(@_)")
End With

though you could just format the entire column outside the loop:

Code:
dws.Range("H:H").numberformat = "($*#,##0.00_);_($*(#,##0.00);_($*""_""??_);_(@_)")
 

devofish

Board Regular
Joined
Dec 10, 2016
Messages
68
Thanks, Rory. I went with the with statement, since there are data in other H cells that aren't formatted similarly.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,260
Messages
5,852,931
Members
431,532
Latest member
Lroy9827

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top