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!

 

[offthelip]

I think I have taken all the conditions into account, I wasn't sure where you wanted the output.

Sub ttest()
tt = Range("F24")
output = ""
F24V = WorksheetFunction.Text(tt, "0")
With Worksheets("SF_Goals_Forms")
  mcol = .Range("CR1:CR20000")
  Icol = .Range("K1:K20000")
End With
For i = 1 To 20000
 If F24V = mcol(i, 1) Then
  output = Icol(i, 1)
  Exit For
 End If
Next i
If output = 0 Then
output = ""
End If
MsgBox output

 

[zinah]

I think I have taken all the conditions into account, I wasn't sure where you wanted the output.

Sub ttest()
tt = Range("F24")
output = ""
F24V = WorksheetFunction.Text(tt, "0")
With Worksheets("SF_Goals_Forms")
  mcol = .Range("CR1:CR20000")
  Icol = .Range("K1:K20000")
End With
For i = 1 To 20000
If F24V = mcol(i, 1) Then
  output = Icol(i, 1)
  Exit For
End If
Next i
If output = 0 Then
output = ""
End If
MsgBox output

I think I have taken all the conditions into account, I wasn't sure where you wanted the output.

Sub ttest()
tt = Range("F24")
output = ""
F24V = WorksheetFunction.Text(tt, "0")
With Worksheets("SF_Goals_Forms")
  mcol = .Range("CR1:CR20000")
  Icol = .Range("K1:K20000")
End With
For i = 1 To 20000
If F24V = mcol(i, 1) Then
  output = Icol(i, 1)
  Exit For
End If
Next i
If output = 0 Then
output = ""
End If
MsgBox output

Hi, thank you for your help. This is the formula that I have : =IF(TEXT(F24, "0") = "", "", IFERROR(INDEX(SF_Goals_Forms!K1:K20000, MATCH(TEXT(F24, "0"), SF_Goals_Forms!CR1:CR20000, 0)), ""))

And below is the VBA code of this formula:

With cel.Offset(0, 2)  'checks if progress notes exist - need to add condition if result is 0
        .Formula = "=IF(TEXT(" & .Offset(0, -2).Address(False, False) & ", " & Chr(34) & "0" & Chr(34) & ")" & " = " & Chr(34) & Chr(34) & ", " & Chr(34) & Chr(34) & _
                    ", IFERROR(INDEX(" & sfSht.Name & "!" & Cells(firstR, [K1].Column).Address(False, False) & ":" & _
                        Cells(lastR, [K1].Column).Address(False, False) & ", MATCH(" & KeyTxt & ", " & _
                        sfSht.Name & "!" & sfKeyRng & ", 0)), " & Chr(34) & Chr(34) & ")) "

        .Copy
        .PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End With

However, I need to add another condition, which is "if the result = 0, then make empty, otherwise, the exact result", I know the formula that gives me the result, but I'm not sure how to add this condition to the current VBA codes, does that make sense?

 

[offthelip]

I wasted my time answering your question because you didn't ask a clear question and you didn't post your existing code. If you had obviously I would have understood what you wanted.
What you want added to your equation is NOT a trivial change. So I do not have the time to do it.
What I do when I need to "develop" a bit of code to write an equation with vba, is to remove the "=" sign in the first character. This means the output is written as text. then select the cell and add the "=" manually. EXCEL will then highlight any errors which it find with the equation. Best of luck

 

[RoryA]

Probably something like this:

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)),"""")"
        
    .FormulaR1C1 = "=IF(RC[-2]="""","""",IF(" & IFERROR_FORMULA & "=0,""""," & IFERROR_FORMULA & "))"

 

[zinah]

I wasted my time answering your question because you didn't ask a clear question and you didn't post your existing code. If you had obviously I would have understood what you wanted.
What you want added to your equation is NOT a trivial change. So I do not have the time to do it.
What I do when I need to "develop" a bit of code to write an equation with vba, is to remove the "=" sign in the first character. This means the output is written as text. then select the cell and add the "=" manually. EXCEL will then highlight any errors which it find with the equation. Best of luck

I'm sorry I wasted your time and was not clear enough! Thank you for your time and feedback, I really appreciate that!

 

[zinah]

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)),"""")" .FormulaR1C1 = "=IF(RC[-2]="""","""",IF(" & IFERROR_FORMULA & "=0,""""," & IFERROR_FORMULA & "))"

Hi, thank you for your help, I tried your code and this result was blank, the formula that your code represent is:

=IF(F24="","",IF(IFERROR(INDEX(SF_Goals_Forms!$K$1:$K$20000, MATCH(TEXT('F24', "0"),SF_Goals_Forms!$CR$1:$CR$20000, 0)),"")=0,"",IFERROR(INDEX(SF_Goals_Forms!$K$1:$K$20000, MATCH(TEXT('F24', "0"),SF_Goals_Forms!$CR$1:$CR$20000, 0)),"")))

But when I tweaked your formula, I got my result which is blank if 0, I think you missed adding text formula to your vba code, is it possible for you to help me building below formula as vba formula?

=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)), "")))

 

[RoryA]

I don’t understand the point of the TEXT function. It would only return “” if the cell were blank.

 

[zinah]

I don’t understand the point of the TEXT function. It would only return “” if the cell were blank.

The text function is needed as some data are values so I need this. I tried your formula the result was blank but when I used my formula I mentioned above it worked, I’m not that advanced in VBA codes but I’m good with excel formulas. Will you be able to help with rewriting my formula as VBA codes?

 

[RoryA]

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".