Use variable within excel function

spencer_time

Board Regular
Joined
Sep 19, 2019
Messages
55
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
Hello, I have some test code that I can't get to work correctly. I am trying to figure out if it's possible to use a variable within an excel function, and if so, the proper way to do that.

The following is what I currently have, it is not working:
Code:
Sub embedVarFunc()

    Dim rng As Range
    Dim num As Integer
    num = 1
    Set rng = Sheet&num.Range("A1").CurrentRegion 'attempt to use variable as part of function
    
    MsgBox rng.Address
    
End Sub

If any of you know how to do this properly, any advice would be appreciated.
(the end use will be within a large spreadsheet that imports files to sheets, names those sheets after the filename of the originating file, and performs operations on the sheets with regard to the file name{which is in the format 01CEQ09, and the first two numbers will be extracted into a variable and used for further sorting and operations})

Thanks in advance for any advice.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
For sheet names try
Code:
Set Rng = Sheets("Sheet" & num).Range("A1").CurrentRegion
 
Upvote 0
Solution
To use the name of a sheet as a variable, it can be like this:

For example, if the sheet is called "1", then:

Code:
Sub embedVarFunc()


    Dim rng As Range
    Dim num As [COLOR=#ff0000]String[/COLOR]
[COLOR=#ff0000]    num = "1"[/COLOR]
    Set rng = [COLOR=#ff0000]Sheets(num)[/COLOR].Range("A1").CurrentRegion 'attempt to use variable as part of function
    
    MsgBox rng.Address
    
End Sub
 
Upvote 0
For sheet names try
Code:
Set Rng = Sheets("Sheet" & num).Range("A1").CurrentRegion

Thank you Fluff, this is exactly what I needed! You have been very helpful to me with regards to my spreadsheet I'm working on.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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