CODEWITHSUNDEEP
excelvba
Muhammad Uzair Balagamwala
Muhammad Uzair Balagamwala

Reputation: 3

Adding a formula in an active range using the contents of the cell in that range

I want to add a formula to a range of cells using the contents of the cell.

I am relatively new to VBA and I want to make a macro that reduces my work.

The result should be something like this. Using Round formula as an example.

For example, I select a range of cells and the macro adds the formula to the selected range using the contents of that cell. The below image might be clearer in explaining what I want.

Expected Result: Expected Result

Upvotes: 0

Views: 133

Answers (1)

Bálint Kolosi
Bálint Kolosi

Reputation: 83

Sub ApplyRoundFormula()

For Each cell In Selection.Cells
    If cell.HasFormula Then
        StrCurrentFormula = cell.Formula
        StrCurrentFormula = Mid(StrCurrentFormula, 2, 999)
        cell.Formula = "=ROUND(" & StrCurrentFormula & ",0)"
    ElseIf IsNumeric(cell) Then
        cell.Formula = "=ROUND(" & cell.Value & ",0)"
    End If
Next

End Sub

This script loops through each cell of a selected range (you can change Selection to any range reference), if it has formulas, it crops the equation mark from the beginning and puts the rest into a ROUND formula. If it doesn't have a formula but it has a numeric value, it puts that numeric value into a ROUND formula.

Upvotes: 0

Related Questions