Writing VBA formula

[zinah]

Hi,

Anyone can help me with writing below formula with VBA code?

=IF(IF(TEXT(F24, "0") = "", "", IFERROR(INDEX(SF_Goals_Forms!K1:K20000, MATCH(TEXT(F24, "0"), SF_Goals_Forms!CR1:CR20000, 0)), ""))=0,"",IF(TEXT(F24, "0") = "", "", IFERROR(INDEX(SF_Goals_Forms!K1:K20000, MATCH(TEXT(F24, "0"), SF_Goals_Forms!CR1:CR20000, 0)), "")))

Thank you!

 

[RoryA]

FYI, the simple way to do this kind of thing is to enter the formula in the cell manually, then with the cell active, open the VB Editor and in the immediate window type:

?Replace$(Activecell.FormulaR1C1, chr(34), chr(34) & chr(34))

and press Enter. That will output the formula as you need it in VBA.

 

[zinah]

FYI, the simple way to do this kind of thing is to enter the formula in the cell manually, then with the cell active, open the VB Editor and in the immediate window type:

?Replace$(Activecell.FormulaR1C1, chr(34), chr(34) & chr(34))

and press Enter. That will output the formula as you need it in VBA.

THANK YOU so much, that really helped, the formula works perfectly great, blank if 0

 With cel.Offset(0, 2)  'checks if progress notes exist - need to add condition if result is 0
    Dim IFERROR_FORMULA As String
    IFERROR_FORMULA = "IFERROR(INDEX('" & sfSht.Name & "'!R" & firstR & "C" & [K1].Column & ":R" & _
                        lastR & "C" & [K1].Column & ", MATCH(" & KeyTxt & ",'" & _
                        sfSht.Name & "'!" & sfSht.Range(sfKeyRng).Address(ReferenceStyle:=xlR1C1) & ", 0)),"""")"
    .Formula = "=IF(IF(TEXT(RC[-2], ""0"") = """", """", IFERROR(INDEX(SF_Goals_Forms!R[-23]C[3]:R[19976]C[3], MATCH(TEXT(RC[-2], ""0""), SF_Goals_Forms!R[-23]C[88]:R[19976]C[88], 0)), """"))=0,"""",IF(TEXT(RC[-2], ""0"") = """", """", IFERROR(INDEX(SF_Goals_Forms!R[-23]C[3]:R[19976]C[3], MATCH(TEXT(RC[-2], ""0""), SF_Goals_Forms!R[-23]C[88]:R[19976]C[88], 0)), """")))"

 

[zinah]

What is the KeyTxt variable in your code?

As I said, the TEXT function applied there will only return "" if the source cell has a formula in it returning "". Do you need the formula to process actually empty cells? If F24 is actually empty, then TEXT(F24,"0") will return "0".

KeyTxt stands for below:

For Each cel In Sheets("ref.").[Key_EEID]
        KeyTxt = "TEXT(" & cel.Address(False, False) & ", " & Chr(34) & "0" & Chr(34) & ")"