Call Cell ref from another cell in a Funtion

mendi111

New Member
Joined
Jun 14, 2015
Messages
2
In worksheet I want to do some calculation on range suppose A20 to A40 , this range will change according to some condition, so what I want is that I will always pick start of the range ( here it is A20) from A1 cell and end of the range ( here it is A40) from B1 cell.

My custom function will take parameter as range I will pass A1:B1 as parameter to it and then function will map this range input to actual range .....here it will be A1:B1 abs input range and it will change to A20:A40 internally .
As shown below.

MyFun ( rng as Range)

Dim MyRng as Range
------
------
' here rng is = Range ( A1:B1)
' and MyRng = Range ( A20:A40) based on value in A1 and B1 cell.
-----
'do some calculation

End Function

I want help in converting the input range A1:B2 to actual range A20:40.

Regards
Mendi
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Mendi,

2 quick questions-

1. will the range always be on Column A?
2. in your example above is Range("A40") the last used cell? What determines the last cell that you use?

thx

FarmerScott
 
Upvote 0

Forum statistics

Threads
1,202,981
Messages
6,052,898
Members
444,610
Latest member
dodong

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