VBA code - Insert formula across randomly selected cells

[ctillett18]

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

 

[Trebor76]

Hi ctillett18,

Welcome to MrExcel!!

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

Robert

 

[ctillett18]

Work-related I guess? I'm just trying to figure out how to automate some data entry stuff I have to do.

 

[Trebor76]

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

See how this goes:

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

 

[ctillett18]

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?

 

[Trebor76]

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