HELP NEED TO MAKE CODE GENERIC LOOPING THROUGH CELLS TO IMPORT DATA

[ytjjjtyj]

Hello,
Can anybody please help me find a faster and quicker way to code this without having to make this super long and repetitive. I am only changing a few things like location and the named range(fed_a..., fed_b... etc.)

For i = 1 To 100
Range("A307").Item(i, 1) = Hundred.Item(i, 1)
Range("A307").Item(i, 2) = Hundred.Item(i, 2)
Range("A307").Item(i, 3) = wkshtData.Range("Fed_A_" & Run.text).Item(i, 2)
Next i
wkshtDataAgg.Range("D307:EE406") = vntData_R
Range("A307D406").Name = "Fed_R_" & Run.text & "_Mapped"


For i = 1 To 100
Range("A408").Item(i, 1) = Hundred.Item(i, 1)
Range("A408").Item(i, 2) = Hundred.Item(i, 2)
Range("A408").Item(i, 3) = wkshtData.Range("Fed_A_" & Run.text).Item(i, 2)
Next i
wkshtDataAgg.Range("D408:EE507") = vntData_O
Range("A408D507").Name = "off_" & Run.text & "_Mapped"


For i = 1 To 100
Range("A509").Item(i, 1) = Hundred.Item(i, 1)
Range("A509").Item(i, 2) = Hundred.Item(i, 2)
Range("A509").Item(i, 3) = wkshtData.Range("Feed_A_" & Run.text).Item(i, 2)
Next i
wkshtDataAgg.Range("D509:EE608") = vntData_L
Range("A509D608").Name = "pop_" & Run.text & "_Mapped"


I don't know why the emojis are showing but it is suppose to be : D

[p45cal]

1. In:
Range("A307").Item(i, 1) = Hundred.Item(i, 1)
is Range("A307") on the same sheet as the wkshtDataAgg.Range("D307:EE406") in
wkshtDataAgg.Range("D307:EE406") = vntData_R
?

2. Where is this code sitting? In a standard code-module, a sheet's code-module, the ThisWorkbook code-module maybe, or elsewhere?

3. It might be that:

For i = 1 To 100
  Range("A307").Item(i, 1) = Hundred.Item(i, 1)
  Range("A307").Item(i, 2) = Hundred.Item(i, 2)
  Range("A307").Item(i, 3) = wkshtData.Range("Fed_A_" & Run.Text).Item(i, 2)
Next i

can be replaced without looping with:

Range("A307").Resize(100, 2) = Hundred
Range("A307").Resize(100).Offset(, 2) = wkshtData.Range("Fed_A_" & Run.Text)

but I'm not sure what Run is, nor Hundred.

[Kenneth Hobs]

When pasting code, type the code tags or click the # icon in the reply toolbar. Paste between tags.

As had similar questions as p45cal. I assumed that you meant range object names for Run and Hundred.

Test this on a backup copy. If you do a lot of this, I would poke the values for DoIt() into and array and iterate that for the calls.

Sub Main()  
  Dim ws As Worksheets
  Set ws = Worksheets("Sheet2")
  
  On Error GoTo endMain
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  
  DoIt [A307], 406, "Fed_R_", ws
  DoIt [A408], 507, "off_", ws
  DoIt [A509], 608, "pop_", ws
  
endMain:
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
End Sub


Sub DoIt(rA As Range, sR As String, wD As Worksheet)
  Dim i As Integer, a(1 To 100, 1 To 3)
  
  For i = 1 To 100
    a(i, 1) = Range("Hundred")(i, 1)
    a(i, 2) = Range("Hundred")(i, 2)
    a(i, 3) = wD.Range("Fed_A_" & Range("Run")(i, 2).Text)
  Next i
  rA.Resize(100, 3).Value = a
  
  wD.Range(wD.Cells(rA.Row, "D"), wD.Cells(rA.Row + 99, "EE")) = vntData_R
  Range(rA, Cells(rA.Row + 99, "D")).Name = sR & Range("Run").Text & "_Mapped"
End Sub

[ytjjjtyj]

Ahh, I see! Thank you!

[p45cal]

…but I don't.

[ytjjjtyj]

I think it worked because I initially wasn't resizing my range and now I am