Set a name range that works when calling other macros

[gordonlyle]

Hi clever folks,

Is there a way to name a range that will work when calling other macros?

By this I mean that in the example below, if I run "rail4_megacalc" then it errors when it finds 'cantilever' as it doesn't seem to have a name associated. I'm just trying to reduce the amount of times I have to recreate the span1200 when I have many

---

Sub rail4_megacalc()

' calculates rail 4 with all fixed point brackets
'
Dim cantilever As Range
Set cantilever = Range("AH17:AJ17")
Dim span As Range
Set span = Range("AH21:AJ21")
Dim check As Range
Set check = Range("AD37")
Dim fixed As Range
Set fixed = Range("AB27:AD27")

Call span1200

---

End Sub


Sub span1200()

cantilever.Select
ActiveCell.FormulaR1C1 = "600"
span.Select
ActiveCell.FormulaR1C1 = "1200"
If check = "0" Then End
fixed.Select
ActiveCell.FormulaR1C1 = "Fixed double"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed XL"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed single"
cantilever.Select
ActiveCell.FormulaR1C1 = "550"
If check = "0" Then End
fixed.Select
ActiveCell.FormulaR1C1 = "Fixed double"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed XL"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed single"
cantilever.Select
ActiveCell.FormulaR1C1 = "500"
If check = "0" Then End
fixed.Select
ActiveCell.FormulaR1C1 = "Fixed double"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed XL"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed single"
cantilever.Select
ActiveCell.FormulaR1C1 = "450"
If check = "0" Then End
fixed.Select
ActiveCell.FormulaR1C1 = "Fixed double"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed XL"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed single"
cantilever.Select
ActiveCell.FormulaR1C1 = "400"
If check = "0" Then End
fixed.Select
ActiveCell.FormulaR1C1 = "Fixed double"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed XL"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed single"
cantilever.Select
ActiveCell.FormulaR1C1 = "350"
If check = "0" Then End
fixed.Select
ActiveCell.FormulaR1C1 = "Fixed double"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed XL"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed single"
cantilever.Select
ActiveCell.FormulaR1C1 = "300"
If check = "0" Then End
fixed.Select
ActiveCell.FormulaR1C1 = "Fixed double"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed XL"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed single"
cantilever.Select
ActiveCell.FormulaR1C1 = "250"
If check = "0" Then End
fixed.Select
ActiveCell.FormulaR1C1 = "Fixed double"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed XL"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed single"
cantilever.Select
ActiveCell.FormulaR1C1 = "200"
If check = "0" Then End
fixed.Select
ActiveCell.FormulaR1C1 = "Fixed double"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed XL"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed single"
cantilever.Select
ActiveCell.FormulaR1C1 = "150"
If check = "0" Then End
fixed.Select
ActiveCell.FormulaR1C1 = "Fixed double"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed XL"
If check = "0" Then End
ActiveCell.FormulaR1C1 = "Fixed single"

End Sub

---

Many thanks to you all in advance.
Gordon

​

 

[Joe4]

You can pass values (like ranges) from one procedure to another.
See: http://www.homeandlearn.org/passing_values_to_a_sub.html

Alternatively, you can name ranges via VBA. Then your other VBA code can reference that same named range.
See: https://www.mrexcel.com/forum/excel-questions/26288-name-range-vba-should-easy.html