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]

Did you step through that code and watch its execution and verify that the variables are what you expect? The only thing that looks suspect to me is that your variables are at the module level and I don't see a need for that. That means as long as they're in scope they can be affected by other code in the same module, so maybe that's your issue. Do you really need to use Value2 instead of Value? BTW, if you can use Value, you don't need to use it at all.

 

[StephenCrump]

.... or doing what i want backwards.

for example, interest rate in (i,18) needs to be placed in range "C4"

This code line is doing the reverse of that, putting the C4 value into Cells(i,18)

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

And this line will put the same C4 value in every row (i.e. every value of i), which I doubt is what you want?

 

[rossross]

This code line is doing the reverse of that, putting the C4 value into Cells(i,18)

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

And this line will put the same C4 value in every row (i.e. every value of i), which I doubt is what you want?

i've messed around with reversing the variables and what i get is either it places blanks on ws2 which is what i don't want or if i reverse it, it does nothing. and if i change from with ws1 to with i also get a complete bypass of the commands

 

[saboh12617]

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

 

[rossross]

This code line is doing the reverse of that, putting the C4 value into Cells(i,18)

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

And this line will put the same C4 value in every row (i.e. every value of i), which I doubt is what you want?

the code as posted above completely bypasses everything from lr = .cells.... to end with.. when switching the variables around to say ws2 = ws1, i also get a bypass of my code in the same spot. when switching my statement to with ws2 instead of with ws1 and my variables to say ws2 = ws1, it places the values from ws2 into ws1 which i want the opposite of. whenever i leave the variables as is above, it takes the values from what would be (i, 5) and places them in an undesired, also ID'ed range in ws2 which i also don't want. whenever i state with ws2 and set ws1 = ws2 it takes the values from ws1 and places them in ws2 which is also the opposite of what i want. so no matter how i flip these things around, i'm either getting the opposite of what i want or nothing at all.


the code is definitely broken but i don't know why as i've used something substantially similar in other codes before. open to doing it an entirely different wa.y

 

[rossross]

Did you step through that code and watch its execution and verify that the variables are what you expect? The only thing that looks suspect to me is that your variables are at the module level and I don't see a need for that. That means as long as they're in scope they can be affected by other code in the same module, so maybe that's your issue. Do you really need to use Value2 instead of Value? BTW, if you can use Value, you don't need to use it at all.

yes, i've stepped through the code but no matter how i flip them around, i'm getting either the opposite of what i want or nothing at all. and i was told that value2 was slightly quicker than value. is this not true?

 

[Micron]

Your calcs sheet has at least 9 rows with data, yes? If not, your counter will not work. Can you post some data for each sheet using XL2BB add-in? Or upload a wb copy to a file share?

I've never used Value2. Apparently the only difference is that it cannot return currency or date data.

 

[rossross]

Your calcs sheet has at least 9 rows with data, yes? If not, your counter will not work. Can you post some data for each sheet using XL2BB add-in? Or upload a wb copy to a file share?

I've never used Value2. Apparently the only difference is that it cannot return currency or date data.

there's 1 header row, then two rows of data currently as it's just placeholders and simply in there for testing. a row of weighted averages plus two more 3 mores of data and some other things so i say count to last row which is row 11 then subtract 8 to get row 3 currently. using column 1 anyway it correctly ID's the third row as the bottom of my data which i want.

 

[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

my laptop is currently on the fritz at the moment so i'll try this ASAP. but i will say that it is correctly identifying the last row of my data when i step through it