Populating rows with formulas when new data is entered

If you could provide an example worksheet explaining and showing what you are trying to do, that would help a lot.

Understood.

In the attached file.
B1-B4 can change at anytime. As you can see A11-A13 is for 5/10 and had different input values in B1-B4 and you can see that the numbers in J11-M13 are numbers and NOT formulas like J14-M14 which is using the numbers in B1-B4 to compute.

What will happen however if tomorrow B1-B4 WILL change and i dont want J14-M-14 to reflect the new numbers.

So what I believe I need are 2 macros:
1) a macro that at the of the day (or better yet when i change ANY number ins B1-B4) to save J11-M(whatever is the last line of data) as values
2) a macro that knows when i enter data into A15 and then creates the formulas that currently reside in J14-M14.

Again thank you for your help with this. Its above my know how to do.

or better yet when i change ANY number ins B1-B4

I don't think you want to do that, because the Worksheet_Change event doesn't remember the previous value once you entered a new value.So you'd have to have the code create temp values in some blank cells somewhere upon entering a cell, then use those temp values when _Change is triggered. I guess it's doable, but I think having a button you could press to change the formulas to values would be a lot better. Let me know if that approach is ok.

a macro that knows when i enter data into A15 and then creates the formulas that currently reside in J14-M14.

This is easy to do and I'll implement it as a Worksheet_Change event, so as soon as you enter the date, the formulas will appear.

@simarui: I like your code, but how I understand the problem is that sungen99 wants to preserve J:M with the values of the formulas before changes were made to B1:B4.

That's why I'm suggesting a button. Add a button, assign the sungen99 macro to it. Then before making any changes to B1-B4, press the button, and the last line of formulas will be preserved as values.

The second sub adds the formulas to J:M any time you change a cell in column A, row greater than 10.

Please Login or Register  to view this content.

Thank you all for your assistance with this. Very helpful. Walruseggman’s example is the closest to what I am looking for but upon some testing I found an error but also a new request.

If noticed that if I add more than 2 lines of new data before running the macro that it does not save the value of the 1st one and leaves it as the formulas.

That being said. What would be helpful is instead of a button. Would there be a way that upon closing of the program that it could run the macro then exit??? I would not need a button then at all making it easier for the user.

Moderators:
Check the log as i followed protonLeah request yesterday but when i went to post it said your message needs to be more than 10 words and is too short. Please tell me what to do and i would be happy to comply. I wanted to rename it "populating rows with formulas when new data is entered"

title changed.

I actually did it too Zbor... Ha.... apparently i also needed to copy the entire message as well. Thanks for your help.

Ok now that has been taken care of back to my post.

Thank you all for your assistance with this. Very helpful. Walruseggman’s example is the closest to what I am looking for but upon some testing I found an error but also a new request.

If noticed that if I add more than 2 lines of new data before running the macro that it does not save the value of the 1st one and leaves it as the formulas.

That being said. What would be helpful is instead of a button. Would there be a way that upon closing of the program that it could run the macro then exit??? I would not need a button then at all making it easier for the user.

Here you go.

This sub belongs in the ThisWorkbook object (you may need to change "Sheet1" to match whatever the sheet name is with the dates/formulas on it):

Please Login or Register  to view this content.

And this code belongs on the sheet object with the formulas:

Please Login or Register  to view this content.

WOW walruseggman this is exactly what i am looking for. Now i just need to enter the real formulas with the real data (which is going to take forever- HA) but it will work.... so great!!!!! thank you!

Again thank you all for your assistance on this but I need to tweak the code a little more or at least get a better understanding of the code so I can hopefully do it myself.
First to give you an update…. I have two new constants… A5 is L# and A6 is M#

Instead of col A being the deciding factor to full the formulas in I need it to be D.
If you enter data in D it will complete the formula set up like it does now but it will also add the value (it’s a date) from Constant A5 to the current A row as well at the constant M# form A6 will automatically be copied into the current B row.
What I am trying to do is to remove as much of the repetitiveness from the user so simplify adding of data.
Thank you.

