VBA Loop through range to copy/paste values not working

rossross

Board Regular
Joined
Apr 11, 2022
Messages
50
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 
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?

i either had something backwards or tried with ws2 or ws1. just a bunch of trying to correct the logical flow. which for a mush brain such as myself, isn't so easy.
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Forum statistics

Threads
1,226,521
Messages
6,191,535
Members
453,662
Latest member
Xcelling

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top