×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Macro putting variables into sum formulas

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.

RE: Macro putting variables into sum formulas

Range("N19").Formula

RE: Macro putting variables into sum formulas

(OP)
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.

RE: Macro putting variables into sum formulas

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?

RE: Macro putting variables into sum formulas

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

CODE --> vb

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.

RE: Macro putting variables into sum formulas

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.

RE: Macro putting variables into sum formulas

(OP)
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()


RE: Macro putting variables into sum formulas

Quote:

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.

Because VBA functions do not work in cell formulae.

RE: Macro putting variables into sum formulas

(OP)
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?

RE: Macro putting variables into sum formulas

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.

RE: Macro putting variables into sum formulas

(OP)
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?

RE: Macro putting variables into sum formulas

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

RE: Macro putting variables into sum formulas

(OP)
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

RE: Macro putting variables into sum formulas

(OP)
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)),"""")"

RE: Macro putting variables into sum formulas

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/

RE: Macro putting variables into sum formulas

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources