1

I'm trying to make a range value "paste" without using the copy/paste-function. I'm fairly new to VBA and don't understand why my code doesn't work. I'm sorry if this is a type of question which is already answered but I can't figure my problem out with other posts.

I'm able to select the range I want my values to copy from and paste too with

name_task = [code].select name_task_2 = [code].select

But i can't write values to name_task_2 with

name_task_2 = name_task.value

This works, and for ME is what I've written somehow the same just more complex, but since it doesn't work it obviously isn't the same :D

sht2.Range("C2:D12") = sht1.Range("A8:B18").value

Excel sheet1

Sub Time_Estimate() Application.ScreenUpdating = False Dim name_task As Variant Dim name_task_2 As Variant Dim R_count As Double Dim C_count As Double Dim sht1 As Worksheet Dim sht2 As Worksheet Set sht1 = Sheet1 'Sheets("Tekla_2016") name_task = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown)) R_count = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown)).Rows.Count 'Debug.Print name_task.Rows.Count 'Debug.Print name_task.Columns.Count Debug.Print R_count Set sht2 = Sheet2 'Sheets("Timeforbruk_2016 - UFERDIG") name_task_2 = sht2.Range("C2:D2", sht2.Range("C2:D2").Offset(R_count - 1, 0)) 'name_task_2 = name_task.value 'Why doesn't this work??? 'sht2.Range("C2:D12") = sht1.Range("A8:B18").value 'This works but it's not dynamic Application.ScreenUpdating = True End Sub

EDIT: This code now work as I want:

Sub Time_Estimate() Application.ScreenUpdating = False Dim name_task As Range Dim name_task_2 As Range Dim rng_sht1 As Range Dim rng_sht2 As Range Dim R_count As Double Dim C_count As Double Dim sht1 As Worksheet Dim sht2 As Worksheet Set sht1 = Sheet1 'Sheets("Tekla_2016") Set sht2 = Sheet2 'Sheets("Timeforbruk_2016 - UFERDIG") Set name_task = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown)) R_count = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown)).Rows.Count Set name_task_2 = sht2.Range("C2:D2", sht2.Range("C2:D2").Offset(R_count - 1, 0)) name_task_2 = name_task.value Application.ScreenUpdating = True End Sub
Improve this question
3
  • So what exactly doesn't work? The sub you posted or just the first to lines of code? Also important is the error message. Commented Sep 23, 2016 at 8:07
  • name_task = [code].select Doesn't work, but name_task = [code] should work, if [code] returns a range object. Just like in your other example: sht2.Range("C2:D12") = sht1.Range("A8:B18").value works, but sht2.Range("C2:D12").Select = sht1.Range("A8:B18").value will not work - it doesn't make sense. Commented Sep 23, 2016 at 8:09
  • If you are counting something like rows you don't need a Double. It's better to use an integer type. In VBA it is best to use Long (4 bytes) as Integer has only 2 bytes and thus has an upper limit of 32767 Commented Sep 23, 2016 at 10:20

1 Answer 1

Reset to default
3 
someRange.Select

selects the range someRange and returns the boolean value True (you usually don't notice that because you don't try to assign it to something). That means that after 

name_task = [code].select name_task_2 = [code].select

both name_task and name_task_2 are booleans (True) and not ranges.

In the sub you posted you have another problem:

name_task = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown))

since you didn't specify what name_task is (other than Variant, which basically means nothing), vba will use the range's Value as default property and name_task will be a variant array containing sht1.Range("A8:B8", sht1.Range("A8").End(xlDown)).Value. To assign a range object you need to use Set:

Set name_task = sht1.Range("A8:B8", sht1.Range("A8").End(xlDown))

From MSDN/this answer:

Set Keyword. In VBA, the Set keyword is necessary to distinguish between assignment of an object and assignment of the default property of the object. Since default properties are not supported in Visual Basic .NET, the Set keyword is not needed and is no longer supported.

This is actually a nice example why it is a good idea to use Option Explicit (or in your case to declare the variables properly). If you declare Dim name_task As Range VBA will tell you that something is wrong at the right line and troubleshooting becomes much easier.

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

4 Comments

fwiw, that's one of the better explanations I've read around here. ALWAYS Use Option Explicit
Thanks for reply! The return key is not my friend on this forum. I agree that variant is not a good idea and that's because of lack of knowledge, sorry. If I declare them as range I got it to work with set before both, name_task and name_task_2 Can you explain a little further what the set-argument actually do?
I have Option Explicit on, but I thought declaring as variant would save me.
@Grohl I added an explanation of Set from MSDN. Declaring it as variant saved you from one error ;) There are some cases where you need a variant, such as working with arrays. Most of the time you are better off declaring variables as their actual type though.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.