0

I am trying to use a dynamic drop down table which adjusts the formulas in a sheets. (for example if the drop down value is abc, then part of the formulas will be adjusted via find and replaced) If I do it manually, it works out perfectly, but the code just selects my range and doesn't do anything else. I recorded my manual work with the Macro recorder and get the following result:

Sub a()
Range("B50:I60","B30:I40","B70:I90").Select
Selection.Replace What:=")", Replacement:=";Sheet1!X:X;"";9;"")", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=Format, FOrmulaBersion:=xlReplaceFormula2
End Sub

I want it to look like this:

    Sub replace()
    If Range("A2").Value="Juli 2020" _
    Then Range("B50:I60","B30:I40","B70:I90").Select
    Selection.Replace What:=")", Replacement:=";Sheet1!X:X;"";9;"")", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=Format, FOrmulaBersion:=xlReplaceFormula2
    End If
End Sub

Both of these codes don't work when I run them from VBA, event though if I do it manually I can easily adjust my formulas. PS:I thought refering to the worksheet directly and activate it would help but it doesn't change anything. Can anyone help? It's very frustrating… Hugs, Annabelle

Annabelle
  • 13
  • 3
  • 2
    `Select` and `Activate` are usually unnecessary and frequently cause problems. Fully qualify your references in your code. Use a break point to see which worksheet and workbook your ranges are referring to. Also read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Ron Rosenfeld Jun 05 '20 at 11:06
  • There are several issues with this code, both those pointed out above and the fact that the last two parameters (`ReplaceFormat:=Format, FOrmulaBersion:=xlReplaceFormula2`) are invalid. It is customary to provide code examples that actually compile, so please see this: https://stackoverflow.com/help/how-to-ask Anyway, you probably want to take a somewhat different approach to the problem as shown in this answer: https://stackoverflow.com/questions/49623964/excel-vba-replace-a-text-inside-cells-formula – Miqi180 Jun 05 '20 at 11:31
  • @Ron Rosenfeld: I simple was wrong **not being sure**... I will delete my comment. – FaneDuru Jun 05 '20 at 12:22
  • `Range("B50:I60,B30:I40,B70:I90")` – Naresh Jun 05 '20 at 13:00
  • Hello, Thank you for your comments. My main problem is that the recorded code is not working when I try to run it. That never happened to me before.. Right now I tried a very simple code which should work, but doesn't. Am I seeing something the wrong way? Thanks again for your help. `Sub Makro1() Range("A5:D21").Replace What:=")", Replacement:=";Table1!M:M;""c"")", LookAt:=xlPart End Sub` – Annabelle Jun 05 '20 at 13:23
  • It works just fine if I replace a word by another one (text format), but not when I am looking to replace values in formulas.. – Annabelle Jun 05 '20 at 13:46
  • I found the answer: I need to define the find and replace objects as strings: `Sub Replace_Example() Dim Replacetext As String Dim Findtext As String If Range("A1").Value = "a" Then Findtext = ")" Replacetext = ",Tabelle1!M:M,""a"")" Range("A5:D20").replace what:=Findtext, replacement:=Replacetext, lookat:=xlPart, MatchCase:=False End if End Sub` – Annabelle Jun 05 '20 at 14:13

0 Answers0