Nested For Loop in Excel VBA

Question

The following code is working for only when c = 2 ,however I want it to work for other values as well. Below is the Excel table on which I want to run it.

Date    PickupCost  StorageCost DeliveryCost
1/1/2017    140       35          0
1/8/2017    80        20          0
1/10/2017   0          0         149
1/30/2017   35         8          0

I want to fill data of each date missing but only the value at column 3 (StorageCost) needs to be same in other missing date values as previous day's StorageCost value.

Dim j, p, w, c As Long
Dim date1, date2 As Date
j = Cells(Rows.Count, 1).End(xlUp).Row
w = 2
For c = w To j
   date1 = Range("A" & w).Value
   date2 = Range("A" & (w + 1)).Value
   p = DateDiff("d", date1, date2)
   For w = 2 To p
       Range("A" & (c + 1)).EntireRow.Insert
       ActiveSheet.Cells(c + 1, 1).Value = (ActiveSheet.Cells(c, 1).Value) + 1
       ActiveSheet.Cells(c + 1, 2).Value = 0
       ActiveSheet.Cells(c + 1, 3).Value = ActiveSheet.Cells(c, 3).Value
       ActiveSheet.Cells(c + 1, 4).Value = 0
       c = c + 1
   Next w
   w = w + 1
   ActiveSheet.Range("A1").Select
   j = Cells(Rows.Count, 1).End(xlUp).Row
Next c

What is c supposed to be, and where are you initialising it? — David Glickman
– David Glickman, Commented Mar 14, 2017 at 18:05
c is a counter based on which number of times loop needs to run so that gaps between each combination of date at a row and date at previous row needs to be calculated. It is initialized on basis of value of w. — Ahmed Dildar
– Ahmed Dildar, Commented Mar 14, 2017 at 18:11
Please explain what you're trying to do better - I don't understand. Is "data missing" the 0 values? Can you post an example of what you want the results to be? — user1274820
– user1274820, Commented Mar 14, 2017 at 18:29
Okay I think I get it by running the code - you want to fill in the dates between each date, but only storage cost stays the same? the other values change to 0? — user1274820
– user1274820, Commented Mar 14, 2017 at 18:32

ArchiCAT · Accepted Answer · 2017-03-14 18:38:22Z

1

Your main problem is that once you defined your For c = w To j loop then it will only run until it reaches value j had when you defined the for loop. If you want an endpoint to the loop that adapts to the runtime changing number of rows, you should use a Do Until loop, like this:

Dim p As Long
Dim c, w As Integer
Dim date1, date2 As Date

c = 2
Do Until c = Cells(Rows.Count, 1).End(xlUp).Row
   date1 = Range("A" & c).Value
   date2 = Range("A" & (c + 1)).Value
   p = DateDiff("d", date1, date2)
   For w = c To c + p - 2
       Range("A" & (w + 1)).EntireRow.Insert
       ActiveSheet.Cells(w + 1, 1).Value = (ActiveSheet.Cells(c, 1).Value) + 1
       ActiveSheet.Cells(w + 1, 2).Value = 0
       ActiveSheet.Cells(w + 1, 3).Value = ActiveSheet.Cells(c, 3).Value
       ActiveSheet.Cells(w + 1, 4).Value = 0
       c = c + 1
   Next w
   c = c + 1
Loop

answered Mar 14, 2017 at 18:38

ArchiCAT

1191 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ahmed Dildar Over a year ago

thank you so much. It worked exactly according to my requirement.

Collectives™ on Stack Overflow

Nested For Loop in Excel VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related