Unable to set the FormulaArray property of the Range class

[juliusrhyan]

Hi Everyone,
I have a problem which I hope someone would be able to assist me wtih.
I tried an approach I found online wherein you have to divide the formula into two parts However, I'm still receiving Run-Time error '1004:
There was a thread here that resolved someone's code however, I am unable to post on that thread to post my similar problem.

Please find my code below.

Sub FindTranslation()

Dim B_lr As Integer, B_dex, B_rng, B_cel As Range, Page As String
    Set B_dex = Me.Range("B8")
    B_lr = Me.Cells(Rows.Count, B_dex.Column).End(xlUp).Row
    Set B_rng = Range(B_dex.Offset(B_lr), B_dex)

Dim Formula1, Formula2 As String
    Formula1 = "=IF(RC[-2]="""","""",RC[-2]&"" "")&IF(TEXTJOIN(""~   "",TRUE,IF(TRIM(RC[-1])=Translations!C[-2],Translations!C[-1],""""))=""""," & "Form2"
    Formula2 = "IFERROR(VLOOKUP(TRIM(""*""&RC[-1]&""*""),Translations!C[-2]:C[-1],2,FALSE),""Not Found""),TEXTJOIN(""~   "",TRUE,IF(TRIM(RC[-1])=Translations!C[-2],Translations!C[-1],"""")))"

    For Each B_cel In B_rng
        Page = InStr(B_cel.Value, "Page")
        If B_cel <> "" And B_cel.Offset(, 1).Value = "" And Page = 0 Or B_cel <> "" And B_cel.Offset(, -1).Value <> "" Then
            With B_cel.Offset(, 1)
                .FormulaArray = Formula1
                .Replace "Form2", Formula2, xlPart
            End With
        End If
    Next B_cel

End Sub

 

[RoryA]

Formula1 is not a valid formula - it's missing a closing bracket for the first IF function.

 

[juliusrhyan]

Hi Rory,
I appreciate your swift response.

The formula was generated by recording a macro, then I copied the recorded formula onto the sub.
Still, I did try to add another bracket on the first IF to check if that's the problem but it still gives me the same error.

Formula1 = "=IF((RC[-2]="""","""",RC[-2]&"" "")&IF(TEXTJOIN(""~&"",TRUE,IF(TRIM(RC[-1])=Translations!C[-2],Translations!C[-1],""""))=""""," & "Form2"

 

[RoryA]

The bracket needs to be after the Form2

 

[juliusrhyan]

Hi Rory. Thank you again for your swift response and thank you very much for the clarification.
The error is not showing up anymore but it gives this array formula on the target cell. I think the Form2 was not replaced.
=IF(A9="","",A9&" ")&IF(TEXTJOIN("~&",TRUE,IF(TRIM(B9)=Translations!A:A,Translations!B:B,""))="",Form2())

I added the bracket and the formula now looks like the one below.

Dim B_lr As Integer, B_dex, B_rng, B_cel As Range, Page As String
    Set B_dex = Me.Range("B8")
    B_lr = Me.Cells(Rows.Count, B_dex.Column).End(xlUp).Row
    Set B_rng = Range(B_dex.Offset(B_lr), B_dex)

Dim Formula1, Formula2 As String
    Formula1 = "=IF(RC[-2]="""","""",RC[-2]&"" "")&IF(TEXTJOIN(""~&"",TRUE,IF(TRIM(RC[-1])=Translations!C[-2],Translations!C[-1],""""))=""""," & "Form2()"
    Formula2 = "IFERROR(VLOOKUP(TRIM(""*""&RC[-1]&""*""),Translations!C[-2]:C[-1],2,FALSE),""Not Found""),TEXTJOIN(""~&"",TRUE,IF(TRIM(RC[-1])=Translations!C[-2],Translations!C[-1],"""")))"
    For Each B_cel In B_rng
        Page = InStr(B_cel.Value, "Page")
        If B_cel <> "" And B_cel.Offset(, 1).Value = "" And Page = 0 Or B_cel <> "" And B_cel.Offset(, -1).Value <> "" Then
            With B_cel.Offset(, 1)
                .FormulaArray = Formula1
                .Replace "Form2()", Formula2, xlPart
            End With
        End If
    Next B_cel

 

[RoryA]

That still shouldn't work as you have added another opening bracket. It should be:

Formula1 = "=IF(RC[-2]="""","""",RC[-2]&"" "")&IF(TEXTJOIN(""~&"",TRUE,IF(TRIM(RC[-1])=Translations!C[-2],Translations!C[-1],""""))=""""," & "Form2)"

Also, unless you work in Excel with R1C1 settings, your Formula2 formula needs to be in A1 format not R1C1, or you should switch in the code, make the change, then switch back to A1.

 

[RoryA]

One approach:

Sub FindTranslation()

   Dim B_lr As Integer, B_dex, B_rng, B_cel As Range, Page As String
    Set B_dex = Range("B8")
    B_lr = Cells(Rows.Count, B_dex.Column).End(xlUp).Row
    Set B_rng = Range(B_dex.Offset(B_lr), B_dex)
   Dim formulaFormat As XlReferenceStyle
   formulaFormat = Application.ReferenceStyle
   Application.ReferenceStyle = xlR1C1
   Dim Formula1, Formula2 As String
    Formula1 = "=IF(RC[-2]="""","""",RC[-2]&"" "")&IF(TEXTJOIN(""~&"",TRUE,IF(TRIM(RC[-1])=Translations!C[-2],Translations!C[-1],""""))=""""," & "Form2)"
    Formula2 = "IFERROR(VLOOKUP(TRIM(""*""&RC[-1]&""*""),Translations!C[-2]:C[-1],2,FALSE),""Not Found""),TEXTJOIN(""~&"",TRUE,IF(TRIM(RC[-1])=Translations!C[-2],Translations!C[-1],"""")))"
    For Each B_cel In B_rng
        Page = InStr(B_cel.Value, "Page")
        If B_cel <> "" And B_cel.Offset(, 1).Value = "" And Page = 0 Or B_cel <> "" And B_cel.Offset(, -1).Value <> "" Then
            With B_cel.Offset(, 1)
                .FormulaArray = Formula1
                .Replace "Form2)", Formula2, xlPart
            End With
        End If
    Next B_cel
    Application.ReferenceStyle = formulaFormat
End Sub

 

[juliusrhyan]

Rory, you are my hero!!

I am so amazed that you fixed my code then provided a solution in just minutes..

It is now giving me the exact results that I was looking for.

Thank you very much

 

[RoryA]

Glad we could help!