AHHHH i got it... not that stupid afterall.. hehe..


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 3 And Target.Row > 10 Then
r = Target.Row
Range("A" & r).Formula = "*$B$5"
Range("B" & r).Formula = "*$B$6"
Range("J" & r).Formula = "=E" & r & "*$B$1"
Range("K" & r).Formula = "=J" & r & "*E" & r & "/$B$2"
Range("L" & r).Formula = "=J" & r & "*$B$3/$B$1"
Range("M" & r).Formula = "=SUM(J" & r & ":L" & r & ")/$B$4"
End If
End Sub

I now understand the concepts given in the examples above and am about to start the real coding. I have run into a bit of a snag and require just a little more assistance.
What would the code structure be for the 3 examples below? Its all just a bit confusing to me and I have A LOT of formulas to code so I want to be sure that its doing what I expect. Hehe..
Thanks again!

=SMALL($AB$4:$AB$12, COUNTIF($AB$4:$AB$12,"<"&(BV16))+1)

=VLOOKUP(CG16,$AD$4:$AE$12,2,FALSE)

=IF(BL16>0,(((AO16-$AI$9)*$AI$10)*-1),0)+IF(Y16="Heifer",AI11,0)

You'll have to give me context. For example, in your first equation, what's the relationship between BV16 and the cell the formula is in? Are they in the same row? same column? Why BV? etc. etc.

A workbook sample would be preferred.

walruseggman I would send over an workbook sample but I fear that would take me forever to do- it would be easier to just send the actual workbook. Problem with that is I am not “authorized” to give it out as it’s a proprietary program.
What I CAN tell you is that any line that does not have the “$” absolute assigned to it would be row specific. So for the first one for example:
=SMALL($AB$4:$AB$12, COUNTIF($AB$4:$AB$12,"<"&(BV16))+1)
All of this is looking absolute data with the exception of BV16 which would be I assume
Range("AA" & r).Formula = “SMALL($AB$4:$AB$12, COUNTIF($AB$4:$AB$12,"<"&(BV” & r & “))+1)”
Is that correct??
So would =VLOOKUP(CG16,$AD$4:$AE$12,2,FALSE)
Then be
Range("AB" & r).Formula = “VLOOKUP(CG” & r & “,$AD$4:$AE$12,2,FALSE)”

And then
=IF(BL16>0,(((AO16-$AI$9)*$AI$10)*-1),0)+IF(Y16="Heifer",AI11,0)
Would be
Range("AC" & r).Formula = “IF(BL” & r & “>0,(((AO” & r & “-$AI$9)*$AI$10)*-1),0)+IF(Y” & r & “="Heifer",AI11,0)”

walruseggman I would send over an workbook sample but I fear that would take me forever to do- it would be easier to just send the actual workbook. Problem with that is I am not “authorized” to give it out as it’s a proprietary program.
What I CAN tell you is that any line that does not have the “$” absolute assigned to it would be row specific. So for the first one for example:
=SMALL($AB$4:$AB$12, COUNTIF($AB$4:$AB$12,"<"&(BV16))+1)
All of this is looking absolute data with the exception of BV16 which would be I assume
Range("AA" & r).Formula = “SMALL($AB$4:$AB$12, COUNTIF($AB$4:$AB$12,"<"&(BV” & r & “))+1)”
Is that correct??
So would =VLOOKUP(CG16,$AD$4:$AE$12,2,FALSE)
Then be
Range("AB" & r).Formula = “VLOOKUP(CG” & r & “,$AD$4:$AE$12,2,FALSE)”

And then
=IF(BL16>0,(((AO16-$AI$9)*$AI$10)*-1),0)+IF(Y16="Heifer",AI11,0)
Would be
Range("AC" & r).Formula = “IF(BL” & r & “>0,(((AO” & r & “-$AI$9)*$AI$10)*-1),0)+IF(Y” & r & “="Heifer",AI11,0)”

Without knowing what you are trying to accomplish with the formulas, all I can say is that the syntax looks correct.

Well thats is at least good news on the way i need to code it. Thank you for all of your help.