CODEWITHSUNDEEP
excelvba
Ahmed Dildar
Ahmed Dildar

Reputation: 53

Nested For Loop in Excel VBA

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

Upvotes: 0

Views: 14835

Answers (1)

ArchiCAT
ArchiCAT

Reputation: 119

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

Upvotes: 1

Related Questions