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
• 27,740 points
27 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
• 51,240 points

Related Questions In Others

0 votes
2 answers
0 votes
1 answer
0 votes
1 answer

VBA Code to Populate Balance Sheet from Trial Balance Data

It appears that what you need to ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 51,240 points
188 views
0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 51,240 points
215 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
522 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,382 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
110 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
274 views
0 votes
1 answer
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
• 51,240 points
74 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