VBA code - Insert formula across randomly selected cells

ctillett18

New Member
Joined
Sep 10, 2019
Messages
3
Hi - I'm trying to figure out how to write VBA code to insert a specific formula across a random selection of cells.

The gist of it is, that for whichever cells I have selected I want the code to write "= [EXISTING CELL VALUE] - adjacent cell".

My VBA code works as long as I only have a single cell selected ... how do I amend it to work on whichever cells I have selected? Code is below. I realize the issue is probably with the "ActiveCell" part of the code below, but I don't know how to get around it.

Sub CFS_2Q()
'
Selection.FormulaR1C1 = "=" & ActiveCell & "-RC[-1]"
'
End Sub
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hi ctillett18,

Welcome to MrExcel!!

Can I ask - is this an assignment or work related issue?

Robert
 
Upvote 0
OK thanks. It just looked like a school assignment which we don't answer directly.

See how this goes:

Code:
Option Explicit
Sub CFS_2Q()
    
    Dim rngMyCell As Range
    
    Application.ScreenUpdating = False
    
    For Each rngMyCell In Selection
        Selection.FormulaR1C1 = "=" & rngMyCell & "-RC[-1]"
        Exit For
    Next rngMyCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert
 
Upvote 0
Thanks Trebor76.

When I run your code, the macro works across multiple selected cells, but it maintains the single value in the actively selected cell (and applies that to the entire selection) as opposed to being dynamic if there are different values in different cells. In other words, if I were to apply the macro down the column with values:

10
9
8
7
6

it would work, but insert the "10" (assuming that was the originally selected cell) where the 9, 8, 7, & 6 are. HOWEVER, I was messing around with it a bit and came up with this:

Sub CFS2Q()
Dim a As Range
For Each a In Selection
a.FormulaR1C1 = "=" & a.Value & "-RC[-1]"
Next
End Sub

and it seems to be doing the trick. I'm honestly not sure why though. Any hints?
 
Upvote 0
My original code stops after the initial range has been set (probably a mistake on my part) where yours loops through each cell and sets the formula for that cell.

If you remove the "Exit For" from my code you will get the same result.

Glad we got it sorted ;)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top