VBA - Change formula to value if a certain Condition is met

**picton2000** · 03-08-2025, 06:31 PM

I have a range of cells from A1 down to about Z1000, but the range does vary.
The cells contain data pulled in from various spreadsheets by data link.

What I need is vba to assess the range and if a cell contains "SM Input" in the formula path then the cell stays as a formula.
Any cell that does not contain "SM Input" then it converts the formula in that cell to a value.

My finished range is then either a cell value or a cell formula which contain "SM Input" somewhere in the link path.

Does a VBA routine exist that will achieve this ?

Many thanks for any help you can give.

**6StringJazzer** · 03-08-2025, 10:48 PM

This is not especially hard but I am not clear on what you mean by "formula path". What exactly does the formula look like?

Here is the idea but unable to perform a reliable test without your file. Also, to confirm, you want to check cells in all 26 columns?

EDIT: The parameters were reversed in my InStr call. See red for correction

Please Login or Register  to view this content.

**bebo021999** · 03-08-2025, 10:51 PM

Try this:

PHP Code:


Sub test()

Dim ce As Range

For Each ce In ActiveSheet.UsedRange

    If InStr(1, ce.Formula, "SM Input") = 0 Then

        ce.Value = ce.Value

    End If

Next

End Sub

**picton2000** · 03-10-2025, 10:56 AM

Thanks, your code worked.

Much appreciated.

VBA - Change formula to value if a certain Condition is met

LinkBack

Thread Tools

Rate This Thread

Display

VBA - Change formula to value if a certain Condition is met

Re: VBA - Change formula to value if a certain Condition is met

Re: VBA - Change formula to value if a certain Condition is met

Re: VBA - Change formula to value if a certain Condition is met

Thread Information

Users Browsing this Thread

Similar Threads

cell change color with formula and condition

Change formula if a condition met

[SOLVED] Need to change a Sumproduct Formula to incorporate a condition

[SOLVED] Change cell formula (C4) when a if condition is met

How to change the formula by its calculated value if condition is true ?

Excel 2007 : How to change Sheet1 tab's color, based on a formula condition?

auto change cell formula on condition of worksheet change in other cells

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions