Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code is working for row1; how to apply to row2?
First off: This forum has been VERY helpful for me and I have learned way more than I thought I ever would about Excel and VB. I have automated a few spreadsheets we work with at my current job with a little help from the search function here. Now onto my current mess. I spent most of yesterday evening helping someone automate a spreadsheet with MANY different IF:Then scenarios which are triggered by dates, dollar amounts, and the period of contracts. The spreadsheet allocates dollar amounts across our fiscal quarters beginning with the month following the provided date. The amount allocated is provided in another column and divided by the length of a contract which is shown in a separate column. I spent several hours (and I'm sure I used way more lines of code than necessary) and have working macros for the first row of the spreadsheet. There are 100 more rows below this one where I need the same macro to occur. There has to be an easier way than copying and pasting the current formulas and changing all the cell values mentioned to match the next row. Any suggestions??? Appreciate any help you can provide... -- moike ------------------------------------------------------------------------ moike's Profile: http://www.excelforum.com/member.php...o&userid=37668 View this thread: http://www.excelforum.com/showthread...hreadid=572754 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code is working for row1; how to apply to row2?
I am not clear is you are using VBA or just plain formulas.. If you are using formulas, just drag and fill, unless you have absolute reference to your cells in the first row, It should work ok. If you are using VBA, you would have to include the other rows into your codes. -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=572754 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code is working for row1; how to apply to row2?
Not being clairvoyant, I am somewhat strapped by the minimal information
provided. However, assuming your code is a User Defined Function used in worksheet and you want to generalize to work relative to its location, you can use Public Function CalcSpread(somearg) Dim rng as Range, rngDate as Range, rngDollar as Range Dim rate as Double, Answer as Double set rng = Application.Caller with rng.Parent set rngDate = .Range(.Cells(rng.row,3),.cells(rng.row,14)) set rngDollar = .Cells(rng.row,15) End With rate = rng.Dollar/ rng.Date.count ' blah blah CalcSpread = Answer End Function -- Regards, Tom Ogilvy "moike" wrote: First off: This forum has been VERY helpful for me and I have learned way more than I thought I ever would about Excel and VB. I have automated a few spreadsheets we work with at my current job with a little help from the search function here. Now onto my current mess. I spent most of yesterday evening helping someone automate a spreadsheet with MANY different IF:Then scenarios which are triggered by dates, dollar amounts, and the period of contracts. The spreadsheet allocates dollar amounts across our fiscal quarters beginning with the month following the provided date. The amount allocated is provided in another column and divided by the length of a contract which is shown in a separate column. I spent several hours (and I'm sure I used way more lines of code than necessary) and have working macros for the first row of the spreadsheet. There are 100 more rows below this one where I need the same macro to occur. There has to be an easier way than copying and pasting the current formulas and changing all the cell values mentioned to match the next row. Any suggestions??? Appreciate any help you can provide... -- moike ------------------------------------------------------------------------ moike's Profile: http://www.excelforum.com/member.php...o&userid=37668 View this thread: http://www.excelforum.com/showthread...hreadid=572754 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code is working for row1; how to apply to row2?
Bearacade Wrote: Without seeing your codes and exactly what you are doing, it hard to say what you need to do. Most likely a loop would help you run the macro thru all the neccessary rows. You might need to include examples to make it more clear Sure, Here is more info about what I am doing. Structure of the spreadsheet: Column C has a dollar amount, Column D has a date, column F has the term of the contract in months, columns G through Q are fiscal quarters. I need the macro to first verify if the amount in column C is or < than 100k. If less than 100k; the full value of column C will be entered in whichever fiscal quarter includes the date of 1 month beyond the date of column D. If the amount in C is greater than 100k; the value of column C must be divided by the number of months in the contract which is reflected in column F. This new amount must be entered in columns G through Q (fiscal quarters) but it can't start until 1 month after the date in column D. Anyway, I have working code to do this in the first row but my code contains the cell locations associated with the first row. I need the code to continue through all rows with the same steps using the values on that row. Are there other ways to signify which cells I want to include in my code other than calling out the location (ie: A1,A2)? I guess I could use: Range("A1").Select ActiveCell.FormulaR1C1 = "=RC[+1]" and continue with that format for the rest of the steps so that the only time I call out a specific location would be the "A1" at the beginning. That would require me to use the same code over and over in the macro but keep changing the A1 to A2,A3, etc. Is there an easier way (maybe a loop) to tell the macro where to start and then to move to the next row once finished with all the steps I outlined? -- moike ------------------------------------------------------------------------ moike's Profile: http://www.excelforum.com/member.php...o&userid=37668 View this thread: http://www.excelforum.com/showthread...hreadid=572754 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code is working for row1; how to apply to row2?
The simpliest way of doing it would be: For x = 1 To 100 Cells(x, 1).Select (Your Macro/Fuction here) Next x This would select A1, B1, C1 and so forth HT -- Bearacad ----------------------------------------------------------------------- Bearacade's Profile: http://www.excelforum.com/member.php...fo&userid=3501 View this thread: http://www.excelforum.com/showthread.php?threadid=57275 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code is working for row1; how to apply to row2?
thanks, i'll try it out -- moike ------------------------------------------------------------------------ moike's Profile: http://www.excelforum.com/member.php...o&userid=37668 View this thread: http://www.excelforum.com/showthread...hreadid=572754 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get row2 value into row1 in calculated Field Column in pivo | Excel Discussion (Misc queries) | |||
How do I make Row2 the header row? | Excel Discussion (Misc queries) | |||
VBA to Identify changes in row1 | Excel Programming | |||
VBA dynamically format Row1 | Excel Programming | |||
run code on opening workbook and apply code to certain sheets | Excel Programming |