Long Array VBA macros

dellzy

Board Regular
Joined
Apr 24, 2013
Messages
146
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?


Code:
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":-




Code:
=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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.
 
Upvote 0
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?

Code:
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 :-

Code:
{=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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top