VBA Find the incorrect row on a different sheet

[Excelquestion35]

Hi all,

I'm working on a macro that updates a database, based on the input of a form. This form is located on a different sheet. My code was working fine all the time until I noticed that it didn't perform any changes when the macro was run from the sheet with the form. That's why I had to slightly change below code (I changed inarr from ActiveSheet to the sheet on which the edit should be made (FLMs)). To my knowledge the code should normally find the line with the conditions of having the same manager & site as condition and copy that for the new adaptation.

However, now the code will instead of going to the FLMs sheet to find the right rule, it will go to the first row in the sheet of where the form is located (FLM-change2). Something goes probably wrong at the place where the If statements starts but I can not find the correct solution. Would anyone be able to help out on this issue so that the code looks for the right line in the FLMs sheet based on the conditions instead of inserting the data in the FLM-change2 sheet form?

Sub GeheleMacro()
Dim lastrow As Long
Dim ws As String
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
lastrow2 = Sheets("FLMs").Cells(Rows.Count, "B").End(xlUp).Row

Sheets("FLMs").Range("B4:B" & lastrow2).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("NIET VERWIJDEREN").Range("A1"), _
UNIQUE:=True
    
    
inarr = Sheets("FLMs").Range("$B$1:$AS$66") ' load all the data into a variant array NOTE starting from row1
manager = Sheets("FLM-change2").Range("G18")
site = Sheets("FLM-change2").Range("G17")
newflm = Sheets("FLM-change2").Range("G13")
wlmuserid = Sheets("FLM-change2").Range("G10")
kronosid = Sheets("FLM-change2").Range("G11")
mail = Sheets("FLM-change2").Range("G12")
verandering = Sheets("FLM-change2").Range("G19")

For i = 3 To 66   ' start loop at row 3
   If inarr(i, 2) = manager And inarr(i, 1) = site Then ' we have found the row
    Range(Cells(i, 2), Cells(i, 45)).Select ' do whatever you need to do on this row
    Selection.Copy
    Selection.Insert Shift:=xlDown
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = newflm
    
 
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.Value = "=Today()"
    ActiveCell.Offset(0, 0).Range("A1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = manager
    
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = kronosid
    
    ActiveCell.Offset(0, 3).Range("A1").Select
    ActiveCell.FormulaR1C1 = mail
    
    ActiveCell.Offset(0, 4).Range("A1").Select
    ActiveCell.FormulaR1C1 = wlmuserid
    
    ActiveCell.Offset(0, 2).Range("A1").Select
    ActiveCell.FormulaR1C1 = mail
    
        If verandering = "Verwijderen" Then ActiveCell.Offset(1, -11).Range("A1").Select
        If verandering = "Verwijderen" Then ActiveCell.FormulaR1C1 = "No"
        
               
    Exit For
        
   End If
Next i
End sub

I am aware that the code is not that fancy by switching constantly to searching for a different cell but this was the solution that I could think of. Yet, it does the job.

Your help is much appreciated!

 

[johnnyL]

Before the 'For i = 3 to 66' part, Select the sheet name that you will be working on by adding the following line of code:

Worksheets(“FLMs”). Select


I assume that is the sheet name that you want to be working with, if not change it to the correct sheet name.

 

[Excelquestion35]

Sometimes the solution can be that simple, it works thank you!

 

[johnnyL]

You are welcome @Excelquestion35.