Autofill range in formula

[BradleyS]

Hi

I have a couple of formulas as shown below in my macro code:

-------------------------------
Range("Q3").Select
Selection.FormulaArray = _
"=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,INDEX(general!R2C6:R507C6,MAT CH(1,IF(general!R2C1:R507C1=RC1,IF(ISNUMBER(SE ARCH(R1C[-1],general!R2C13:R507C13)),1))))))"

Range("R3").Select
Selection.FormulaArray = _
"=LOOKUP(REPT(""z"",255),CHOOSE({1,2},"""",INDEX(general!R2C14:R507C14,MATC H(1,IF(general!R2C1:R507C1=RC1,IF(ISNUMBER(SEARCH(R1C[-1],general!R2C13:R507C13)),1))))))"

-------------------------------

...however the range of rows in the general worksheet will keep changing and I wondered how I can alter this formula so that it includes the correct amount of rows regardless of row length, so that when I run my macro it includes everything?

[Bob Phillips]

[VBA]Const LOOKUP_FORMULA As String = _
"=LOOKUP(<lookup>,CHOOSE({1,2},<null>,INDEX(general!R2C<col>:R<lastrow>C<co l>," & _
"MATCH(1,IF(general!R2C1:R<lastrow>C1=RC1,IF(ISNUMBER(SEARCH(R1C[-1],general!R2C13:R<lastrow>C13)),1))))))"
Dim lastrow As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("Q3").FormulaArray = Replace(Replace(Replace(Replace(LOOKUP_FORMULA, _
"<null>", 0), _
"<lastrow>", lastrow), _
"<col>", 6), _
"<lookup>", "9.99999999999999E+307")

.Range("R3").FormulaArray = Replace(Replace(Replace(Replace(LOOKUP_FORMULA, _
"<null>", """"""), _
"<lastrow>", lastrow), _
"<col>", 14), _
"<lookup>", "REPT(""z"",255)")
End With
[/VBA]

[BradleyS]

Thank you ever so much for the reply.

However, this seems to be taking the row count from the current sheet and not the general! sheet

For example the current raw formula is:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,INDEX(general!$F$2:$F$507,MATC H(1,IF(general!$A$2:$A$507=$A3,IF(ISNUMBER(SEARCH(P$1,general!$M$2:$M$507)) ,1))))))

while your code writes:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,INDEX(general!$F$2:$F$252,MATCH(1,IF(general!$A$2:$A$252=$A3,IF(ISNUMBER(SEARCH(P$1,general!$M$2:$M$252)),1))))))

I really appreciate you help.

[Bob Phillips]

You are absolutely right, it is.

Correction.

[VBA]Const LOOKUP_FORMULA As String = _
"=LOOKUP(<lookup>,CHOOSE({1,2},<null>,INDEX(general!R2C<col>:R<lastrow>C<co l>," & _
"MATCH(1,IF(general!R2C1:R<lastrow>C1=RC1,IF(ISNUMBER(SEARCH(R1C[-1],general!R2C13:R<lastrow>C13)),1))))))"
Dim lastrow As Long

With Worksheets("general")

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

With ActiveSheet
.Range("Q3").FormulaArray = Replace(Replace(Replace(Replace(LOOKUP_FORMULA, _
"<null>", 0), _
"<lastrow>", lastrow), _
"<col>", 6), _
"<lookup>", "9.99999999999999E+307")

.Range("R3").FormulaArray = Replace(Replace(Replace(Replace(LOOKUP_FORMULA, _
"<null>", """"""), _
"<lastrow>", lastrow), _
"<col>", 14), _
"<lookup>", "REPT(""z"",255)")
End With
[/VBA]

[BradleyS]

Sorry for the delay in replying, I was away from home for a few days.

Your code is fantastic and works a treat!
Thank you ever so much, a true genius!