CODEWITHSUNDEEP
excelvbareplaceexcel-formula
Lennard W.
Lennard W.

Reputation: 11

Excel VBA Replace is not working with formulas

I tried to replace a string form value in order to create a formula:

Cell:

"XY=(TRIM(CONCATENATE(AH11, AH12)))"

Now I am using a classical replace function in VBA:

Cells.Replace What:="XY", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

When recording this with the macro recorder it works and the formula comes up, but if running again with the macro it does not work.

Does anyone know why?

The whole reason behind this is that I've created a "Formula Creator" which should create a formula based on a dropdown and this is the output of the dropdown concatenation.

Thanks!

Upvotes: 1

Views: 3275

Answers (2)

user16465737
user16465737

Reputation: 1

I had the following issue: I recorded a find/replace on my pc, it gave a command with "FormulaVersion:=xlReplaceFormula2". I embedded that in my macro and it worked fine. I installed my macro on another computer, and the find/replace command caused the macro to abort every time on that computer, even though it worked fine on mine.
On the second computer, I recorded a find/replace and found that it recorded without the "FormulaVersion:=xlReplaceFormula2" piece. So, I removed it from my macro. Both computers are now working fine.

Upvotes: 0

ed2
ed2

Reputation: 1497

Looks okay at first glance, problem is not immediately obvious.

A few things to try:

  1. MatchByte - check out that parameter - on the unlikely possibility that the saved parameter is the cause.
  2. Try removing the last argument FormulaVersion:=xlReplaceFormula2 and see what happens
  3. Check that "cells" is referring to a range object in your use case (i.e. you or your code are navigated to the expected sheet containing your cell, when this line of your VBA code is reached).
  4. Confirm that the substring "XY" is indeed part of your cell value as displayed, not its format. You can do this in VBA by getting e.g. Range($A$1).value2 (where "$A$1" is the reference of your cell). Or you can do it in excel by, in another cell setting its formula to "=$A$1" and THEN formatting it to general.

Based on your question I would not expect any of these to be the problem but posting here in case it helps troubleshoot.

Upvotes: 1

Related Questions