Set row number for Date function in while loop?

[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

 

[Bob Phillips]

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))
Range("BF" & CStr(LSearchRow)).Value = _
"=DATE(YEAR(AI" & LSearchRow & ")+5,MONTH(AI" & _
LSearchRow & "),DAY(AI" & LSearchRow & "))"

'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



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

Not 100% sure I picked the right variable, but you are looking for this
construction (where the variable is concatenated in and among the fixed text
part of your formula...

Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Range("BF" & _
CStr(LSearchRow)).Value = "=DATE(YEAR(AI" & LSearchRow & _
")+5,MONTH(AI" & LSearchRow & "),DAY(AI" & LSearchRow & "))"

Rick

 

[Rick Rothstein \(MVP - VB\)]

Damn... posted incorrectly. Sorry.

Rick

Not 100% sure I picked the right variable, but you are looking for this
construction (where the variable is concatenated in and among the fixed
text part of your formula...

Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Range("BF" & _
CStr(LSearchRow)).Value = "=DATE(YEAR(AI" & LSearchRow & _
")+5,MONTH(AI" & LSearchRow & "),DAY(AI" & LSearchRow & "))"

Rick

 

[Rick Rothstein \(MVP - VB\)]

Not 100% sure I picked the right variable, but you are looking for this
construction (where the variable is concatenated in and among the fixed text
part of your formula...

Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Range("BF" & _
CStr(LSearchRow)).Value = "=DATE(YEAR(AI" & LSearchRow & _
")+5,MONTH(AI" & LSearchRow & "),DAY(AI" & LSearchRow & "))"

Rick