How to work only with selected range of cells in the table?

[avkokin]

Hello.
I have a table with the cells containing both numbers, and the usual
text. There are also merged of cells. I need to work only with the
selected cells containing numbers (I reduce each value by 10 %). I use
following my macro (below). But it works and on cells with the text
(of course, I got an error).
Question: how to work only with the selected range of cells in the
table?
Thanks.

Sub table10percent()
Dim rngTable As Range
Dim oCell As Cell
Dim sStr As Variant
Dim i As Long
Set rngTable = Selection.Range
With rngTable
For Each oCell In .Cells
sStr = oCell.Range.Text
sStr = Left(sStr, Len(sStr) - 2)
If IsNumeric(sStr) Then
sStr = CSng(sStr)
End If
sStr = percent(sStr)
oCell.Range.Text = Round(sStr, 3)
Next oCell
End With
End Sub

Public Function percent(a As Variant) As Variant
Dim num As Variant
num = a * 0.1
percent = a - num
End Function

 

[Greg Maxey]

Not sure I understand exactly, but have you tried expanding the scope of
your IF ... End If statement:

If IsNumeric(sStr) Then
sStr = CSng(sStr)
sStr = percent(sStr)
oCell.Range.Text = Round(sStr, 3)
End If

 

[avkokin]

Yes, it's correct! Thank you, Gregory!

Not sure I understand exactly, but have you tried expanding the scope of
your IF ... End If statement:

  If IsNumeric(sStr) Then
    sStr = CSng(sStr)
    sStr = percent(sStr)
    oCell.Range.Text = Round(sStr, 3)
  End If






- Show quoted text -