Long Array VBA macros

[dellzy]

Hello Excel Experts,
I run this and came out error "Type mismatch" and the yellow highlights at the row ".Replace "AAA", FORMP5, lookat:=xlPart". Can anyone correct me, please?

Sub SLAresult1()
Dim FORMP1, FORMP2, FORMP3, FORMP4, FORMP5 As String


FORMP1 = "=IFERROR(IF(ISNUMBER(SEARCH(""business"",RC[-4]))=TRUE,XXX,YYY),""n/a"")"
FORMP2 = "IF(ISNUMBER(SEARCH(""day"",RC[-4]))=TRUE,IF(RC[-13]<=LEFT(RC[-4],(FIND("" "",RC[-4],1)-1))*24,""Within SLA"",""Smelly Fish"")"
FORMP3 = "IF(ISNUMBER(SEARCH(""hour"",RC[-4]))=TRUE,IF(RC[-13]<=LEFT(RC[-4],(FIND("" "",RC[-4],1)-1)),""Within SLA"",""Smelly Fish""),ZZZ))))"
FORMP4 = "IF(ISNUMBER(SEARCH(""min"",RC[-4]))=TRUE,IF(RC[-13]<=LEFT(RC[-4],(FIND("" "",RC[-4],1)-1))/60,""Within SLA"",""Smelly Fish"")))),AAA"
FORMP5 = "IF(ISNUMBER(SEARCH(""day"",RC[-4]))=TRUE,IF(RC[-14]<=LEFT(RC[-4],(FIND("" "",RC[-4],1)-1))*24,""Within SLA"",""Smelly Fish"")," & _
"IF(ISNUMBER(SEARCH(""hour"",RC[-4]))=TRUE,IF(RC[-14]<=LEFT(RC[-4],(FIND("" "",RC[-4],1)-1)),""Within SLA"",""Smelly Fish"")," & _
"IF(ISNUMBER(SEARCH(""min"",RC[-4]))=TRUE,IF(RC[-14]<=LEFT(RC[-4],(FIND("" "",RC[-4],1)-1))/60,""Within SLA"",""Smelly Fish"")))))"
   
Application.ReferenceStyle = xlR1C1
With ActiveSheet.Range("AR2")
.FormulaArray = FORMP1
.Replace "XXX", FORMP2, lookat:=xlPart
.Replace "YYY", FORMP3, lookat:=xlPart
.Replace "ZZZ", FORMP4, lookat:=xlPart
.Replace "AAA", FORMP5, lookat:=xlPart
End With
Application.ReferenceStyle = xlA1
End Sub

A1 notation style should be like this in cell "AR2":-

=IFERROR(IF(ISNUMBER(SEARCH("business",AN2))=TRUE,IF(ISNUMBER(SEARCH("day",AN2))=TRUE,IF(AE2<=LEFT(AN2,(FIND(" ",AN2,1)-1))*24,"Within SLA","Smelly Fish"),IF(ISNUMBER(SEARCH("hour",AN2))=TRUE,IF(AE2<=LEFT(AN2,(FIND(" ",AN2,1)-1)),"Within SLA","Smelly Fish"),IF(ISNUMBER(SEARCH("min",AN2))=TRUE,IF(AE2<=LEFT(AN2,(FIND(" ",AN2,1)-1))/60,"Within SLA","Smelly Fish")))),IF(ISNUMBER(SEARCH("day",AN2))=TRUE,IF(AD2<=LEFT(AN2,(FIND(" ",AN2,1)-1))*24,"Within SLA","Smelly Fish"),IF(ISNUMBER(SEARCH("hour",AN2))=TRUE,IF(AD2<=LEFT(AN2,(FIND(" ",AN2,1)-1)),"Within SLA","Smelly Fish"),IF(ISNUMBER(SEARCH("min",AN2))=TRUE,IF(AD2<=LEFT(AN2,(FIND(" ",AN2,1)-1))/60,"Within SLA","Smelly Fish"))))),"n/a")

Thanks a lot in advance.
DZ

 

[Rick Rothstein]

Hello Excel Experts,
I run this and came out error "Type mismatch" and the yellow highlights at the row ".Replace "AAA", FORMP5, lookat:=xlPart". Can anyone correct me, please?

Looking at the difference between what is assigned to FORMP1 and FORMP2, the first has a leading equal sign and the second does not. Since the first worked, I would suggest putting an equal sign before the IF function name in all the assignments to FORMP# variables. The reason you may be having trouble with this is because this line of code...

Dim FORMP1, FORMP2, FORMP3, FORMP4, FORMP5 As String

only declares FORMP5 as a String variable, all the rest of the FORMP# variables get declared as Variants (in VB, every variable must be declared individually as to their data type, otherwise they default to Variants). The fact that those variables are Variants (which can contain anything) may make them sensitive to the missing equal sign.

 

[dellzy]

Looking at the difference between what is assigned to FORMP1 and FORMP2, the first has a leading equal sign and the second does not. Since the first worked, I would suggest putting an equal sign before the IF function name in all the assignments to FORMP# variables. The reason you may be having trouble with this is because this line of code...

Dim FORMP1, FORMP2, FORMP3, FORMP4, FORMP5 As String

only declares FORMP5 as a String variable, all the rest of the FORMP# variables get declared as Variants (in VB, every variable must be declared individually as to their data type, otherwise they default to Variants). The fact that those variables are Variants (which can contain anything) may make them sensitive to the missing equal sign.

Hello there, Sir,
Thank you for your response. I modified a little based on your notes, now no more error came out but unfortunately the XXX and YYY are still not populated in the cell AR2 in the worksheet. Anything wrong with codes?

Sub SLAresult1()
Dim FORMP1 As String
Dim FORMP2 As String
Dim FORMP3 As String
Dim FORMP4 As String
Dim FORMP5 As String
Dim FORMP6 As String


FORMP1 = "=IFERROR(IF(ISNUMBER(SEARCH(""business"",RC[-4]))=TRUE,XXX,YYY),""n/a"")"
FORMP2 = "IF(ISNUMBER(SEARCH(""day"",RC[-4]))=TRUE,IF(RC[-13]<=LEFT(RC[-4],(FIND("" "",RC[-4],1)-1))*24,""Within SLA"",""Smelly Fish"")"
FORMP3 = "IF(ISNUMBER(SEARCH(""hour"",RC[-4]))=TRUE,IF(RC[-13]<=LEFT(RC[-4],(FIND("" "",RC[-4],1)-1)),""Within SLA"",""Smelly Fish""),ZZZ))))"
FORMP4 = "IF(ISNUMBER(SEARCH(""min"",RC[-4]))=TRUE,IF(RC[-13]<=LEFT(RC[-4],(FIND("" "",RC[-4],1)-1))/60,""Within SLA"",""Smelly Fish""),AAA)"
FORMP5 = "IF(ISNUMBER(SEARCH(""day"",RC[-4]))=TRUE,IF(RC[-14]<=LEFT(RC[-4],(FIND("" "",RC[-4],1)-1))*24,""Within SLA"",""Smelly Fish""),BBB)"
FORMP6 = "IF(ISNUMBER(SEARCH(""min"",RC[-4]))=TRUE,IF(RC[-14]<=LEFT(RC[-4],(FIND("" "",RC[-4],1)-1))/60,""Within SLA"",""Smelly Fish"")))))"
    
Application.ReferenceStyle = xlR1C1
With ActiveSheet.Range("AR2")
.FormulaArray = FORMP1
.Replace "XXX", FORMP2, lookat:=xlPart
.Replace "YYY)", FORMP3, lookat:=xlPart
.Replace "ZZZ", FORMP4, lookat:=xlPart
.Replace "AAA", FORMP5, lookat:=xlPart
.Replace "BBB", FORMP6, lookat:=xlPart
End With
Application.ReferenceStyle = xlA1
End Sub

The result in cell AR2 become :-

{=IFERROR(IF(ISNUMBER(SEARCH("business",AN2))=TRUE,XXX,YYY),"n/a")}

Whoever can help to correct my codes above, I thank you so much in advance.
DZ