VBA Question

[Guest]

I need to know what VBA code to use to write a procedure that will do these
steps:

Look at each cell on the worksheet from (row1, col2) to (endrow, endcol.)
If a cell is blank, leave the cell blank.
If a cell is not blank, convert that cell to a number, or do a paste special
to multiply that cell times the number 1 placed in a spare blank cell on the
spreadsheet.

Thanks.

 

[JE McGimpsey]

One way:

Public Sub ConvertToValues()
On Error Resume Next
With ActiveSheet
With Intersect(.Range("B:IV"), .UsedRange)
With Union(.SpecialCells(xlCellTypeConstants, _
xlTextValues), .SpecialCells( _
xlCellTypeFormulas, xlNumbers))
.Value = .Value
End With
End With
End With
On Error GoTo 0
End Sub

 

[Guest]

Magnificent. I appreciate your assistance, JE.

One way:

Public Sub ConvertToValues()
On Error Resume Next
With ActiveSheet
With Intersect(.Range("B:IV"), .UsedRange)
With Union(.SpecialCells(xlCellTypeConstants, _
xlTextValues), .SpecialCells( _
xlCellTypeFormulas, xlNumbers))
.Value = .Value
End With
End With
End With
On Error GoTo 0
End Sub