I Want to Link a Variable Range with Multiple Formulas

Hi,
You may apply like:

Dim rng As Range
Dim sn, en As Integer

sn = Range("B4").Value
en = sn + Range("C4").Value - 1
set rng = Range("E" & sn:"E" & en)
Range("G4").Application.Formula = "= SUM(rng)"
...
Range("K4").Application.Formula = "= MIN(rng)"

Good Luck !!!

Originally Posted by John521520

My Requirement:
I want to link Ranges of these 5 formulas with cells B4 and C4 (Cell B4 and C4 make basically a series staring and ending values)

Now whenever I have to find these five values for a specific range, I would only put required series starting and ending values in cells B4 and C4 respectively)

Try
vba

Sub test()
    Dim r As Range
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(\$?E\$?)\d+(:\$?E\$?)\d+"
        For Each r In [g4:k4]
            r.Formula = .Replace(r.Formula, "$1" & [b4] & "$2" & [c4])
        Next
    End With
End Sub

Or just
1)Select G4:L4
2) Ctrl + H (find + Replace) replace E4:E10 with OFFSET(INDIRECT("e"&$B$4&":e"&$C$4),,,$C$4-$B$4+1)
Then the formula should work as you change any one of B4:C4.

Administrative Note:

@sungjunbae

You need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code] [/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. As you are new here, I have done it for you this time.)

@John521520

Please make it clear to whom you are responding in each case, as I have done in this post. Thanks.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.