VBA to insert formula into cell based on another cell

[midnightorion]

Ok, so I'm having trouble figuring this out.

What I'm trying to do is have cell "A9" = If "Calculations On" Then

"B19" = "formula"

With a Message Box that prompts the user that "Calculations are on"


If "A9" = "Calculations Off" Then

Delete "B19" forumla.


Here's what I have so far. I'm sure there's a more efficient way of doing this, but I'm a complete noob and can't seem to grasp coding. I can manipulate the hell out of it, but always seem to forget all the stuff I learn shortly after

Please Login or Register  to view this content.

[midnightorion]

I've updated the code to:

Please Login or Register  to view this content.

But I seem to end up in an endless loop? Any help is appreciated.

[midnightorion]

Please Login or Register  to view this content.

My latest attempt.

It seems as it doesn't matter what cell I change, it will trigger the event? I only want it to trigger if the Status of "A9" Changes. But either way, once trigger still runs an endless loop?

[thatandyward]

You could try something like this…

Please Login or Register  to view this content.

This only executes if cell A9 is changed, it also sets all the formulas at once by using R1C1 notation.

I have left out turning off screen updating/display alerts - this sub isn't really doing much and switching them on/off would likely increase the run time. But you can experiment and see what you think works best, don't forget to turn them both back on before your sub exits!

[midnightorion]

I just tried it, but it seems as if it goes straight to "Somethings Wrong" and exits no matter what selection is chosen.

Also I don't really need that Statement in the code. I put it there to see if I could find out which section of the code was being triggered or where it stopped.

[thatandyward]

Are you sure the values in A9 match the values in the code?

You might want to use Data Validation to ensure only those 2 values can be set in A9

Realized your example was simplified/testing code, just structured it as you had it. obviously just delete the sections you don't need once it's doing what you want.

[midnightorion]

I see what was done now. I've been playing with the Text trying to figure out if it was something with my validation and shortened it to "Yes" and "No" Where your example had the full "Calculation Difference On". Once that was corrected it started triggering. One other issue was the Calculation was subtracting itself, instead of the other cell. Playing with it, and adjusting -2 to -1. It now appears to be working perfectly. I have no idea how you came up with this, but thank you very much!!!!


Final Code: Didn't change anything, just corrected the calculation.

Please Login or Register  to view this content.

[thatandyward]

You're welcome.

Yes, I made a copy/paste error with the [-2] instead of the [-1] in the second half of the formula, well caught!

The R1C1 notation can be very useful in situations like this, essentially it's defining the cells in the formula relative to the current cell i.e. …CurrentRow[-2] CurrentColumn… you can also use R1C1 to define absolute cell references by just using the row/col # R19C2 would be B19, 19th row and second column, you can also mix-and-match R[-2]C18

[midnightorion]

Awesome thanks for the tip, but.....
I often see coders posting R1C1 or R19. How do you guys know which Row and Column your in if say your in Cell F55 ? I have alot to learn about real coding.

[thatandyward]

In R1C1 both the rows & the columns are referenced numerically, they're also switched over from A1 notation i.e. rows first, then columns.

The column numbering is as you would expect Col A = 1, Col B = 2, Col C = 3 …

So cell F55 in A1 notation => R55C6 in R1C1 notation i.e. row #55 column #6

Most VBA code/functions refers to rows & columns by number and although it takes a bit of getting used to it does make life easier than dealing with column letters. VBA code/functions also refers to rows first, then cols, much like R1C1, in fact R1C1 is how Excel internally handles cell addresses, the A1 notation is really just a 'surface skin' to make it easier for general users.

One trick that helped me a lot whilst I was getting used to it was the Column() function. You can enter =COLUMN() as a formula in a cell and it will return the column number. I would quite often add that to a run of columns whilst I was coding something up so I could quickly see which column was which.

[midnightorion]

In R1C1 both the rows & the columns are referenced numerically, they're also switched over from A1 notation i.e. rows first, then columns.

The column numbering is as you would expect Col A = 1, Col B = 2, Col C = 3 …

So cell F55 in A1 notation => R55C6 in R1C1 notation i.e. row #55 column #6

Most VBA code/functions refers to rows & columns by number and although it takes a bit of getting used to it does make life easier than dealing with column letters. VBA code/functions also refers to rows first, then cols, much like R1C1, in fact R1C1 is how Excel internally handles cell addresses, the A1 notation is really just a 'surface skin' to make it easier for general users.

One trick that helped me a lot whilst I was getting used to it was the Column() function. You can enter =COLUMN() as a formula in a cell and it will return the column number. I would quite often add that to a run of columns whilst I was coding something up so I could quickly see which column was which.

Ah ok that makes sense and thanks for the detailed response, I really appreciate the help.
I'll try and use the R1C1 notation more often, as well as the =column() formula to help along the way.

Thanks again and have a good night.