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.

 

[footoo]

Sub div()
Dim cel As Range
On Error Resume Next
For Each cel In Selection.SpecialCells(xlCellTypeConstants, 1)
    cel = cel / 1000
Next
On Error GoTo 0
End Sub

 

[ArslanButt]

Thanks. It works like a charm

Sub div()
Dim cel As Range
On Error Resume Next
For Each cel In Selection.SpecialCells(xlCellTypeConstants, 1)
    cel = cel / 1000
Next
On Error GoTo 0
End Sub

 

[Rick Rothstein]

Sub div()
Dim cel As Range
On Error Resume Next
For Each cel In Selection.SpecialCells(xlCellTypeConstants, 1)
    cel = cel / 1000
Next
On Error GoTo 0
End Sub

You do not have to process each cell individually... you can process each area (rectangular group of numeric constants) in the selection separately no matter how many cells are in each area. Note that the structure is almost identical to yours... the only difference is the specifying of the Areas property for the SpecialCells method.

Sub DivideBy1000()
  Dim Ar As Range
  For Each Ar In Selection.SpecialCells(xlConstants).[B][COLOR="#0000FF"]Areas[/COLOR][/B]
    Ar = Ar / 1000
  Next
End Sub

 

[footoo]

You do not have to process each cell individually... you can process each area (rectangular group of numeric constants) in the selection separately no matter how many cells are in each area. Note that the structure is almost identical to yours... the only difference is the specifying of the Areas property for the SpecialCells method.

Sub DivideBy1000()
  Dim Ar As Range
  For Each Ar In Selection.SpecialCells(xlConstants).[B][COLOR=#0000FF]Areas[/COLOR][/B]
    Ar = Ar / 1000
  Next
End Sub

If you loop areas instead of cells, you still need to restrict the specialcells to numbers only, and include an error handler.

 

[footoo]

You do not have to process each cell individually... you can process each area (rectangular group of numeric constants)

Doesn't work for me.

Maybe this :

Sub DivideBy1000()
Dim Ar As Range
On Error Resume Next
For Each Ar In Selection.SpecialCells(xlConstants, 1).Areas
    Ar = Evaluate(Ar.Address & "/ 1000")
Next
End Sub

 

[Rick Rothstein]

If you loop areas instead of cells, you still need to restrict the specialcells to numbers only, and include an error handler.

Whoops! I forgot to include the second argument to the SpecialCells function to handle that problem (I don't think you need an error handler when it is included)...

Sub DivideBy1000()
  Dim Ar As Range
  For Each Ar In Selection.SpecialCells(xlConstants[B][COLOR="#FF0000"], xlNumbers[/COLOR][/B]).Areas
    Ar = Ar / 1000
  Next
End Sub

 

[MARK858]

I don't think you need an error handler when it is included

Hi Rick, you do for me if there is only text (including numbers formatted as text) or blanks in the selection (i.e. no numbers).

 

[Rick Rothstein]

Hi Rick, you do for me if there is only text (including numbers formatted as text) or blanks in the selection (i.e. no numbers).

When I include a mix of real numbers, text numbers, text with no numbers and blanks cells within my selection, the code I posted in Message #7 divides the real numbers by 1000, leaves everything else alone and does not issue an error messages at all... is it really working differently for you? If so, what version of Excel are you using (I am using XL2010)?

 

[MARK858]

When I include a mix of real numbers, text numbers, text with no numbers and blanks cells within my selection, the code I posted in Message #7 divides the real numbers by 1000, leaves everything else alone and does not issue an error messages at all... is it really working differently for you? If so, what version of Excel are you using (I am using XL2010)?

As stated...

you do for me if there is only text (including numbers formatted as text) or blanks in the selection (i.e. no numbers).

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