Using a variable to name a selected range in VBA

Question

2 questions, the program I have made copy and pastes a table from another worksheet to the cell you have selected. What I want to do is name this selected table using a variable, how do I do this? In other words I have down the bottom (Selection.Name = Ans) but this will not name the selected what ever the variable Ans is

and for some reason the Vlookup is also not working correctly, I think it has something to do with how I set the range "MyRange".

'VLOOKUP

Dim Ans As Variant
Dim myVal As String
Dim MyRange As Range
Set MyRange = Sheets("Program (H)").Range("A1:B50")
myVal = Sheets("Program (H)").Range("D2")
Ans = Application.VLookup(myVal, MyRange, 2, False)
If IsError(Ans) Then
Ans = "Not Found!"
Else
'do nothing
End If

Selection.Name = Ans

If it helps the whole code is:

Sub New_Table()
    ' New_Table Macro

    'Reveals the Worksheet "Program (H)"
    With Worksheets("Program (H)")
    .Visible = True
    End With

    'Add 1 to the counter
    Sheets("Program (H)").Select
    Range("D2").Value = Range("D2").Value + 1

    'Paste the sheet
    Sheets("Caclulator (H)").Select
    Range("B3:P17").Select
    Selection.Copy
    Sheets("SOR Data").Select
    ActiveSheet.Paste

    'VLOOKUP

    Dim Ans As Variant
    Dim myVal As String
    Dim MyRange As Range

    Set MyRange = Sheets("Program (H)").Range("A1:B50")
    myVal = Sheets("Program (H)").Range("D2")
    Ans = Application.VLookup(myVal, MyRange, 2, False)

    If IsError(Ans) Then
        Ans = "Not Found!"
    Else
        'do nothing
    End If

    Selection.Name = Ans

    'Hide the sheet again

    With Worksheets("Program (H)")
        .Visible = False
    End With
End Sub

The vlookup works for me. Check your data. Regd your 1st query, can you rephrase it? — Siddharth Rout, Dec 12 '13 at 04:01
just changed it then, thank you. My data is two columns, the left column (A) is numbers 1 - 50, the right column B is names Table 1 - Table 50 — James Willcox, Dec 12 '13 at 04:06
I see what do you want to name? The cell which is the result of vlookup? — Siddharth Rout, Dec 12 '13 at 04:09
I've just stuck the entire code up, I want to name the table I pasted as it is still the last selected item. So I have a selected range, I just want to name that range based on the variable — James Willcox, Dec 12 '13 at 04:18
There are couple of problems with your code. You are using `.Select`. I would recommend using objects and fully qualify them so that in the end, you do not have to use `Selection.Name` [INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) Also you might want to move the naming part in the `Else` part of the `If` so that the naming happens only if a value is found. — Siddharth Rout, Dec 12 '13 at 04:23
Problem solved! I killed the VLOOKUP in VBA, did it in a single cell in excel and just referenced that cell to name the table, so much easier and only had to use one line — James Willcox, Dec 12 '13 at 05:20

score 0 · Answer 1 · answered Dec 12 '13 at 23:12

Problem solved! I killed the VLOOKUP in VBA, did it in a single cell in excel and just referenced that cell to name the table, so much easier and only had to use one line

Use:

Selection.Name = Worksheets("Program (H)").Range("F2")

Where F2 is the VLOOKUP in excel

Using a variable to name a selected range in VBA

1 Answers1