Date Column - Loop through each row in a column and put Forward Date in same row different columns

[ANALYSTBANK]

I have a Sheet wherein, I have DATE data in Column B, starting from A3, and want to populate DATE data in other columns, until row (cell) in ColumnA is found to be empty


Row 1
Row 2 ColA, ColB, ColC, ColD
Row 3 23-Aug-11, A3 + 3 days, A3+5 Days, A3+8 days
Row 4 30-Aug-11,
Row 5 05-Sep-11,
Row 6 22-Sep-11,
Row 7 3-Oct-11,
Row8 14-Oct-11,

VBA Macro Code should fill all row (cells) in Column B with some forward date count as shown above till Column N or Column V

 

[Joe4]

I am not quite clear where column N or V comes into play, but here is some code that should give you the methodology you need. If it doesn't already do exactly what you want, most of the "nuts and bolts" are there that it should work with a few minor adjustments.

Sub MyCopy()


    Dim myLastRow As Long
    Dim myLastCol As Long
    Dim myCol As Long
    
'   Find last row with data in column A
    myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Find last column with data in row 3
    myLastCol = Cells(3, Columns.Count).End(xlToLeft).Column
    
'   Copy formulas in row 3 all the way across for all rows and columns
    For myCol = 2 To myLastCol
        Cells(3, myCol).Copy
        Range(Cells(4, myCol), Cells(myLastRow, myCol)).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    Next myCol
        
End Sub

 

[ANALYSTBANK]

@Joe4,

Thanks , but this does not solve my issue.

I'm novice to VBA code, and logically tweaked the code, and also tried as u gave, but it does not give the desired result.

Kindly look at my attached workbook, I want output in columnC,D,E, based on some days addition to source date taken from respective rows in Column B

 

[Joe4]

Thanks , but this does not solve my issue.

If the code does not do what you want, then I fear you have not described your problem in enough detail.

The code as I wrote it, will work on the example you posted in your first post. For however many columns you have populated in row 3 starting in column B, it will copy those formulas down for all rows.
So, the formula in cell B3 is being copied down column B for all rows,
the formula in cell C3 is being copied down column C for all rows,
the formula in cell D3 is being copied down column D for all rows.

If this is not what you want, you need to then explain exactly how these values are to be calculated.

As you have probably noted, you cannot attach files on this site. However, you can attach screen images using the tools mentioned here: http://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[ANALYSTBANK]

Apology for any mis-communication at my end.


See,
Worksheet 1
1) I have Input Column say, Column B (RANGE - B3:B20) which has only DATE Data (actual file has over 500+ rows). This column has DATE data already entered.


I want macro to
2) Select the entire range in ColumnB (macro should stop where there is blank cell in Column B - Using ranged concept, and stop where blank cell is found in ColumnB)


3) Now in Column C [ starting from cell c3, same row corresponding to Cell B3] I want macro to calculate and store date in dd-mmm-yy format which is result of B3+5 (5 days forward), this should be done for each row in Column C corresponding to each cell in Column B


4) Same as in 3) above, Column D [starting from cell D3] should have date value which is B3 + 15 days forward, and so on in all rows in ColumnD


5) Then in Column E [starting from cell E3] should have date value which is B3 + 20 days forward, and so on in all rows in ColumnE


For your ready reference, I give the image wherein Input and Output result is shown.

 

[Joe4]

The code I wrote for you does essentially that. The are only a few minor differences:
- You originally said that the populated column populated was column A, not column B. That is a simple change.
- My code looked for the last populated row, instead of the first blank. Often times, these are the same, but if you may have a blank in your original date column, and then other stuff under it that you do not want this logic applied to, a minor modification is required.
- My code wrote would work for however many columns you have populated in row 3. But we can easily limit it to columns C-E, if you have stuff in columns past column E that you do not want this logic applied to.

So the code with those minor changes would look like:

Sub MyCopy()


    Dim myLastRow As Long
    Dim myCol As Long
    
'   Find first blank row in column B, after row 3
    myLastRow = Range("B3").End(xlDown).Row
    
'   Copy formulas in cells C3 - E3 down for all rows up to first blank row
    For myCol = 3 To 5
        Cells(3, myCol).Copy
        Range(Cells(4, myCol), Cells(myLastRow, myCol)).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    Next myCol
        
End Sub

 

[ANALYSTBANK]

Sir, (@Joe). Thanks very much, this works, but

Result in Column C-E is blank, perhaps, above code copies and pastes only the 'Formula', whereas ColB (Input or source column) has date value which is manually entered, and hence result is BLANK. I want some forward date value in corresponding row cells through Column C-E.

For Example, In above snapshot Cell B3 has date value of 14-Jan-10, so i want

19-Dec-10 in Cell C3 which is + 5 days from date value in Cell B3,
29-Dec-10 in Cell D3 which is + 15 days from date value in Cell B3,
03-Jan-11 in Cell E3 which is + 20 days from date value in Cell B3,

Sorry, but am completely novice to VBA and i guess, through your help, am very close to my answer.

 

[ANALYSTBANK]

I am not sure, how to incorporate Macro to Calculate Date Difference in your above code. Instead of date difference, how to add the fixed days from a source date in another columns.

 

[Joe4]

The code which I wrote, based on the example you originally posted in your first post assumes that you have the formulas for the first row (row 3) already filled out, so it copies them down. If that is not the case, then instead of copying, lets enter the formulas directly.

Sub MyPopulateFormulas()


    Dim myLastRow As Long
    
'   Find first blank row in column B, after row 3
    myLastRow = Range("B3").End(xlDown).Row
    
'   Column C entries should add 5 days to column B entry
    Range(Cells(3, "C"), Cells(myLastRow, "C")).FormulaR1C1 = "=RC[-1]+5"
    
'   Column D entries should add 15 days to column B entry
    Range(Cells(3, "D"), Cells(myLastRow, "D")).FormulaR1C1 = "=RC[-2]+15"
    
'   Column E entries should add 20 days to column B entry
    Range(Cells(3, "E"), Cells(myLastRow, "E")).FormulaR1C1 = "=RC[-3]+20"
        
End Sub

 

[ANALYSTBANK]

Awesome, Thanks a Ton