Error message when executing macro

[sofas]

Hi I am trying to convert formulas to vba code but I am facing an error in the next row I do not know how I can correct it

Sub Macro3()


Dim lr As Long
Dim sh As Worksheet: Set sh = Sheets("Source")
Dim dest As Worksheet: Set dest = Sheets("Dest")

lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
 
 ws1 = sh.Name: WS2 = dest.Name

Set a = sh.Range("A2:A" & lr): Set b = sh.Range("B2:B" & lr)
Set c = sh.Range("C2:C" & lr): Set d = sh.Range("D2:D" & lr)

With dest.Range("b2:b" & Range("a" & Rows.Count).End(3).Row)
.Formula = "=IFERROR(INDEX('" & ws1 & "'!" & c.Address & "," & "MATCH(""=""&$E$1,'" & ws1 & "'!" & a.Address & ",""=""&$a$2,'" & ws1 & "'!" & b.Address

'.Formula = =IFERROR(INDEX(Source!$C$2:C$100; MATCH(1;($E$1=Source!A$2:A$100)*(A2=Source!B$2:B$100); 0));"")
   
With dest.Range("c2:c" & Range("a" & Rows.Count).End(3).Row)
.Formula = "=SUMIFS('" & ws1 & "'!" & d.Address & ",'" & ws1 & "'!" & a.Address & ",""=""&$E$1,'" & ws1 & "'!" & c.Address & ",""=""&$B2,'" & ws1 & "'!" & b.Address & ",A2)"
' Formula = "=SUMIFS('Source'!$D$2:D$100,'Source'!$A$2:A$100,$E$1,'Source'!$C$2:C$100,b2,'Source'!$B$2:B$100,a2)"
   
'         .Value = .Value
'         .Value = .Value


    End With
    End With
End Sub

 

[rlv01]

I am facing an error

Excel has many errors. It is better to always include information about the error number and error message.

The formula produced by this line of code

.Formula = "=IFERROR(INDEX('" & ws1 & "'!" & c.Address & "," & "MATCH(""=""&$E$1,'" & ws1 & "'!" & a.Address & ",""=""&$a$2,'" & ws1 & "'!" & b.Address

Does not match the reference formula you have included here:

'.Formula = =IFERROR(INDEX(Source!$C$2:C$100; MATCH(1;($E$1=Source!A$2:A$100)*(A2=Source!B$2:B$100); 0));"")

So I'm guessing that the error had something do do with that. Instead of assigning the formula directly to the range .Formula property as you are doing here:

.Formula = "=IFERROR(INDEX('" & ws1 & "'!" & c.Address & "," & "MATCH(""=""&$E$1,'" & ws1 & "'!" & a.Address & ",""=""&$a$2,'" & ws1 & "'!" & b.Address

Use a variable (FormulaStr) to create the formula first:

Dim FormulaStr As String
With dest.Range("b2:b" & Range("a" & Rows.Count).End(3).Row)
'.Formula = "=IFERROR(INDEX('" & ws1 & "'!" & c.Address & "," & "MATCH(""=""&$E$1,'" & ws1 & "'!" & a.Address & ",""=""&$a$2,'" & ws1 & "'!" & b.Address
FormulaStr = "=IFERROR(INDEX('" & ws1 & "'!" & c.Address & "," & "MATCH(""=""&$E$1,'" & ws1 & "'!" & a.Address & ",""=""&$a$2,'" & ws1 & "'!" & b.Address

Debug.Print FormulaStr  '<--- to inspect the formula in the VBE Immediate window.
End                     '<---- temporary, to be removed after you get the formula fixed

.Formula = FormulaStr

That way you can inspect the formula, compare it to your reference formula and make adjustments as needed until you get it right.

 

[sofas]

Sub Macro3()

Dim lr As Long, lige As Long
Dim sh As Worksheet: Set sh = Sheets("Source")
Dim dest As Worksheet: Set dest = Sheets("Dest")

lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
     lige = dest.Cells(Rows.Count, 1).End(xlUp).Row

 ws1 = sh.Name: WS2 = dest.Name

Set a = sh.Range("A2:A" & lr): Set b = sh.Range("B2:B" & lr)
Set c = sh.Range("C2:C" & lr): Set d = sh.Range("D2:D" & lr)

Application.ScreenUpdating = False
    Dim i As Long
    For i = 2 To lige
  
        dest.Cells(i, "B") = Evaluate( _
            "=IFERROR(INDEX('Source'!$C:$C,MATCH(" _
            & dest.Cells(i, "A").Address _
            & ",'Source'!$B:$B,0),1),"""")")
                
[B]'Formula = "=IFERROR(INDEX('Source'!$C$2:C$100, MATCH(1,($E$1='Source'!A$2:A$100)*(a2='Source'!B$2:B$100), 0)),"""")"[/B]
Next
With dest.Range("c2:c" & Range("a" & Rows.Count).End(3).Row)
.Formula = "=SUMIFS('" & ws1 & "'!" & d.Address & ",'" & ws1 & "'!" & a.Address & ",""=""&$E$1,'" & ws1 & "'!" & c.Address & ",""=""&$B2,'" & ws1 & "'!" & b.Address & ",A2)"

         .Value = .Value


    End With
  
End Sub

I changed the code except the following figure. It works well, but I need to add another condition, which is to fetch the data provided that cell e1, as in the formula below, matches column A.

Formula = "=IFERROR(INDEX('Source'!$C$2:C$100, MATCH(1,($E$1='Source'!A$2:A$100)*(a2='Source'!B$2:B$100), 0)),"""")"

 

[rlv01]

That looks like the same formula you had before.