Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Using ActiveCell.Value in Formula

0

Hi, after entering a value in a cell, I'd like to use a macro shortcut to calculate that value in a formula. 

For an example:

    ActiveCell.FormulaR1C1 = "=100*K/BAZ" (where "K" and "Baz" are named ranges)

works and shows the formula when the cell is selected but always calculates with the value 100. 

    ActiveCell.FormulaR1C1 = ActiveCell.Value * Range("K").value / Range("Baz").value

also works but gives out the result, the formula can't be seen.

When I tried to write ActiveCell.Value * R1C1 style address, I keep getting value returned 0, or a division to 0 error, although there referenced cells are not zero. 

How do I write it so that the formula uses the value in the selected (active cell) and when the macro calculation is done, it also shows the formula? 

Thank you!

Ezybao

excel
posted by: ezybao (rep: 16) on Thu Jan 4, 2024 at 10:59 am
Answer
Discuss

Answers

0
Selected Answer

Hi Ezybao and welcome to the Forum

If you have those named ranges already, in VBA, just write your value in a cell and (with that cell selected) use:

ActiveCell.Formula = "=" & ActiveCell.Value & "*100/BAZ"

Witha value of 56 say, the formula will show in the cell as:

=56*100/BAZ

and the value displayed will depend on K and BAZ (plus formatting)..

Hope that fixes it for you. If so, please remember to mark this Answer as Selected.

posted by: John_Ru (rep: 6142) on Thu Jan 4, 2024 at 12:40 pm
Discuss

Discussion

Wow, it was this simple.. My general Excel skills are good, but I am not experienced in VBA. Thank you very much!

My father created these Lotus123 documents 25 years ago, which we still use at work. In his version, when you want to enter a value, you first press the shortcut macro, CTRL+T, for example, nothing happens at first as it waits for you to input the value and then you press enter twice. The first adds the formula 2nd calculates the result. Do you know if this is possible here? In my method, we first enter the value of the cell and then select it again to run the macro. 

This was the first time I used this (or any) forum, gonna post a 2nd question on another subject now. You guys are amazing!
ezybao (rep: 16) Jan 4, '24 at 1:04 pm
@John

In your answer (which I tested and works nicely) I changed "*100/BAZ" to "*K/BAZ"
as ezybao has in the original post/question so the formula is dynamic.
WillieD24 (rep: 557) Jan 4, '24 at 1:06 pm
@Willie - thanks for correcting my error!

@Ezybao - please note the error pointed out above. Thanks for selecting my Answer.

Note that you may need to ask a new question tomorrow since new users are limited for questions (for good reason)
John_Ru (rep: 6142) Jan 4, '24 at 4:04 pm
@Willie thanks! 

@John_Ru I had a follow-up question in my first response, asking if it was possible to do this macro in a way similar to the Lotus 123 example. Do you think it can be done? 

Also thanks for the warning about the question limit, I'll wait for 24 hours the pass to post my 2nd question. 
ezybao (rep: 16) Jan 5, '24 at 2:30 am
Hi again Ezybao

If you want to use Ctrt+T then follow with 2 returns (like your Lotus123 file), that could be done in VBA (but the formula would produce the result straightway, unless you had to press Ok to really add it to the cell).

Alternatively, if the formula is needed in a given area (range) of the spreadsheet, say a particular column, VBA could do it once you just enter the value and move away from that cell.

Please ask your new question and say what you want to happen (or not happen if you press Cancel perhaps). Better still try to do it in VBA (like you did in this question) then ask when you get stuck- that's how the Forum should work in general.

Hopefully Willie or I will have the spare time to answer you, but it may be over the weekend.
John_Ru (rep: 6142) Jan 5, '24 at 2:57 am
Add to Discussion
0

Hello ezybao and welcome to the forum.

You haven't uploaded a sample file so I can only provide a general answer.

In you first line of code - ActiveCell.FormulaR1C1 = "=100*K/BAZ" - always calculates using 100 because 100 is in the formula.

In your second line - ActiveCell.FormulaR1C1 = ActiveCell.Value * Range("K").value / Range("Baz").value - it creates a circular reference and that's why it doesn't work.

It is possible to use the worksheet Worksheet_SelectionChange event to insert the formula to a cell while using the "ActiveCell.Formula" in the code; something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Result As Long
Dim K As Long, BAZ As Long
Dim Where As Variant

K = Range("A2").Value
BAZ = Range("B2").Value
Where = ActiveCell.Offset(-1, 0).Address(0, 0)

If Not Intersect(Target, Range("C3")) Is Nothing Then   ' specify the cell where change will occur
    ActiveCell.Formula = "= " & Where & " * K / BAZ"
End If

End Sub

I named cell "A2" as "K", cell "B2" as "BAZ" to test this out. Cell "C2" is the cell where the value is changed. Cell "C3" is where the formula is entered.

You will need to adjust the code to suit your particular situation. If this solves your problem please mark my answer as selected.

Cheers   :-)

posted by: WillieD24 (rep: 557) on Thu Jan 4, 2024 at 12:44 pm
Discuss

Discussion

@Willie - nice response but one minor learning point... 

You said "In your second line - ActiveCell.FormulaR1C1 = ActiveCell.Value * Range("K").value / Range("Baz").value - it creates a circular reference and that's why it doesn't working" but that's not true-   the formula would be created by VBA from the current values (without a circular reference to the cell address). . 
John_Ru (rep: 6142) Jan 5, '24 at 5:24 am
@John

Thanks for clearing that up.
WillieD24 (rep: 557) Jan 6, '24 at 5:10 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login