| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilling a formula down
Hi All.......
I have three columns, column A is about 3500 rows, (exact quantity unknown), columns B and C have the same quantity of rows each but are a few hundred less than column A, again (exact quantity unkown) I would like to find the lower points of cols B and C and insert a separate fomula into each column and autofill both formulas down as far as there is data in column A, then Copy PasteSpecial Values to get rid of the formulas.....thus giving me 3 columns of data of all the same length. Any help would be appreciated. Vaya con Dios, Chuck, CABGx3 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilling a formula down
No need to autofill and paste special, just assign values to the cells:
Sub test() Dim LRowA As Long, LRowB As Long Dim rng As Range, c As Range LRowA = Cells(Rows.Count, "A").End(xlUp).Row LRowB = Cells(Rows.Count, "B").End(xlUp).Row Set rng = Range("B" & LRowB + 1 & ":B" & LRowA) For Each c In rng 'puts a value in column B 'that is twice the value of column A c.Value = c.Offset(, -1) * 2 ' puts a value in column C ' that is 1 greater than column B c.Offset(, 1).Value = c.Value + 1 Next End Sub Mike F "CLR" wrote in message ... Hi All....... I have three columns, column A is about 3500 rows, (exact quantity unknown), columns B and C have the same quantity of rows each but are a few hundred less than column A, again (exact quantity unkown) I would like to find the lower points of cols B and C and insert a separate fomula into each column and autofill both formulas down as far as there is data in column A, then Copy PasteSpecial Values to get rid of the formulas.....thus giving me 3 columns of data of all the same length. Any help would be appreciated. Vaya con Dios, Chuck, CABGx3 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilling a formula down
Hey Mike.........thanks much for the comeback.
Your code works super fine with a simple formula like you show, but when I try to modify it to use my compound real formula I get no joy......... Here's my formula, which worked ok when macroed into cell R2 and copied down......... ..Value = "=IF(ISERR(LOOKUP((SUMPRODUCT(($A$12:$A$10000=$A2) *($q$12:$q$10000=DATA!$K$ 4)*J2:j10000))/(SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10000= DATA!$K$4)) ),{0,61,71,81,91,101},{""F"",""D"",""C"",""B"",""A "",""A+""})),""NoData"",LO OKUP((SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10 000=DATA!$K$4)*$J$12:$j$1 0000))/(SUMPRODUcT(($A$12:A$10000=$A2)*($q$12:$q$10000=D ATA!$K$4))),{0,61,7 1,81,91,101},{""F"",""D"",""C"",""B"",""A"",""A+"" }))" it fails in that context, to the error "NoData"...... the$A2 reference is wrong for that row, ........ so I tried to modify it by replacing the $A2 with c.offset(, -17) because I am putting the formula actually in column R......and still want to refer to the value in column A of that row........this failed also to the error "NoData".........and in both cases, the macro actually put the formula in the cells rather than the values of their calculations. Obviously this is over my head.....please help Vaya con Dios, Chuck, CABGx3 "Mike Fogleman" wrote in message . .. No need to autofill and paste special, just assign values to the cells: Sub test() Dim LRowA As Long, LRowB As Long Dim rng As Range, c As Range LRowA = Cells(Rows.Count, "A").End(xlUp).Row LRowB = Cells(Rows.Count, "B").End(xlUp).Row Set rng = Range("B" & LRowB + 1 & ":B" & LRowA) For Each c In rng 'puts a value in column B 'that is twice the value of column A c.Value = c.Offset(, -1) * 2 ' puts a value in column C ' that is 1 greater than column B c.Offset(, 1).Value = c.Value + 1 Next End Sub Mike F "CLR" wrote in message ... Hi All....... I have three columns, column A is about 3500 rows, (exact quantity unknown), columns B and C have the same quantity of rows each but are a few hundred less than column A, again (exact quantity unkown) I would like to find the lower points of cols B and C and insert a separate fomula into each column and autofill both formulas down as far as there is data in column A, then Copy PasteSpecial Values to get rid of the formulas.....thus giving me 3 columns of data of all the same length. Any help would be appreciated. Vaya con Dios, Chuck, CABGx3 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilling a formula down
OK, I guess I'll give you what you asked for:
This will filldown column R Sub test() Dim LRowA As Long, LRowR As Long Dim rng As Range, c As Range LRowA = Cells(Rows.Count, "A").End(xlUp).Row LRowR = Cells(Rows.Count, "R").End(xlUp).Row Set rng = Range("R" & LRowR + 1 & ":R" & LRowA) Range("R" & LRowR + 1).Formula = "=IF(ISERR(LOOKUP((SUMPRODUCT(($A$12:$A$10000=$A2) *($q$12:$q$10000=DATA!$K$4)*J2:j10000))/(SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10000= DATA!$K$4))),{0,61,71,81,91,101},{""F"",""D"",""C" ",""B"",""A"",""A+""})),""NoData"",LOOKUP((SUMPROD UCT(($A$12:$A$10000=$A2)*($q$12:$q$10000=DATA!$K$ 4)*$J$12:$j$10000))/(SUMPRODUcT(($A$12:A$10000=$A2)*($q$12:$q$10000=D ATA!$K$4))),{0,61,71,81,91,101},{""F"",""D"",""C"" ,""B"",""A"",""A+""}))" rng.FillDown rng.Value = rng.Value End Sub Mike F "CLR" wrote in message ... Hey Mike.........thanks much for the comeback. Your code works super fine with a simple formula like you show, but when I try to modify it to use my compound real formula I get no joy......... Here's my formula, which worked ok when macroed into cell R2 and copied down......... .Value = "=IF(ISERR(LOOKUP((SUMPRODUCT(($A$12:$A$10000=$A2) *($q$12:$q$10000=DATA!$K$ 4)*J2:j10000))/(SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10000= DATA!$K$4)) ),{0,61,71,81,91,101},{""F"",""D"",""C"",""B"",""A "",""A+""})),""NoData"",LO OKUP((SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10 000=DATA!$K$4)*$J$12:$j$1 0000))/(SUMPRODUcT(($A$12:A$10000=$A2)*($q$12:$q$10000=D ATA!$K$4))),{0,61,7 1,81,91,101},{""F"",""D"",""C"",""B"",""A"",""A+"" }))" it fails in that context, to the error "NoData"...... the$A2 reference is wrong for that row, ........ so I tried to modify it by replacing the $A2 with c.offset(, -17) because I am putting the formula actually in column R......and still want to refer to the value in column A of that row........this failed also to the error "NoData".........and in both cases, the macro actually put the formula in the cells rather than the values of their calculations. Obviously this is over my head.....please help Vaya con Dios, Chuck, CABGx3 "Mike Fogleman" wrote in message . .. No need to autofill and paste special, just assign values to the cells: Sub test() Dim LRowA As Long, LRowB As Long Dim rng As Range, c As Range LRowA = Cells(Rows.Count, "A").End(xlUp).Row LRowB = Cells(Rows.Count, "B").End(xlUp).Row Set rng = Range("B" & LRowB + 1 & ":B" & LRowA) For Each c In rng 'puts a value in column B 'that is twice the value of column A c.Value = c.Offset(, -1) * 2 ' puts a value in column C ' that is 1 greater than column B c.Offset(, 1).Value = c.Value + 1 Next End Sub Mike F "CLR" wrote in message ... Hi All....... I have three columns, column A is about 3500 rows, (exact quantity unknown), columns B and C have the same quantity of rows each but are a few hundred less than column A, again (exact quantity unkown) I would like to find the lower points of cols B and C and insert a separate fomula into each column and autofill both formulas down as far as there is data in column A, then Copy PasteSpecial Values to get rid of the formulas.....thus giving me 3 columns of data of all the same length. Any help would be appreciated. Vaya con Dios, Chuck, CABGx3 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilling a formula down
HI Mike.......
Well, many thanks for your suggestions.......combining both of yours, and tweaking a little for my application, the thing works beautifully.......You've taught me much neat stuff......... Here's the working version: Sub InsertRanks() Dim LRowA As Long, LRowR As Long Dim rng As Range, c As Range LRowA = Cells(Rows.Count, "A").End(xlUp).Row LRowR = Cells(Rows.Count, "R").End(xlUp).Row Set rng = Range("R" & LRowR + 1 & ":R" & LRowA) For Each c In rng Range("R1").Copy c.Select ActiveSheet.Paste c.Value = c.Value Range("s1").Copy c.Offset(, 1).Select ActiveSheet.Paste c.Offset(, 1).Value = c.Offset(, 1).Value Next End Sub Of course R1 and S1 contain the big formulas......... Again, thank you most kindly for helping me through this....... Vaya conDios, Chuck, CABGx3 "Mike Fogleman" wrote in message . .. OK, I guess I'll give you what you asked for: This will filldown column R Sub test() Dim LRowA As Long, LRowR As Long Dim rng As Range, c As Range LRowA = Cells(Rows.Count, "A").End(xlUp).Row LRowR = Cells(Rows.Count, "R").End(xlUp).Row Set rng = Range("R" & LRowR + 1 & ":R" & LRowA) Range("R" & LRowR + 1).Formula = "=IF(ISERR(LOOKUP((SUMPRODUCT(($A$12:$A$10000=$A2) *($q$12:$q$10000=DATA!$K$ 4)*J2:j10000))/(SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10000= DATA!$K$4)) ),{0,61,71,81,91,101},{""F"",""D"",""C"",""B"",""A "",""A+""})),""NoData"",LO OKUP((SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10 000=DATA!$K$4)*$J$12:$j$1 0000))/(SUMPRODUcT(($A$12:A$10000=$A2)*($q$12:$q$10000=D ATA!$K$4))),{0,61,7 1,81,91,101},{""F"",""D"",""C"",""B"",""A"",""A+"" }))" rng.FillDown rng.Value = rng.Value End Sub Mike F "CLR" wrote in message ... Hey Mike.........thanks much for the comeback. Your code works super fine with a simple formula like you show, but when I try to modify it to use my compound real formula I get no joy......... Here's my formula, which worked ok when macroed into cell R2 and copied down......... .Value = "=IF(ISERR(LOOKUP((SUMPRODUCT(($A$12:$A$10000=$A2) *($q$12:$q$10000=DATA!$K$ 4)*J2:j10000))/(SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10000= DATA!$K$4)) ),{0,61,71,81,91,101},{""F"",""D"",""C"",""B"",""A "",""A+""})),""NoData"", LO OKUP((SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10 000=DATA!$K$4)*$J$12:$j$1 0000))/(SUMPRODUcT(($A$12:A$10000=$A2)*($q$12:$q$10000=D ATA!$K$4))),{0,61,7 1,81,91,101},{""F"",""D"",""C"",""B"",""A"",""A+"" }))" it fails in that context, to the error "NoData"...... the$A2 reference is wrong for that row, ........ so I tried to modify it by replacing the $A2 with c.offset(, -17) because I am putting the formula actually in column R......and still want to refer to the value in column A of that row........this failed also to the error "NoData".........and in both cases, the macro actually put the formula in the cells rather than the values of their calculations. Obviously this is over my head.....please help Vaya con Dios, Chuck, CABGx3 "Mike Fogleman" wrote in message . .. No need to autofill and paste special, just assign values to the cells: Sub test() Dim LRowA As Long, LRowB As Long Dim rng As Range, c As Range LRowA = Cells(Rows.Count, "A").End(xlUp).Row LRowB = Cells(Rows.Count, "B").End(xlUp).Row Set rng = Range("B" & LRowB + 1 & ":B" & LRowA) For Each c In rng 'puts a value in column B 'that is twice the value of column A c.Value = c.Offset(, -1) * 2 ' puts a value in column C ' that is 1 greater than column B c.Offset(, 1).Value = c.Value + 1 Next End Sub Mike F "CLR" wrote in message ... Hi All....... I have three columns, column A is about 3500 rows, (exact quantity unknown), columns B and C have the same quantity of rows each but are a few hundred less than column A, again (exact quantity unkown) I would like to find the lower points of cols B and C and insert a separate fomula into each column and autofill both formulas down as far as there is data in column A, then Copy PasteSpecial Values to get rid of the formulas.....thus giving me 3 columns of data of all the same length. Any help would be appreciated. Vaya con Dios, Chuck, CABGx3 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofilling a formula down
Since you seem willing to learn, could I make a couple suggestions to your
code? Lose the copy/paste. It just wastes time going to and from the clipboard. Reserve it primarily when you need to move cell formatting from one range to another. And even then it would be much quicker to pre-format the destination cells from the worksheet if possible. Also loose the For...Next loop. Knowing how to loop is good, knowing when to loop is better. Looping is good to step through a range of different values and do something with them. In your case, the range has no values yet, you want to create some. In this case it can be easier and quicker to deal with the entire range in one shot. It appears you are keeping your formulas in R1 & S1 for reference. That is fine. It is much easier to create/modify formulas in the cell, rather than VBA. I assume that the rest of the cells in columns R & S are just values, no formulas. But you want to complete the columns of data by using the formulas stored in row 1. Without copy/paste, that is done by assigning a range of formulas to another range. Range("R100:S100").Formula = Range("R1:S1").Formula This assigns the formulas on row 1 to row 100 of their respective columns. From here we fill-down the entire range, in this case 2 columns, all at once, and convert the formulas to values. Rng.Value = Rng.Value Again, no copy/paste special The code would look like this: Sub test() Dim LRowA As Long, LRowR As Long Dim rng As Range LRowA = Cells(Rows.Count, "A").End(xlUp).Row LRowR = Cells(Rows.Count, "R").End(xlUp).Row 'set the range to create values Set rng = Range("R" & LRowR + 1 & ":S" & LRowA) 'Assign row 1 formulas to last row + 1 Range("R" & LRowR + 1 & ":S" & LRowR + 1).Formula = Range("R1:S1").Formula 'Fill the entire range with formulas rng.FillDown 'convert formulas to values rng.Value = rng.Value End Sub Mike F "CLR" wrote in message ... HI Mike....... Well, many thanks for your suggestions.......combining both of yours, and tweaking a little for my application, the thing works beautifully.......You've taught me much neat stuff......... Here's the working version: Sub InsertRanks() Dim LRowA As Long, LRowR As Long Dim rng As Range, c As Range LRowA = Cells(Rows.Count, "A").End(xlUp).Row LRowR = Cells(Rows.Count, "R").End(xlUp).Row Set rng = Range("R" & LRowR + 1 & ":R" & LRowA) For Each c In rng Range("R1").Copy c.Select ActiveSheet.Paste c.Value = c.Value Range("s1").Copy c.Offset(, 1).Select ActiveSheet.Paste c.Offset(, 1).Value = c.Offset(, 1).Value Next End Sub Of course R1 and S1 contain the big formulas......... Again, thank you most kindly for helping me through this....... Vaya conDios, Chuck, CABGx3 "Mike Fogleman" wrote in message . .. OK, I guess I'll give you what you asked for: This will filldown column R Sub test() Dim LRowA As Long, LRowR As Long Dim rng As Range, c As Range LRowA = Cells(Rows.Count, "A").End(xlUp).Row LRowR = Cells(Rows.Count, "R").End(xlUp).Row Set rng = Range("R" & LRowR + 1 & ":R" & LRowA) Range("R" & LRowR + 1).Formula = "=IF(ISERR(LOOKUP((SUMPRODUCT(($A$12:$A$10000=$A2) *($q$12:$q$10000=DATA!$K$ 4)*J2:j10000))/(SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10000= DATA!$K$4)) ),{0,61,71,81,91,101},{""F"",""D"",""C"",""B"",""A "",""A+""})),""NoData"",LO OKUP((SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10 000=DATA!$K$4)*$J$12:$j$1 0000))/(SUMPRODUcT(($A$12:A$10000=$A2)*($q$12:$q$10000=D ATA!$K$4))),{0,61,7 1,81,91,101},{""F"",""D"",""C"",""B"",""A"",""A+"" }))" rng.FillDown rng.Value = rng.Value End Sub Mike F "CLR" wrote in message ... Hey Mike.........thanks much for the comeback. Your code works super fine with a simple formula like you show, but when I try to modify it to use my compound real formula I get no joy......... Here's my formula, which worked ok when macroed into cell R2 and copied down......... .Value = "=IF(ISERR(LOOKUP((SUMPRODUCT(($A$12:$A$10000=$A2) *($q$12:$q$10000=DATA!$K$ 4)*J2:j10000))/(SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10000= DATA!$K$4)) ),{0,61,71,81,91,101},{""F"",""D"",""C"",""B"",""A "",""A+""})),""NoData"", LO OKUP((SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10 000=DATA!$K$4)*$J$12:$j$1 0000))/(SUMPRODUcT(($A$12:A$10000=$A2)*($q$12:$q$10000=D ATA!$K$4))),{0,61,7 1,81,91,101},{""F"",""D"",""C"",""B"",""A"",""A+"" }))" it fails in that context, to the error "NoData"...... the$A2 reference is wrong for that row, ........ so I tried to modify it by replacing the $A2 with c.offset(, -17) because I am putting the formula actually in column R......and still want to refer to the value in column A of that row........this failed also to the error "NoData".........and in both cases, the macro actually put the formula in the cells rather than the values of their calculations. Obviously this is over my head.....please help Vaya con Dios, Chuck, CABGx3 "Mike Fogleman" wrote in message . .. No need to autofill and paste special, just assign values to the cells: Sub test() Dim LRowA As Long, LRowB As Long Dim rng As Range, c As Range LRowA = Cells(Rows.Count, "A").End(xlUp).Row LRowB = Cells(Rows.Count, "B").End(xlUp).Row Set rng = Range("B" & LRowB + 1 & ":B" & LRowA) For Each c In rng 'puts a value in column B 'that is twice the value of column A c.Value = c.Offset(, -1) * 2 ' puts a value in column C ' that is 1 greater than column B c.Offset(, 1).Value = c.Value + 1 Next End Sub Mike F "CLR" wrote in message ... Hi All....... I have three columns, column A is about 3500 rows, (exact quantity unknown), columns B and C have the same quantity of rows each but are a few hundred less than column A, again (exact quantity unkown) I would like to find the lower points of cols B and C and insert a separate fomula into each column and autofill both formulas down as far as there is data in column A, then Copy PasteSpecial Values to get rid of the formulas.....thus giving me 3 columns of data of all the same length. Any help would be appreciated. Vaya con Dios, Chuck, CABGx3 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Autofilling Sheet Name in Formula | Excel Discussion (Misc queries) | |||
| Autofilling using INDIRECT in a formula | Excel Discussion (Misc queries) | |||
| Autofilling incremental numbers in a formula | Excel Worksheet Functions | |||
| Autofilling a formula: not working? | Excel Discussion (Misc queries) | |||
| Autofilling a formula to a variable last cell | Excel Programming | |||
Linear Mode
