CODEWITHSUNDEEP
vbaexcel
javad
javad

Reputation: 105

Cancatenate the range between two cells in a sheet from addresses of two cells in another sheet

I have two cells in sheet1 that contain address for two cells in sheet2. for example in sheet1, the cells(1,1).value has the address $A$6 and cells(1,2) has the address $A$100. These two addresses refers to two cells in sheet2. Now I want to concatenate the range between $A$6 to $A$100 from sheet2.
My code doesn't work:

Sub concatenate()

   Set wss = Sheets("sheet1").Range("sheet2.Cells(1, 1).Value : sheet2.Cells(1, 2).Value")
   For Each cel In wss 
   tval = tval & cel.Value
   Next
   Sheets("sheet2").Cells(1, 7).Value = tval

 End Sub

Upvotes: 0

Views: 32

Answers (1)

Matteo NNZ
Matteo NNZ

Reputation: 12665

Your syntax is incorrect because you're passing the whole parameter as a string, while it should be variables and strings. This should work, say all the rest is good: 

Set wss = Sheets("sheet1").Range(sheet2.Cells(1, 1).Value & ":" & sheet2.Cells(1, 2).Value)

Explanation

This: sheet2.Cells(1,1).Value returns "$A$6" if executed; but, if you write it into a string as you did above (i.e. "sheet2.Cells(1,1).Value"), it means exactly "sheet2.Cells(1,1).Value". And clearly, the Range("sheet2.Cells(1,1).Value") doesn't exist.

Upvotes: 1

Related Questions