PDA

View Full Version : [SOLVED] HELP NEED TO MAKE CODE GENERIC LOOPING THROUGH CELLS TO IMPORT DATA



ytjjjtyj
07-31-2019, 01:01 PM
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("A307:DD406").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("A408:DD507").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("A509:DD608").Name = "pop_" & Run.text & "_Mapped"


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

p45cal
07-31-2019, 03:07 PM
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
07-31-2019, 04:02 PM
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
08-01-2019, 05:11 AM
Ahh, I see! Thank you!

p45cal
08-01-2019, 09:24 AM
…but I don't.

ytjjjtyj
08-02-2019, 12:43 PM
I think it worked because I initially wasn't resizing my range and now I am