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, 2023 in Others by Kithuzzz
• 38,010 points
274 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, 2023 by narikkadan
• 63,420 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, 2023 in Others by narikkadan
• 63,420 points
1,706 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, 2023 in Others by narikkadan
• 63,420 points
611 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, 2023 in Others by narikkadan
• 63,420 points
559 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, 2023 in Others by Kithuzzz
• 38,010 points
583 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
832 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
3,129 views
0 votes
1 answer

VBA code help - Add a line for each missing date with the start and end date defined in a cell

Try this: Sub FillDates() Dim ...READ MORE

answered Jan 24, 2023 in Others by narikkadan
• 63,420 points
579 views
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, 2023 in Others by narikkadan
• 63,420 points
451 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