J
John
I am trying to loop through rows on a worksheet and update a cell using this
function =DATE(YEAR(AI2)+5,MONTH(AI2),DAY(AI2))
The loop function identifies which rows are to be update (not every row has
the DATE function inserted).
The part I can't work out is how to set the row number so it equals the
actual row number. i.e. where is has (AI2) , the 2 is for row 2 so that
number needs to be the actual row number.
Anyway here is the complete Sub:
#######################################################
Sub ChangeDate()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
On Error GoTo Err_Execute
Sheets("Modified").Select
'Start search in row 2
LSearchRow = 2
'Start copying data to row 2 in Working (row counter variable)
LCopyToRow = 2
While Len(Range("A" & CStr(LSearchRow)).Value) > 0
'If value in column BD = False
If (Range("BD" & CStr(LSearchRow)).Value = False) Then
' Update Column BF with Date from Column AI +5 years
'
' First row should have this with the row number matched to the
actual row number
' =DATE(YEAR(AI2)+5,MONTH(AI2),DAY(AI2))
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Range("BF" &
CStr(LSearchRow)).Value = "=DATE(YEAR(AI)+5,MONTH(AI),DAY(AI))"
'Move counter to next row
LCopyToRow = LCopyToRow + 1
'Go back to Sheet "Modified" to continue searching
Sheets("Modified").Select
End If
LSearchRow = LSearchRow + 1
Wend
MsgBox "All matching data has been copied."
Exit Sub
Err_Execute:
MsgBox "An error occurred."
End Sub
function =DATE(YEAR(AI2)+5,MONTH(AI2),DAY(AI2))
The loop function identifies which rows are to be update (not every row has
the DATE function inserted).
The part I can't work out is how to set the row number so it equals the
actual row number. i.e. where is has (AI2) , the 2 is for row 2 so that
number needs to be the actual row number.
Anyway here is the complete Sub:
#######################################################
Sub ChangeDate()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
On Error GoTo Err_Execute
Sheets("Modified").Select
'Start search in row 2
LSearchRow = 2
'Start copying data to row 2 in Working (row counter variable)
LCopyToRow = 2
While Len(Range("A" & CStr(LSearchRow)).Value) > 0
'If value in column BD = False
If (Range("BD" & CStr(LSearchRow)).Value = False) Then
' Update Column BF with Date from Column AI +5 years
'
' First row should have this with the row number matched to the
actual row number
' =DATE(YEAR(AI2)+5,MONTH(AI2),DAY(AI2))
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Range("BF" &
CStr(LSearchRow)).Value = "=DATE(YEAR(AI)+5,MONTH(AI),DAY(AI))"
'Move counter to next row
LCopyToRow = LCopyToRow + 1
'Go back to Sheet "Modified" to continue searching
Sheets("Modified").Select
End If
LSearchRow = LSearchRow + 1
Wend
MsgBox "All matching data has been copied."
Exit Sub
Err_Execute:
MsgBox "An error occurred."
End Sub