Loop thru series to copy and paste with an offset using VBA code

LtCmdrData

New Member
Joined
Jan 24, 2018
Messages
36
I have some trip data in a subtotal form. I use this to calculate miles based on zip codes (columns E & H). I need to copy and paste the first Dest Postal code so it becomes the second Origin Postal code. I need it to move down the page doing this but start over with each new Lane ID (column A). This will allow me to calculate the in between miles. The results should look like the second image. Can someone help me with this task? Thank you.

LANE IDStop#Origin CityOrigin StateOrigin PostalDest CityDest StateDest Postal
1​
1​
37202​
21001
1​
2​
37202​
14227
1 Count
2​
10​
1​
37202​
24210
10​
2​
37202​
30080
10​
3​
37202​
36201
10​
4​
37202​
36201
10​
5​
37202​
36801
10​
6​
37202​
32033
10 Count
6​
100​
1​
37202​
85193
100​
2​
37202​
90670
100​
3​
37202​
95206
100​
4​
37202​
97220
100 Count
4​
101​
1​
37202​
98531
101​
2​
37202​
92618
101 Count
2​
102​
1​
37202​
98531
102​
2​
37202​
97220
102​
3​
37202​
91761
102 Count
3​
103​
1​
37202​
98531
103​
2​
37202​
97071
103 Count
2​
104​
1​
37202​
98531
104 Count
1​
105​
1​
37202​
85226
105​
2​
37202​
85043
105​
3​
37202​
95206
105​
4​
37202​
95206
105 Count
4​
106​
1​
37202​
50049
106​
2​
37202​
61081
106 Count
2​
107​
1​
37202​
50049
107 Count
1​
108​
1​
37202​
14227
108​
2​
37202​
21001
108 Count
2​

LANE IDStop#Origin CityOrigin StateOrigin PostalDest CityDest StateDest Postal
1​
1​
3720221001
1​
2​
2100114227
1 Count
2​
10​
1​
3720224210
10​
2​
2421030080
10​
3​
3008036201
10​
4​
3620136201
10​
5​
3620136801
10​
6​
3680132033
10 Count
6​
100​
1​
3720285193
100​
2​
8519390670
100​
3​
9067095206
100​
4​
9520697220
100 Count
4​
101​
1​
3720298531
101​
2​
9853192618
101 Count
2​
102​
1​
3720298531
102​
2​
9853197220
102​
3​
9722091761
102 Count
3​
103​
1​
3720298531
103​
2​
9853197071
103 Count
2​
104​
1​
3720298531
104 Count
1​
105​
1​
3720285226
105​
2​
8522685043
105​
3​
8504395206
105​
4​
9520695206
105 Count
4​
106​
1​
3720250049
106​
2​
5004961081
106 Count
2​
107​
1​
3720250049
107 Count
1​
108​
1​
3720214227
108​
2​
1422721001
108 Count
2​
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,559
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub LtCmdrData()
   Dim Rng As Range, Cl As Range
   
   For Each Rng In Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlConstants).Areas
      If Rng.Count > 1 Then Rng.Offset(1).Resize(Rng.Count - 1).Value = Rng.Offset(, 3).Value
   Next Rng
End Sub
 
Solution

LtCmdrData

New Member
Joined
Jan 24, 2018
Messages
36
How about
VBA Code:
Sub LtCmdrData()
   Dim Rng As Range, Cl As Range
  
   For Each Rng In Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlConstants).Areas
      If Rng.Count > 1 Then Rng.Offset(1).Resize(Rng.Count - 1).Value = Rng.Offset(, 3).Value
   Next Rng
End Sub
Thank you Fluff! I knew there had to be a solution and once again you came thru. I still have so much to learn about VBA.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,559
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,563
Messages
5,637,077
Members
416,956
Latest member
mitzhaki

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
Top