Macro putting variables into sum formulas
Macro putting variables into sum formulas
(OP)
I have an equation
Range("N19").Value = "=IF(K19=""The End"",SUM(Cell(ActiveCell.Row-K18,ActiveCell.Column-2):(Cell(ActiveCell.Row-1,ActiveCell.Column),"""")"
I want column N to check column K for the string "The End". Inbetween each of those phrases I have excel taking a count of how many rows down the difference between the phrases is. This is stored one row up in K, we will use K18 to hold this value. Then, in the cell this If-Then yeilds a true value, I would like the sum of the two colums left of N(L & M), and K18 rows above this cell.
Can anyone help? I keep returning errors.
Range("N19").Value = "=IF(K19=""The End"",SUM(Cell(ActiveCell.Row-K18,ActiveCell.Column-2):(Cell(ActiveCell.Row-1,ActiveCell.Column),"""")"
I want column N to check column K for the string "The End". Inbetween each of those phrases I have excel taking a count of how many rows down the difference between the phrases is. This is stored one row up in K, we will use K18 to hold this value. Then, in the cell this If-Then yeilds a true value, I would like the sum of the two colums left of N(L & M), and K18 rows above this cell.
Can anyone help? I keep returning errors.





RE: Macro putting variables into sum formulas
RE: Macro putting variables into sum formulas
Thanks for the response!
I still recieve the same error. I don't the problem is with that but it is really with the If-ThenSum-Else formula instead.
RE: Macro putting variables into sum formulas
RE: Macro putting variables into sum formulas
CODE --> vb
Range("N19").Value = "=IF(K19=""The End"",SUM(Cell(ActiveCell.Row-K18,ActiveCell.Column-2):(Cell(ActiveCell.Row-1,ActiveCell.Column),"""")"The value in K18 always changes and I cannot get it to reference it correctly.
Sorry, this is difficult to explain. I am new to programming in VB.
RE: Macro putting variables into sum formulas
So you should use .Formula as indicated in my first reply (although .Value does work)
The formula you are putting in contains VBA syntax, which is why it doesn't work.
I can't understand what you are trying to accomplish, so I can't offer any further guidance.
RE: Macro putting variables into sum formulas
Now, the amount of rows inbetween an "E" occuring in Column B changes and that is why I have a count going on in Column B. For Sum(Cell1, Cell2); Cell1 needs to equal D6(notice that in B8 the count is 3).
I need VBA to input a formula =If(B9="E", SUM(ActiveCell.Offset(-B8,-2):ActiveCell.Offset(-1,-1)), "") in Cells (F2:F5000)
I am getting an error when I try just putting that formula directly above into a cell in Excel.
___A_B_C_D_E_F
5____E___2_3
6____1___4_5
7____2___6_7
8____3___8_9
9____E______Sum()
RE: Macro putting variables into sum formulas
Because VBA functions do not work in cell formulae.
RE: Macro putting variables into sum formulas
Can you think of a way to input the desired function through VB?
RE: Macro putting variables into sum formulas
Might be best to start a new thread for that.
RE: Macro putting variables into sum formulas
Another way to look at it would be I need to take a sum of the cells that are (-x rows, -2 columns) : (-1 rows, 0 columns) while the variable x is a number stored in another cell, which we could call B8. Can we do this through VBA?
RE: Macro putting variables into sum formulas
RE: Macro putting variables into sum formulas
I need VBA to insert a Sum formula into Excel. I would like this Sum formula to use the range [ (-x rows, -2 columns) : (-1 rows, 0 columns) ] in relation to the current cell. The value X is stored in B8.
Is this better
RE: Macro putting variables into sum formulas
I was looking for this formula:
=IF(K19="End Job",SUM(OFFSET(N19,-K18,-2,1,1):OFFSET(N19,-1,-1,1,1)),"")
And so in VBA it looks like
Range("K2:K62000").Formula = "=IF(K19=""The End"",SUM(OFFSET(N19,-K18,-2,1,1):OFFSET(N19,-1,-1,1,1)),"""")"
RE: Macro putting variables into sum formulas
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Macro putting variables into sum formulas
I have a computer with a 3.5Ghz i7 and 8gb of memory but the excel macro is taking up 7.85 gb of ram.
My computer has crashed countless times today, and needless to say, it has been a long week.
Thank you both for all your input.