VBA Loop through range to copy/paste values not working

[rossross]

This is my code. I'm trying to go through the cells in ws2 to get key data points and place them in specific inputs locations in ws1. for example, interest rate in (i,18) needs to be placed in range "C4". once all items from row 1 for example are placed into the proper place, i need to go to the next row and have those done.

my code is either bypassing through all cell steps or doing what i want backwards.

Option Explicit


Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i As Long
Dim lr As Long

Sub placeinputs()

Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("Calcs")
Set ws2 = wb.Worksheets("Data")

With ws1
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    lr = lr - 8
   
    For i = 2 To lr
   
    ws1.Cells(i, 18).Value2 = ws2.Range("C4").Value2
    ws1.Cells(i, 46).Value2 = ws2.Range("C5").Value2
    ws1.Cells(i, 4).Value2 = ws2.Range("C10").Value2
    ws1.Cells(i, 19).Value2 = ws2.Range("C11").Value2
    ws1.Cells(i, 25).Value2 = ws2.Range("C12").Value2
    ws1.Cells(i, 28).Value2 = ws2.Range("C13").Value2
    ws1.Cells(i, 31).Value2 = ws2.Range("C14").Value2
    ws1.Cells(i, 26).Value2 = ws2.Range("C15").Value2
    ws1.Cells(i, 6).Value2 = ws2.Range("E2").Value2
    ws1.Cells(i, 29).Value2 = ws2.Range("E3").Value2
    ws1.Cells(i, 7).Value2 = ws2.Range("E4").Value2
    ws1.Cells(i, 23).Value2 = ws2.Range("E5").Value2
    ws1.Cells(i, 20).Value2 = ws2.Range("E6").Value2
    ws1.Cells(i, 32).Value2 = ws2.Range("E7").Value2
    ws1.Cells(i, 47).Value2 = ws2.Range("E12").Value2
   
    Next i
End With

End Sub

 

[Micron]

I do not understand the talk about value instead of value2 which is better in all ways except for dates.

How is it better in all ways?

Range.Value2 property (Excel)

Office VBA reference topic

learn.microsoft.com

Quote: "The only difference between this property and the Value property is that the Value2 property doesn't use the Currency and Date data types"

 

[rossross]

Hello,

I do not understand the talk about value instead of value2 which is better in all ways except for dates.
However i was wondering about events as it was implied above. Can you run the code below and tell if it is still not working, and if you have the msgbox popping up?

Sub placeinputs()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Set ws1 = ThisWorkbook.Worksheets("Calcs")
    Set ws2 = ThisWorkbook.Worksheets("Data")
    Dim i As Long
    Dim lr As Long
    With ws1
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        lr = lr - 8
        If lr < 2 Then MsgBox "Warning: last row is smaller than first row"
   
        Application.EnableEvents = False
        For i = 2 To lr
            .Cells(i, 18).Value2 = ws2.Range("C4").Value2
            .Cells(i, 46).Value2 = ws2.Range("C5").Value2
            .Cells(i, 4).Value2 = ws2.Range("C10").Value2
            .Cells(i, 19).Value2 = ws2.Range("C11").Value2
            .Cells(i, 25).Value2 = ws2.Range("C12").Value2
            .Cells(i, 28).Value2 = ws2.Range("C13").Value2
            .Cells(i, 31).Value2 = ws2.Range("C14").Value2
            .Cells(i, 26).Value2 = ws2.Range("C15").Value2
            .Cells(i, 6).Value2 = ws2.Range("E2").Value2
            .Cells(i, 29).Value2 = ws2.Range("E3").Value2
            .Cells(i, 7).Value2 = ws2.Range("E4").Value2
            .Cells(i, 23).Value2 = ws2.Range("E5").Value2
            .Cells(i, 20).Value2 = ws2.Range("E6").Value2
            .Cells(i, 32).Value2 = ws2.Range("E7").Value2
            .Cells(i, 47).Value2 = ws2.Range("E12").Value2
        Next i
        Application.EnableEvents = True
        .Calculate
    End With
End Sub

finally got to do this! yes, i get the warning message

 

[StephenCrump]

@rossross, let's go back to the beginning. Your code will do exactly what you tell it to do - it won't bypass, skip or reverse. But it's not at all clear what you want the code to do?

Let's also set aside the .Value/.Value2 discussion. Yes, .Value2 should be slightly faster, but it won't make a noticeable difference here.

I'm trying to go through the cells in ws2 to get key data points and place them in specific inputs locations in ws1. for example, interest rate in (i,18) needs to be placed in range "C4". once all items from row 1 for example are placed into the proper place, i need to go to the next row and have those done.

If you want to put a value in cell 4 of ws1, you code:

ws1.Range("C4").Value = ...

Hence, to put the value of the i-th row of column R of ws2 into C4, you'd say

ws1.Range("C4").Value = ws2.Cells(i, 18).Value

.

But what about the next value of i in the loop? Where does that go? If you don't vary the C4 reference the code will keep overwriting C4.

 

[StephenCrump]

finally got to do this! yes, i get the warning message

Your code is getting lr from ws1. Perhaps it should be from ws2 - which ties in with my previous question.

 

[rossross]

@rossross, let's go back to the beginning. Your code will do exactly what you tell it to do - it won't bypass, skip or reverse. But it's not at all clear what you want the code to do?

Let's also set aside the .Value/.Value2 discussion. Yes, .Value2 should be slightly faster, but it won't make a noticeable difference here.

If you want to put a value in cell 4 of ws1, you code:

ws1.Range("C4").Value = ...

Hence, to put the value of the i-th row of column R of ws2 into C4, you'd say

ws1.Range("C4").Value = ws2.Cells(i, 18).Value

.

But what about the next value of i in the loop? Where does that go? If you don't vary the C4 reference the code will keep overwriting C4.

i guess i didn't make it clear, sorry. i've dynamic ranges which i thought i sorted that piece out, but i guess i didn't. Say in ws2 (data) i need an interest rate from I, 18 to be placed in ws1 (calcs) in cell C4 then i need it to go through the same row and do the same for the other pieces of data until that row is complete. then i need it to move on to the next row and do the same.

 

[StephenCrump]

@saboh12617's Post #5 also raised the possibility of other code running? Do you have other code - e.g. a Worksheet_Change Sub, that might be interfering?

 

[rossross]

Your code is getting lr from ws1. Perhaps it should be from ws2 - which ties in with my previous question.

correct if i change the With statement to ws2, it correctly locates the last row. i've done a million iterations of swapping and didn't realize i had posted that version.

@saboh12617's Post #5 also raised the possibility of other code running? Do you have other code - e.g. a Worksheet_Change Sub, that might be interfering?

nothing else running, no

 

[StephenCrump]

Say in ws2 (data) i need an interest rate from I, 18 to be placed in ws1 (calcs) in cell C4 then i need it to go through the same row and do the same for the other pieces of data until that row is complete. then i need it to move on to the next row and do the same.

So you need to test lr on ws2, not ws1.

And assign the value like this:

ws1.Range("C4").Value = ws2.Cells(i, 18).Value

What does "do the same" mean? If we turn your code around, the first iteration of the loop will populate cells C4, C5, C5, C10, C11 ... E12 in ws1.

Which cells get populated on the next iteration of the loop?

 

[rossross]

So you need to test lr on ws2, not ws1.

And assign the value like this:

ws1.Range("C4").Value = ws2.Cells(i, 18).Value

What does "do the same" mean? If we turn your code around, the first iteration of the loop will populate cells C4, C5, C5, C10, C11 ... E12 in ws1.

Which cells get populated on the next iteration of the loop?

swear i tried this in one of the 10 iterations, but yep solution verified. thanks everyone for your help. long week.

 

[StephenCrump]

yep solution verified.

Great, I'm glad we could point you in the right direction.

But I'm curious what you're doing on the 2nd, 3rd, 4th etc iterations? Are you just overwriting C4, C5 etc?

What does "do the same" mean? If we turn your code around, the first iteration of the loop will populate cells C4, C5, C5, C10, C11 ... E12 in ws1.

Which cells get populated on the next iteration of the loop?