vba excel - syntax for accounting Number Format

[devofish]

What is the correct syntax to number format a cell to within a loop? I have:

  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:

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.

 

[RoryA]

Use the cell's Numberformat property:

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

 

[devofish]

Thanks Rory. Where does this go in my scheme since this snippet is part of a loop?

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]

Oh, I see, you want the other cell formatted. Change this:

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

to this:

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:

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

 

[devofish]

Thanks, Rory. I went with the with statement, since there are data in other H cells that aren't formatted similarly.