Formulas with Counters
How do I place a formula in a cell while using a counter? Here's an example of a formula that I want to use: LEFT(OFFSET($A$27,(ROW($C1)-1)*14,0),LEN(OFFSET($A$27,(ROW($C1)-1)*14,0))-8 In addition, I want to place the same formula in the same column (as an example Column C). Therefore, Cell $C1 will change to $C2 and so on. I want to place the original formula in Cell C1 and then C2... I need to use a counter to tell the program when to stop. How do I do this? Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Formulas with Counters
counter = 15
Range("C1").Resize(counter,1).Formula = _ "=LEFT(OFFSET($A$27,(ROW($C1)-1)*14,0)," & _ "LEN(OFFSET($A$27,(ROW($C1)-1)*14,0))-8)" -- Regards, Tom Ogilvy Novice wrote in message ... How do I place a formula in a cell while using a counter? Here's an example of a formula that I want to use: LEFT(OFFSET($A$27,(ROW($C1)-1)*14,0),LEN(OFFSET($A$27,(ROW($C1)-1)*14,0))-8 In addition, I want to place the same formula in the same column (as an example Column C). Therefore, Cell $C1 will change to $C2 and so on. I want to place the original formula in Cell C1 and then C2... I need to use a counter to tell the program when to stop. How do I do this? Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Formulas with Counters
Aaaargh! I don't know how often I've created formulas like this, using
something like Part1 = "= LEFT(OFFSET($A$27,(ROW($C" Part2 = ")-1)*14,0)," & LEN(OFFSET($A$27,(ROW($C" Part3 = ")-1)*14,0))-8)" myFormula = Part1 & counter & Part2 & counter & Part3 and using a loop. <whimper That's another technique saved for later :) -- Darren "Tom Ogilvy" wrote in message ... counter = 15 Range("C1").Resize(counter,1).Formula = _ "=LEFT(OFFSET($A$27,(ROW($C1)-1)*14,0)," & _ "LEN(OFFSET($A$27,(ROW($C1)-1)*14,0))-8)" -- Regards, Tom Ogilvy Novice wrote in message ... How do I place a formula in a cell while using a counter? Here's an example of a formula that I want to use: LEFT(OFFSET($A$27,(ROW($C1)-1)*14,0),LEN(OFFSET($A$27,(ROW($C1)-1)*14,0))-8 In addition, I want to place the same formula in the same column (as an example Column C). Therefore, Cell $C1 will change to $C2 and so on. I want to place the original formula in Cell C1 and then C2... I need to use a counter to tell the program when to stop. How do I do this? Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Formulas with Counters
It is usually best to capitalize on Excel's built in capabilities to the
maximum extent possible. -- Regards, Tom Ogilvy Darren Hill wrote in message ... Aaaargh! I don't know how often I've created formulas like this, using something like Part1 = "= LEFT(OFFSET($A$27,(ROW($C" Part2 = ")-1)*14,0)," & LEN(OFFSET($A$27,(ROW($C" Part3 = ")-1)*14,0))-8)" myFormula = Part1 & counter & Part2 & counter & Part3 and using a loop. <whimper That's another technique saved for later :) -- Darren "Tom Ogilvy" wrote in message ... counter = 15 Range("C1").Resize(counter,1).Formula = _ "=LEFT(OFFSET($A$27,(ROW($C1)-1)*14,0)," & _ "LEN(OFFSET($A$27,(ROW($C1)-1)*14,0))-8)" -- Regards, Tom Ogilvy Novice wrote in message ... How do I place a formula in a cell while using a counter? Here's an example of a formula that I want to use: LEFT(OFFSET($A$27,(ROW($C1)-1)*14,0),LEN(OFFSET($A$27,(ROW($C1)-1)*14,0))-8 In addition, I want to place the same formula in the same column (as an example Column C). Therefore, Cell $C1 will change to $C2 and so on. I want to place the original formula in Cell C1 and then C2... I need to use a counter to tell the program when to stop. How do I do this? Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Formulas with Counters
Yes, you just have to know what they are first - that's one of the reasons
I'm here :) -- Darren "Tom Ogilvy" wrote in message ... It is usually best to capitalize on Excel's built in capabilities to the maximum extent possible. -- Regards, Tom Ogilvy Darren Hill wrote in message ... Aaaargh! I don't know how often I've created formulas like this, using something like Part1 = "= LEFT(OFFSET($A$27,(ROW($C" Part2 = ")-1)*14,0)," & LEN(OFFSET($A$27,(ROW($C" Part3 = ")-1)*14,0))-8)" myFormula = Part1 & counter & Part2 & counter & Part3 and using a loop. <whimper That's another technique saved for later :) -- Darren "Tom Ogilvy" wrote in message ... counter = 15 Range("C1").Resize(counter,1).Formula = _ "=LEFT(OFFSET($A$27,(ROW($C1)-1)*14,0)," & _ "LEN(OFFSET($A$27,(ROW($C1)-1)*14,0))-8)" -- Regards, Tom Ogilvy Novice wrote in message ... How do I place a formula in a cell while using a counter? Here's an example of a formula that I want to use: LEFT(OFFSET($A$27,(ROW($C1)-1)*14,0),LEN(OFFSET($A$27,(ROW($C1)-1)*14,0))-8 In addition, I want to place the same formula in the same column (as an example Column C). Therefore, Cell $C1 will change to $C2 and so on. I want to place the original formula in Cell C1 and then C2... I need to use a counter to tell the program when to stop. How do I do this? Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
All times are GMT +1. The time now is 03:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com