Use variable within excel function

spencer_time

New Member
Joined
Sep 19, 2019
Messages
41
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,509
Office Version
365
Platform
Windows
For sheet names try
Code:
Set Rng = Sheets("Sheet" & num).Range("A1").CurrentRegion
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,607
Office Version
2007
Platform
Windows
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
 

spencer_time

New Member
Joined
Sep 19, 2019
Messages
41
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,509
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,085,020
Messages
5,381,248
Members
401,725
Latest member
Soly_Man

Some videos you may like

This Week's Hot Topics

Top