0

I am trying to change the range in a formula on sheet "Lists" using a launcher when clicking on one of the cells column A on sheet "Input", I just need to change the row of the cell in the formula. (It does not work either with the cells or range function).

When I try using the formula by itself it works perfectly, but when I use the launcher the program runs, no error message, the msgbox sends me what the formula is supposed to be, but the formula is not updated in the cell. So I guess the launcher is the problem but I cannot figure out what is the real issue, and how to make the code to work.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        'storing value of the clicked cell to go back on it after programmed
        Application.ScreenUpdating = False

        Dim myR As Variant
        myR = ActiveCell.Row
        Sheets("Lists").Select
        Cells(3, 21).Formula2R1C1 = _
          "=FILTER(R3C12:R1048576C12,ISNUMBER(SEARCH(Input!R" & myR & "C2,R3C12:R1048576C12)),""not found"")"
        MsgBox ("=FILTER(R3C12:R1048576C12,ISNUMBER(SEARCH(Input!R" & myR & "C2,R3C12:R1048576C12)),""not found"")")
        Range("S3").Formula2R1C1 = _
          "=FILTER(R3C9:R1048576C9,ISNUMBER(SEARCH(Input!R" & myR & "C1,R3C9:R1048576C9)),""not found"")"
        Call Location
        Call bins
        Sheets("Input").Select
        Application.ScreenUpdating = True
    End If
End sub
GSerg
  • 76,472
  • 17
  • 159
  • 346
chloe
  • 1
  • Have a look at this https://stackoverflow.com/q/50776026/4961700 at the code starting "Do copy and paste from master to target". – Solar Mike Jul 04 '22 at 15:00
  • 2
    What is a launcher? – GSerg Jul 04 '22 at 15:02
  • Gserg, What I meant is that the program starts when I click or exit the colum/cell – chloe Jul 05 '22 at 09:33
  • so when you say "when i use the formula" do you mean if you just put the formula in a cell? thing i would recommend is making this as simple as possible and then building up (as far as I can see the formula should be put in place). try simplifying down to just putting `=22` in the one target cell of sheet lists and remove those calls to `location` and `bins`, see if it works then move to the full formula, then replace those calls. this will really show us where you're having a problem – InjuredCoding Jul 05 '22 at 14:01
  • Hi @InjuredCoding, when I say using formula I mean using the code from the variant initiating to inputing the formula in the correct cell. it does work perfectly if I press a button, but while changing the cell it does not work. I tried taking out **location** and **bins** but it still did not work. so I guess it is this part: `Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Not Intersect(Target, Range("A:A")) Is Nothing Then` – chloe Jul 05 '22 at 14:10
  • Your code seems to put the formula in the 'Input' sheet. Remove `Sheets("Lists").Select` and `Sheets("Input").Select`. Start your formula with `Sheets("Lists").Cells(3, 21).Formula2R1C1....` - no need to select anything. – Darren Bartrup-Cook Jul 06 '22 at 13:26

0 Answers0