Microsoft Office Forums - View Single Post

BobBridges · 04-09-2020, 07:12 PM

Ok, I made one that actually works. In column O you put the various formulae the user wants to be applied, with a single quote in front so that Excel will know to treat that formula as the displayed value of the cell rather than a formula itself. And wherever there's a reference to the cell that has the filename, for example "A2", replace it with some unique text. I used "ZZ". Like this:

Code:

'=MID(ZZ,13,5)

That's for the formula. Your VBA statement gets the value from the correct cell in the table—you already know how to do that, I guess—and then uses the Replace function to change "ZZ" to "A2"-or-whatever, and assigns to the formula of the cell in column K. Like this, perhaps:

Code:

ows.Cells(jr, 11).Formula = Replace(ows.Cells(vr, 15).Value, "ZZ", "A" & jr)

...where jr is the row of the filename you're evaluating, and vr is the row number of the correct formula corresponding to the length of the filename.