PDA

View Full Version : Autofill range in formula



BradleyS
05-07-2012, 08:35 AM
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,MATC H(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,MATCH(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
05-07-2012, 09:55 AM
Const LOOKUP_FORMULA As String = _
"=LOOKUP(<lookup>,CHOOSE({1,2},<null>,INDEX(general!R2C<col>:R<lastrow>C<col>," & _
"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

BradleyS
05-07-2012, 11:54 AM
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,MATC H(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
05-07-2012, 02:32 PM
You are absolutely right, it is.

Correction.

Const LOOKUP_FORMULA As String = _
"=LOOKUP(<lookup>,CHOOSE({1,2},<null>,INDEX(general!R2C<col>:R<lastrow>C<col>," & _
"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

BradleyS
05-09-2012, 03:10 PM
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!:beerchug: