ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Set row number for Date function in while loop? (https://www.excelbanter.com/excel-programming/413632-set-row-number-date-function-while-loop.html)

John
Set row number for Date function in while loop?
 
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
Set row number for Date function in while loop?
 
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)

"John" wrote in message
...
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

Rick Rothstein \(MVP - VB\)[_2242_]
Set row number for Date function in while loop?
 
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


"Bob Phillips" wrote in message
...
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)

"John" wrote in message
...
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

Rick Rothstein \(MVP - VB\)[_2243_]
Set row number for Date function in while loop?
 
Damn... posted incorrectly. Sorry.

Rick

"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"Bob Phillips" wrote in message
...
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)

"John" wrote in message
...
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

Rick Rothstein \(MVP - VB\)[_2244_]
Set row number for Date function in while loop?
 
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


"John" wrote in message
...
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


All times are GMT +1. The time now is 04:40 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com