Getting Type Mistmatch error when using .Numberformat

[NimishK]

Hello

I am Getting Type Mistmatch error when using .Numberformat marked in Red
Did I miss something here therefore am i getting Type Mismatch error or is it the wrong method that i've adopted

Public Sub nmbrFormat()

Dim rNos As Range, cellsRNos As Range
Dim wsMis As Worksheet
Set wsMis = Worksheets("Sheet4")

With wsMis
   Set rNos = Union(.Columns("BF"), .Columns("BG"), .Columns("BH"), .Columns("BI"), _
   .Columns("BK"), .Columns("BL"), .Columns("BM"), .Columns("BN"), .Columns("BP"), .Columns("BQ"), _
   .Columns("CG"), .Columns("CH"), .Columns("CI"), .Columns("CJ"))
End With

For Each cellsRNos In rNos
[COLOR=#ff0000]   cellsRNos.NumberFormat = Trim(Replace(Format(String(Len(Int(cellsRNos.value)) - 1, "#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00"[/COLOR]
Next

NimishK

 

[Norie]

What do you actually have in the cells you are looping through?

 

[NimishK]

What do you actually have in the cells you are looping through?

But Natural the numeric values in other words (Amount).
Are you trying to indicate the error i am facing is it because of Blank Cells in the columns

rNos is set to range of columns
and cellsRnos is again set to cells of amount of rNos

 

[Norie]

Can you post some examples of the values?

 

[NimishK]

in col CI 458,660

1108,000

94,400

146,320

<tbody>
</tbody>

Also the values are displayed as Formula

CI263=-(BF263-BK263-BP263)

 

[Norie]

When you get the error what is the value of cellsRNos?

 

[NimishK]

Private Sub Workbook_Open()
call [COLOR=#333333]nmbrFormat[/COLOR]
End Sub

 

[NimishK]

I am not quite sure is it because of Header Row which contains text and that is why Error

When you get the error what is the value of cellsRNos?

How can i check the above ?

 

[Norie]

If there is a header row then it's going to be included in your loop because you are using entire columns.

To skip the header row you could try something like this, which is based on the header row being in row 1.

For Each cellsRNos In rNos
    If cellsRNos.Row > 1 Then
        cellsRNos.NumberFormat = Trim(Replace(Format(String(Len(Int(cellsRNos.value)) - 1, "#"), " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00"
    End If
Next

 

[NimishK]

Thanks yes it worked except in between there was error 2015 which again checked on sheet as there was blank cell which turned to give formula error. Now Rectified
Thank you so much