VBA: Using a concatenated string as a command

Question

How can you you use a concatenated string as a command in VBA? The following code results in the following error:

Run-time error 424: Object required

Sub run()

    Dim pic As Picture
    Dim wks As Worksheet
    
    Set wks = Sheets("Counter Party Select")
    wks.Unprotect
    
    Dim company As String
    Dim sRange As String
    Dim concate As String
    Dim quote As String
    
    Dim s1 As String
    
    company = wks.Range("B3")
    
    'This vlookup pulls the correct range based on the value
    sRange = Application.VLookup(company, Sheets("Company Logos").Range("D3:E1000"), 2)
    s1 = "Company Logos"
    quote = Chr$(34) 'The character number of a quatation mark
    Concat = "Sheets(" & quote & s1 & quote & ")." & sRange

    'This results in Concat = "Sheets("Company Logos").Range("A146:A148")"
    
    Concat.Copy  'The error occurs on this line 
    
    wks.Select
    wks.Range("D2").Select
    ActiveSheet.Paste
    wks.Range("A1").Select

End Sub

How can I rewrite this code so that I can make the string an object and execute the .Copy command on it?

Dim Concat As Range and Set Concat = WorkSheets("Company Logos").Range("A146:A148") — Scott Craner
– Scott Craner, Commented Jan 22, 2021 at 17:23
You cannot use a concatenated string as a full-fledged VBA statement. You can however easily concatenate a range address. Please elaborate how you are assembling your string. — GSerg
– GSerg, Commented Jan 22, 2021 at 17:25
@GSerg I have a table in a sheet that has values in the first column and ranges in the second column. The procedure uses a vlookup on the first column to pull the necessary range on the second column. It then uses that range to concatenate the string — FinDev
– FinDev, Commented Jan 22, 2021 at 17:26
I don't see where concatenation comes from. What you have described is Dim Concat As Range, Set Concat = Sheets("Company Logos").Range(Application.WorksheetFunction.VLookup(...)). — GSerg
– GSerg, Commented Jan 22, 2021 at 17:28
That is exactly what I said above. Dim Concat As Range, Set Concat = Sheets(s1).Range(sRange), assuming sRange contains "A146:A148". If your sheet (and therefore sRange) contains "Range("A146:A148")" in that second column, then fix it to only contain "A146:A148". — GSerg
– GSerg, Commented Jan 22, 2021 at 17:33

GSerg · Accepted Answer · 2021-01-22 18:01:59Z

2

You cannot use a concatenated string as a full-fledged VBA statement. You can however easily concatenate a range address.

You cannot do that straight away because you have made your life more complicated by storing wrong data:

'This results in Concat = "Sheets("Company Logos").Range("A146:A148")"

You should not store the literal text Range("A146:A148") in that second column that you VLookup. You should only store the address itself, A146:A148, as text.

Fix the data on the sheet accordingly, and it will work as expected:

Dim Concat As Range
Set Concat = Sheets(s1).Range(sRange)

answered Jan 22, 2021 at 18:01

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA: Using a concatenated string as a command

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related