How can I use a string from a text box as a reference for a range in excel vba?

Elfman

New Member
Joined
Dec 7, 2016
Messages
1
Hi! I'm quite a newbie to VBA macros and I am in urgent need. I created a Userform to save some time with copy and pasting excel content. Now I have the following issue: I get a string output from a Userform - Textbox (txtHRS_ANW). I created exactly the same names as named ranges. Now I want to use the string output as a reference for the named range. I am not able to add the double quotes in the brackets - i tried using """" and chr(34) My code is as follows:


Code:
 Private Sub cmdHRSLoading_Click()
    Dim NameRange As String
    If chkANW = True Then
        NameRange = Me.txtHRS_ANW.Value 'gives me following string HRS_ANW_CORP01 (until CORP10 depending on user entry)
        Sheet5.Range(NameRange).Select 'here I would like to use the string as a reference for the range
        Selection.Copy
        Sheet9.Range("A" & Rows.count).End(xlUp).Offset(1).Select
        Selection.Paste
    End If
    
    End Sub
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
Welcome to the board

I'm assuming the text string you get IS the correct range name, and that there is only one of each in the workbook (i.e. not duplicated on move/copied worksheets). You therefore don't need to reference the worksheet (range name is enough, so long as you aren't working in a separate workbook), and you don't need to select anything (I'll let you off that one as you're a VBA novice, but it's a bad habit!). It also looks like you're working on Sheet9 and trying to select on Sheet5 without first selecting Sheet5, which is probably the source of your problem. If instead you're on Sheet5, then your code to select the range on Sheet9 would give the wrong result as "Rows.Count" will default to the currently-active worksheet, as the Row object is not fully referenced. Finally I've renamed your variable to clarify that it's a string - as you write longer and longer macros, you'll need to easily see what type of variable you're working with, so get in the habit of using a specific convention, e.g. str###, int###, dbl### etc.

Try this instead
Code:
Private Sub cmdHRSLoading_Click()
Dim strNameRange As String

If chkANW = True Then
    strNameRange = Me.txtHRS_ANW.Value
    Range(strNameRange).Copy
    Sheet9.Range("A" & Sheet9.Rows.count).End(xlUp).Offset(1,0).Paste
End If

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,366
Messages
5,601,199
Members
414,434
Latest member
Riyen

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
Top