VBA Code Help - Need to add a line for each missing date and copy data from cells below

0 votes

I want the new line to transfer the data from the cells below for columns A to c and E to L. I have the code below to add a line for each missing date and then update column D with the missing date.

Currently, I end up with a worksheet like this:

enter image description here

The VBA code is:

Dim wks As Worksheet
Set wks = Worksheets("NAV_REPORT")

Dim lastRow As Long
lastRow = Range("D2").End(xlDown).Row

For i = lastRow To 2 Step -1
    curcell = wks.Cells(i, 4).Value
    prevcell = wks.Cells(i - 1, 4).Value

    Do Until curcell - 1 <= prevcell
        wks.Rows(i).Insert xlShiftDown

        curcell = wks.Cells(i + 1, 4) - 1
        wks.Cells(i, 4).Value = curcell
    Loop
Next i

Can someone help me in updating the above code to fill up the cells below?

Jan 20 in Others by Kithuzzz
• 34,760 points
78 views

1 answer to this question.

0 votes

All you have to do is copy the row above and insert it if you want the added row to be identical to the row above:

wks.Rows(i).Copy
wks.Rows(i).Insert xlShiftDown

This will insert the exact data on the previous row and the rest of your code will amend the date as necessary.

answered Jan 20 by narikkadan
• 59,740 points

Related Questions In Others

0 votes
2 answers
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8 in Others by narikkadan
• 59,740 points
342 views
0 votes
1 answer
0 votes
1 answer

Looping through a folder with Excel files and scraping date from selected cells usin VBA

You record the outcome of your comparison ...READ MORE

answered Feb 16 in Others by narikkadan
• 59,740 points
55 views
0 votes
1 answer

How To Copy/Cut Row of Data Based on TRUE/FALSE Condition [Excel VBA]

Solution Loop through the rows on the Price ...READ MORE

answered Feb 4 in Others by narikkadan
• 59,740 points
84 views
0 votes
1 answer

Confused on VBA copy destination

Try this: Sub so75496418AddChart() Dim rng As Range Dim strTypeName ...READ MORE

answered Feb 20 in Others by Kithuzzz
• 34,760 points
70 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
575 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,551 views
0 votes
1 answer
0 votes
1 answer

Excel VBA - Need to get a specific line from TextBox and send to an specific Cell

Moving your string into an array first ...READ MORE

answered Feb 2 in Others by narikkadan
• 59,740 points
89 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP