Excel 2007 : VBA help

[kmkelley]

I have some spreadsheets that need updating. I have a macro that can pull the data if I input the range manually but I was hoping to get it to pull based on a cell value as I need this to work for different values.

Cell A15 in MySheet has the value. I want it to generate a range to use later in the macro, MyRange so that if the value of A15 was 4 I would get the range to be "A1:A4"

Any ideas?

Sub Test()
'
'
Dim MySheet As String
Dim MyNum As Integer
Dim MyRange As Range
    MySheet = Range("A1").Value
    MyNum = Range("A15").Value
    MyRange = A1: AMyNum
    
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Sheet1").Select
    Range("A1").Select
    Cells.Find(What:=MySheet, After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Offset(0, -1).Range(MyRange).Select
    Selection.Copy
    Sheets(MySheet).Select
    Range("A17").Select
    ActiveSheet.Paste
    
    ' Notify the user that the process is complete.
   MsgBox "Fund List has been updated"
    
End Sub

[TMS]

Please note that you should put CODE Tags around your code. The Forum Moderators are quite strict about following the forum rules ... and this is one of them.

Assuming you do that, try this:

Dim MyNum As Long
MyNum = Range("A15").Value
Set MyRange = Range("A1: A" & MyNum)

or

Set MyRange = Range("A1: A" & Range("A15").Value)

Regards

[kmkelley]

That worked!! Thank you

[Cutter]

They're pretty strict about vague thread titles, too. As explained in Rule #1.

[TMS]

@kmkelley: thanks for the feedback. And thanks for adding the Code Tags

Cutter makes a good point which I should really also have mentioned.

That said, it's sometimes difficult to know how to word it.

Maybe something like: "How to use a variable in a VBA range statement"

Regards

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST