Need to increment the date value in rows based on the coloumn value

pratheesh

New Member
Joined
Dec 16, 2020
Messages
12
Office Version
  1. 365
  2. 2011
  3. 2010
Platform
  1. Windows
Hi,
I wanted to increment the date value based on the column value

My input:
ArrivalNights
01/01/2016​
2​
01/01/2016​
2​
01/01/2016​
6​

Output:

ArrivalNights
1/01/2016​
2​
2/01/2016​
2​
1/01/2016​
6​
2/01/2016​
6​
3/01/2016​
6​
4/01/2016​
6​
5/01/2016​
6​
6/01/2016​
6​


My "Arrival" date values should be increment based on the "Nights" column vales

Please help me with it

thanks in advance :)
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Welcome to the Board!

Your "input" table shows 2 records with 2 night stays starting on 1/1/2016, but your "ouput" table only shows one.
Was this a mistake, or am I not understanding how you want this to work?

Please also indicate exactly where this "Input" range resides (sheet name, range addresses), and where you want this "Output" range to be written to (sheet name, range addresses).
 
Upvote 0
Welcome to the Board!

Your "input" table shows 2 records with 2 night stays starting on 1/1/2016, but your "ouput" table only shows one.
Was this a mistake, or am I not understanding how you want this to work?

Please also indicate exactly where this "Input" range resides (sheet name, range addresses), and where you want this "Output" range to be written to (sheet name, range addresses).
Sorry, that's a mistake. the input is as below

input:

ArrivalNights
01/01/2016​
2​
01/01/2016​
6​
 
Upvote 0
Welcome to the Board!

Your "input" table shows 2 records with 2 night stays starting on 1/1/2016, but your "ouput" table only shows one.
Was this a mistake, or am I not understanding how you want this to work?

Please also indicate exactly where this "Input" range resides (sheet name, range addresses), and where you want this "Output" range to be written to (sheet name, range addresses).

input:

Sheet name = sheet1
Range address = A1:B3

Output

Sheet name = sheet2
Range address = A1
 
Upvote 0
Try this VBA code:
VBA Code:
Sub MyOutputSheet()

    Dim inSht As Worksheet
    Dim outSht As Worksheet
    Dim lr As Long
    Dim r As Long
    Dim dte As Date
    Dim nt As Long
    Dim n As Long
    Dim nr As Long
    
    Application.ScreenUpdating = False
    
'   Set workheet objects
    Set inSht = Sheets("Sheet1")
    Set outSht = Sheets("Sheet2")
    
'   Put headers on output sheet (may not be necessary if you already have these)
    outSht.Range("A1") = "Arrival"
    outSht.Range("B1") = "Nights"
    
'   Find last row with data in input sheet
    lr = inSht.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all records on input sheet, starting with row 2
    For r = 2 To lr
'       Get Arrival Date and Nights from row
        dte = inSht.Cells(r, "A")
        nt = inSht.Cells(r, "B")
'       Loop through all nts
        For n = 1 To nt
'           Find next available for on output sheet
            nr = outSht.Cells(Rows.Count, "A").End(xlUp).Row + 1
'           Populate values
            outSht.Cells(nr, "A") = dte
            outSht.Cells(nr, "B") = nt
'           Increment date by 1
            dte = dte + 1
        Next n
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!", vbOKOnly
        
End Sub
 
Upvote 0
Try this VBA code:
VBA Code:
Sub MyOutputSheet()

    Dim inSht As Worksheet
    Dim outSht As Worksheet
    Dim lr As Long
    Dim r As Long
    Dim dte As Date
    Dim nt As Long
    Dim n As Long
    Dim nr As Long
   
    Application.ScreenUpdating = False
   
'   Set workheet objects
    Set inSht = Sheets("Sheet1")
    Set outSht = Sheets("Sheet2")
   
'   Put headers on output sheet (may not be necessary if you already have these)
    outSht.Range("A1") = "Arrival"
    outSht.Range("B1") = "Nights"
   
'   Find last row with data in input sheet
    lr = inSht.Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all records on input sheet, starting with row 2
    For r = 2 To lr
'       Get Arrival Date and Nights from row
        dte = inSht.Cells(r, "A")
        nt = inSht.Cells(r, "B")
'       Loop through all nts
        For n = 1 To nt
'           Find next available for on output sheet
            nr = outSht.Cells(Rows.Count, "A").End(xlUp).Row + 1
'           Populate values
            outSht.Cells(nr, "A") = dte
            outSht.Cells(nr, "B") = nt
'           Increment date by 1
            dte = dte + 1
        Next n
    Next r
   
    Application.ScreenUpdating = True
   
    MsgBox "Macro complete!", vbOKOnly
       
End Sub
Thank You so much ... You saved my time, I have struggled a lot on this. thanks again :)
 
Upvote 0
You are welcome!

If you notice, I add lots of comments to my code to try to explain what each step is doing, so you can follow along and learn.
If you have any particular questions about the code, feel free to post them here.
 
Upvote 0
You are welcome!

If you notice, I add lots of comments to my code to try to explain what each step is doing, so you can follow along and learn.
If you have any particular questions about the code, feel free to post them here.
Yep sure, I really appreciate that. thank you. :)
 
Upvote 0
You are welcome!

If you notice, I add lots of comments to my code to try to explain what each step is doing, so you can follow along and learn.
If you have any particular questions about the code, feel free to post them here.
Hi Joe,

I have a situation here. I need some additional help in that Vb code to duplicate the whole rows based on the column value and which included the "Arrival date" increment. please find the attached image file for more clarity over my query.

Thank you.
 

Attachments

  • Capture.PNG
    Capture.PNG
    66.4 KB · Views: 3
Upvote 0
It appears that you stumbled upon a very important lesson here that many people run into.
You don't want to oversimplify your question for the sake of posting it here. Otherwise, you may get a reply that answers your question, but doesn't really solve your problem (because your problem was really more complex than the question you posted!).

In order to help you better, I would like to be able to copy/paste your data to a spreadsheet on my side. However, I cannot do that with the pictures you posted.
But you can post excerpts of your data using this tool described here: XL2BB - Excel Range to BBCode

So if you can post a sampling of your actual data using that tool, I will be better able to assist you.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top