Macro to convert cm to inches paste value formula

[EnricoF]

Hello,

I was wondering if I could get some help. I have written a simple macro to convert a range from cm to inches (see below). The draw back to this macro is that I have a simple minus formula in the range that is being pasted as value when I run the macro. I need the macro to not paste value the formula but still convert the formulas results to inches.

For Each cell In Range("C10:C250")
If cell.Interior.Color = RGB(204, 192, 218) Then
Else
If IsEmpty(cell) Then
Else
If IsNumeric(cell) Then


cell = WorksheetFunction.Convert(cell.Value, "cm", "in")
End If
End If
End If
Next cell


Your help will be greatly appreciated


Enrico

 

[RickXL]

Hi,

You might be able to use something like this.

But beware that if formulas in one row link to formulas in another then you could get the wrong answer because the conversion could be done twice.

Sub TestConvert()
    Dim cell As Range
    For Each cell In Range("C10:C250")
        Select Case True
            Case cell.Interior.Color = RGB(204, 192, 218)
            Case cell.Value = vbNullString
            Case cell.HasFormula: cell = cell.Formula & "/2.54"
            Case IsNumeric(cell): cell = cell.Value / 2.54
            Case Else: Debug.Print "Other: "; cell.Value
        End Select
    Next
End Sub

Select Case works like multiple IF statements. In the example above, it finds the foirst case that is true then performs that action. It skips the remaining options.
the Else case is performed if none of the others is executed. The first two do nothing. The third one adds a "/2.54" to the formula. The fourth one recalculates the number then writes it back.