VBA String error

[mrsushi]

HI

Cant seem to work out the below problem. D5 - G5 is returning "FALSE" and filling down with FALSE When in fact it should be retrieving data from the sheet "LT03 Full data" tab

Any ideas of why this is happening please?



Sub NewRecon()
Dim strFormulas(4 To 7) As Variant
lngLastRow = Range("A" & Rows.Count).End(xlUp).Row

strFormulas(4) = Worksheets("LT03 Rec").Range("D5").Formula = "=IF(COUNTIF(R5C1:R50000C1,RC1)>1,""DUPLICATE"","""")"
strFormulas(5) = Worksheets("LT03 Rec").Range("E5").Formula = "=VLOOKUP(CONCATENATE(RC[-4],"" "",RC[-3]),'LT03 Full Data'!C[-4]:C[-2], 2, FALSE)"
strFormulas(6) = Worksheets("LT03 Rec").Range("F5").Formula = "=VLOOKUP(CONCATENATE(RC[-5],RC[-4]),'LT03 Full Data'!C[-5]:C[-3],2, FALSE)"
strFormulas(7) = Worksheets("LT03 Rec").Range("G5").Formula = "=VLOOKUP(CONCATENATE(RC[-6], "" "",RC[-5]),'LT03 Full Data'!C[-6]:C[-4],2, FALSE)"

Range("D5:G5").Formula = strFormulas
Range("D5:G" & lngLastRow).FillDown

End Sub

 

[Fluff]

It should be like

strFormulas(4) = "=IF(COUNTIF(R5C1:R50000C1,RC1)>1,""DUPLICATE"","""")"

 

[mrsushi]

Many thanks for the tweak. That's sorted it out.

Do you know if this is the most efficient Script? The whole process took about a minute to complete. Im trying to find if there is another script which is faster

Many thanks

 

[Rick Rothstein]

Try putting this immediately after your Dim statement...

Application.ScreenUpdating = False

and this...

Application.ScreenUpdating = True

immediately before the End Sub statement and see if that speeds it up for you.