Change cell reference in the middle of a formula to a value?

Re: Change cell reference in the middle of a formula to a va

I'm not sure if I understand you completely, but if I do, here is a little macro that will write your formula into cell C1, building in the value found in cell A1:

Re: Change cell reference in the middle of a formula to a va

Jmiskey, your solution works perfectly! I thought that I could shorten your two lines:

myFormula = "=(if(b1=8," & Range("A1") & "*(b1+b7),0))"
Range("C1").Formula = myFormula

so, I tried:

Range("C1").Formula = "=(if(b1=8," & Range("A1") & "*(b1+b7),0))"

and, it worked fine. Is there a special reason for using your two lines? Maybe for clarity and neatness?

I think your solution was very good. Thank you for your contribution to this forumn.

Re: Change cell reference in the middle of a formula to a va

I already got a whole bunch of formulas in a whole bunch of places, and isn't your solution just a new way to write a single formula into a single cell or am I not seeing the magic? You know how when you are in a formula and you press F9 and it magically turns the formula into a number, well I want that, but I only want to change a part of formula, not the whole thing.

Ah, I don't think there is anything that can do this. I think I will have to write =A1 into another cell, like D1, and then refer to D1 instead of A1 in my formula. And then when I need it, my macro will copy / paste special values D1 so that it turns into a number. Then I can get rid of A1. So instead of =(if(b1=8,a1*(b1+b7),0)) I will have =(if(b1=8,d1*(b1+b7),0)) and d1 starts off having =a1 in it, but after paste special values it will have 7 in it.

Re: Change cell reference in the middle of a formula to a va

Mark,

I don't think there is any way you can do something like press F9 and have it update just "part" of the formula (just like you can't do two different text formats in one cell!).

Ralph,

I split it up between two lines for the reasons you said, neatness and clarity. I find that sometimes if you try to make the code and short as possible, it can get kind of jumbled and gets kind of confusing as to what is actually happening, (especially if you are updating your code a year later!).

Re: Change cell reference in the middle of a formula to a va

Jmiskey, thank you for your explanation. It makes good sense to me, too, to be consistent and formal in coding, as in most things that require understanding by others, or by yourself at some later dated. Sort of like good documentation. So, I will try to emulate you, and no try to crowd things into one line, just to reduce the length of the code. Thanks again.

The main reason I asked the question is that, being just a fringe user and understander of Visual Basic, I have umpteen doubts as to why most of the VB code is done as it is. I come from BASIC and QuickBASIC, where, to me, things were pretty straight forward. I do find VB to be very complicated. I don't understand the basic structure at all, and tend to give up too quickly. I do think that I will never progress in this discipline of VB! I should probably stick to my BASIC and QuickBASIC, and for engineering and such applications only, where logic is direct and short.

Regrds,

Ralph

Re: Change cell reference in the middle of a formula to a va

Ralph,

Don't give up on yourself to quickly! The only programming background I had before learing VBA was high school level BASIC and one year of college PASCAL 1. I find the logic to be quite similar.

I took one two-day Intro to VB for Excel class, and then just purchased some books and taught myself. If you have a basic understanding of programming, which it sounds like you do, you should be able to pick it up without too much trouble. There is so much to learn, I am constantly learning new stuff all the time (thanks to this board!), but you should be able to learn enough to do most things in a relatively short time period, if you work through the tutorials. And of course, use this sight for any questions you incur!

:wink: