Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Set row number for Date function in while loop?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,069
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Function to find/display the date X number of days later? a0xbjzz Excel Worksheet Functions 1 October 26th 07 02:12 PM
Number of day - date function [email protected] Excel Discussion (Misc queries) 4 January 18th 06 08:55 PM
Number value based on date function Neil M Excel Worksheet Functions 2 January 12th 06 12:57 AM
Loop Function unable to loop Junior728 Excel Programming 1 July 28th 05 10:23 AM
For loop with non consecutive number zapatista66 Excel Programming 5 July 30th 04 01:36 PM


All times are GMT +1. The time now is 06:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement