Create a Loop that adds DateSerial value from AA3 to LastRow in VBA-CodePudding

Total NOOB here. Tyring to create a loop that loops through cells X3, Y3, Z3 to create DateSerial Value in AA3, then repeats that action until AA LastRow incrementing XYZ by 1 cell.

I know it is incomplete. I've stared at it too long without any progress. So far I have,

Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim MyDate
Dim oRange As Range, cell As Range


x = CInt(Range("X3").Value)
y = CInt(Range("Y3").Value)
z = CInt(Range("Z3").Value)

MyDate = DateSerial(z, x, y)

    Set oRange = Range("AA3:AA" & LastRow)
    Set cell = Range("AA3")
    For Each cell In oRange
    cell.Value = MyDate
    cell.Offset(-3).Value
    cell.Offset(-2).Value
    cell.Offset(-1).Value
    
Next cell

CodePudding user response：

To make a simple loop that moves down the worksheet row by row, I would suggest using a For Loop , where the loop index is the row #. Then you can reference cells in that row like MyWorksheet.Cells( RowNum, ColNum). In your case, you'd want something like:

Sub Example()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim LastRow As Long
    LastRow = ws.Range("X:X").Cells(ws.Rows.Count).End(xlUp).Row
    
    Dim r As Long
    For r = 3 To LastRow
        With ws.Cells(r, "AA")
            .NumberFormat = "YYYY-MM-DD" 'Change as desired
            
            .Value = DateSerial( _
                         Year:=ws.Cells(r, "Z").Value, _
                         Month:=ws.Cells(r, "X").Value, _
                         Day:=ws.Cells(r, "Y").Value _
                     )
        End With
    Next
End Sub

You could achieve a similar effect with a For Each loop where the loop element is the worksheet row:

Sub Example()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim LastRow As Long
    LastRow = ws.Range("X:X").Cells(ws.Rows.Count).End(xlUp).Row
    
    Dim Row As Range
    For Each Row In ws.Range("3:" & LastRow).Rows
        With Row.Columns("AA")
            .NumberFormat = "YYYY-MM-DD" 'Change as desired
            
            .Value = DateSerial( _
                         Year:=Row.Columns("Z").Value, _
                         Month:=Row.Columns("X").Value, _
                         Day:=Row.Columns("Y").Value _
                     )
        End With
    Next
End Sub

You could also avoid the need for a loop (or VBA) altogether by using Excel formulas.

The formula would be =DATEVALUE(Z3&"-"&X3&"-"&Y3)

And you could drag that formula down the column manually, or input it using VBA like:

Sub Example()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim LastRow As Long
    LastRow = ws.Range("X:X").Cells(ws.Rows.Count).End(xlUp).Row

    With Range("AA3:AA" & LastRow)
        .NumberFormat = "YYYY-MM-DD" 'Change as desired
        .Formula = "=DATEVALUE(Z3&""-""&X3&""-""&Y3)"
    End With
End Sub

CodePudding user response：

1000 roads lead to Rome ;-)

Option Explicit

Sub CreateSerialDate()

Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim lastRow As Integer
Dim oRange As Range, myCell As Range

'Column Z has all the years
lastRow = ActiveSheet.Range("Z3").CurrentRegion.Rows.Count   1  'Nr of lines - header   2)

Set oRange = Range("AA3:AA" & lastRow)
For Each myCell In oRange

    x = CInt(myCell.Offset(0, -3).Value)
    y = CInt(myCell.Offset(0, -2).Value)
    z = CInt(myCell.Offset(0, -1).Value)
    
    myCell.Value = DateSerial(z, x, y)
    
Next myCell

End Sub