Ignore cell with text, dicide the rest by 1000 with condition

[LazySun]

Hi, I have an Excel table converted from PDF. It contains text and range of number. When I use the 1st procedure, if I understand correctly, it should select cell with constant to include in the result? Somehow I still include the text part? Using the 2nd code is okay but It took long time to edit the table.

Sub DividedbyThousand()

Dim ConstantCells As Range

Dim cell As Range

Set ConstantCells = Selection.SpecialCells(xlCellTypeConstants)

For Each cell In ConstantCells

If cell.Value > -100 Then

cell.Value = cell.Value / 1000

End If

Next cell

For Each cell In ConstantCells

If cell.Value < -100 Then

cell.Value = cell.Value / 1000

End If

Next cell



End Sub

Sub DividedbyThousandv2()

Dim cell As Range



For Each cell In Selection

If cell.Value > 100 Then

cell.Value = cell.Value / 1000

ElseIf cell.Value < -100 Then

cell.Value = cell.Value / 1000

End If

Next cell



End Sub

 

[bebo021999]

cell with text is known as constant and greater then any value.
Try below code, with only 1 loop by using ABS:

Sub DividedbyThousand()
Dim ConstantCells As Range
Dim cell As Range
Set ConstantCells = Selection.SpecialCells(xlCellTypeConstants)
    For Each cell In ConstantCells
        If IsNumeric(cell.Value) Then ' work with cell with value only
            If Abs(cell.Value) > 100 Then cell.Value = cell.Value / 1000 ' if cell<-100 or cell >100
        End If
    Next
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

With SpecialCells(xlCellTypeConstants) you can specify the type of constants by adding a second argument, thereby eliminating the text values. That would be an easy change to your existing macro and you would not have to test each cell to see if it is a number.

 Set ConstantCells = Selection.SpecialCells(xlCellTypeConstants, xlNumbers)

BTW, when posting vba code in the forum, please use the available code tags. Then your code retains the indentation etc of your actual code like in your image and like the code in post #2, making it much easier to read &/or debug. My signature block below has more details.

 

[Peter_SSs]

Another option you could consider is to process them all at once, rather than looping one at a time.
Test with a copy of your data.

Sub Divide1000()
  With Selection
    .Value = Evaluate(Replace("if(#="""","""",if(istext(#),#,if(abs(#)<=100,#,#/1000)))", "#", .Address))
  End With
End Sub

 

[LazySun]

Welcome to the MrExcel board!

With SpecialCells(xlCellTypeConstants) you can specify the type of constants by adding a second argument, thereby eliminating the text values. That would be an easy change to your existing macro and you would not have to test each cell to see if it is a number.

 Set ConstantCells = Selection.SpecialCells(xlCellTypeConstants, xlNumbers)

BTW, when posting vba code in the forum, please use the available code tags. Then your code retains the indentation etc of your actual code like in your image and like the code in post #2, making it much easier to read &/or debug. My sddignature block below has more details.

Thanks for the reply, I will follow the guidelines for next post.

 

[Peter_SSs]

I will follow the guidelines for next post.

No problem.

Did you compare the 'Evaluate' method for speed?

 

[LazySun]

No problem.

Did you compare the 'Evaluate' method for speed?

I did find an improvement in dividing by 1000 with my version as below. Sorry for late reply due to many tasks.

Sub DividedbyThousand()

Dim ConstantCells As Range

Dim cell As Range

Set ConstantCells = Selection.SpecialCells(xlCellTypeConstants)

For Each cell In ConstantCells

If cell.Value > 100 Then

cell.Value = cell.Value / 1000

End If

Next cell

For Each cell In ConstantCells

If cell.Value < 100 Then

cell.Value = cell.Value / 1000

End If

Next cell

End Sub

Thank you all of you for your kind support.