VBA code to divide range of cells by 1000 excluding blank and formula cells

[ArslanButt]

Hi every one, i am newly registered but have been a vivid follower of this forum since last couple of year. Can anyone write a short code as i need to divide range of cells by 1000 but exclude blank and formula cells ? Any help will be highly appreciated.

 

[Rick Rothstein]

As stated...

If there are no real numbers in the range it does error.

I seem to be dense this morning. I read your "in the selection" as meaning along with other real numbers and completely forgot about the "no cells found" error when only non-(real) numbers were the only thing in the selection. Sorry... you are correct, of course... an On Error trap is needed with my code as well.

 

[MARK858]

No problem (I'm dense every morning )

 

[footoo]

I get Error 13 with this line:

Ar = Ar / 1000

Need to use this instead :

Ar = Evaluate(Ar.Address & "/ 1000")

 

[MARK858]

I get Error 13 with this line:

Ar = Ar / 1000

I get the same (I only tested earlier for if the data wasn't numbers as I was sure that would error).

 

[footoo]

Depending upon the data to process, this might be more efficient :

Sub DivideBy1000()
Dim rng As Range
On Error Resume Next
Set rng = Selection.SpecialCells(xlConstants, 1)
On Error GoTo 0
If Not rng Is Nothing Then
    With Cells(Rows.Count, "A").End(xlUp)(2)
        .Value = 1000
        .Copy
        rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlDivide
        Application.CutCopyMode = False
        .ClearContents
    End With
End If
End Sub