Macro putting variables into sum formulas 1

[DHuskic]

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.

 

[MintJulep]

Range("N19").[red]Formula[/red]

 

[DHuskic]

Hi MintJulep
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.

 

[MintJulep]

You have a mix of syntax for worksheet operations and VBA in there. Are you trying to do this in VBA or on the worksheet?

 

[DHuskic]

I am putting this code into vba(my fault I should have put in in code format here on the thread).

Range("N19").Value = "=IF(K19=""The End"",SUM(Cell(ActiveCell.Row-K18,ActiveCell.Column-2):(Cell(ActiveCell.Row-1,ActiveCell.Column),"""")"

I am trying to take a Sum(Cell(K18 rows above current cell, 2 columns left):Cell(1 row above current cell))
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.

 

[MintJulep]

It appears that you want to use VBA to place a formula in a cell.

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.

 

[DHuskic]

How about this. If I was trying to put a formula in F9 = If(B9="E", SUM(D6:E8), "")
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()

 

[MintJulep]

I need VBA to input a formula =If(B9="E", SUM([red]ActiveCell.Offset(-B8,-2)[/red]:[red]ActiveCell.Offset(-1,-1)[/red]), "") in Cells (F2:F5000)
I am getting an error when I try just putting that formula directly above into a cell in Excel.

Because VBA functions do not work in cell formulae.

 

[DHuskic]

That explains it, I was starting to confuse the two types of formulas.
Can you think of a way to input the desired function through VB?

 

[MintJulep]

If you could clearly describe what you want to ACCOMPLISH rather than how you think it should be done then I'm sure someone can offer guidance.

Might be best to start a new thread for that.

 

[DHuskic]

Could you let me know if you think this is a better explination?
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?

 

[MintJulep]

No, I don't think that's a better explanation.

 

[DHuskic]

MintJulep, I really trying to explain this correctly, sorry for all the difficulties I am having.

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

 

[DHuskic]

I solved it after a few hours of research.
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)),"""")"

 

[IDS]

Well done on getting it working, but if you are just starting with VBA I'd encourage you to post a more general description of exactly what the formula is trying to accomplish. At the moment you have a macro that works with two specific cells, and fills a large range with a formula. With a bit of work you can probably get something much more useful and robust, and learn something on the way.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[DHuskic]

IDS, as a matter of fact I put in a LastRow integer, which finds the last row of the original data, and then replaces 62000 with that row so I do not fill up the entire spreadsheet with unneccssary information.

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.