Todays date minus 1
 

Hi
Is it possible to search a column in Excel (Col H). Find any
containing todays() date and subtract 1 from these dates only? I have
searched for something like this but no luck.

Many thanks
Eddie

Eddie,

Do you mean to put the date (today minus 1) in another column or to amend the one already in column H?

Todays date minus 1
 

On Apr 18, 8:22*am, Spencer101
wrote:
webels;1600912 Wrote:

Hi
Is it possible to search a column in Excel (Col H). Find any
containing todays() date and subtract 1 from these dates only? I have
searched for something like this but no luck.

Many thanks
Eddie

Eddie,

Do you mean to put the date (today minus 1) in another column or to
amend the one already in column H?

--
Spencer101

Hi Spencer101
The code would hopefully amend the one already in column H.

Eddie

Todays date minus 1
 

On Tuesday, April 17, 2012 5:42:10 PM UTC-5, webels wrote:
Hi
Is it possible to search a column in Excel (Col H). Find any
containing todays() date and subtract 1 from these dates only? I have
searched for something like this but no luck.

Many thanks
Eddie
code like this

Sub findpart()
With Worksheets("yoursheetname").Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).Row)
Set C = .Find(date, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address
Do
c.value=date-1
'or
'c.offset(,1)=date-1

Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address < firstAddress
End If
End With
End Sub

Todays date minus 1
 

On Apr 18, 1:05*pm, Don Guillett wrote:
On Tuesday, April 17, 2012 5:42:10 PM UTC-5, webels wrote:
Hi
Is it possible to search a column in Excel (Col H). Find any
containing todays() date and subtract 1 from these dates only? I have
searched for something like this but no luck.

Many thanks
Eddie

code like this

Sub findpart()
With Worksheets("yoursheetname").Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).Row)
* * Set C = .Find(date, LookIn:=xlValues)
* * If Not C Is Nothing Then
* * * * firstAddress = C.Address
* * * * Do
c.value=date-1
'or
'c.offset(,1)=date-1

* *Set C = .FindNext(C)
* * * * Loop While Not C Is Nothing And C.Address < firstAddress
* *End If
End With
End Sub

Hi Don
Thanks for this code

it works but I get a debug on the following line of the code

Loop While Not C Is Nothing And C.Address < firstAddress

so if I place the following on column A

16/04/2012
16/04/2012
16/04/2012
16/04/2012
18/04/2012
18/04/2012
19/04/2012
18/04/2012
18/04/2012

I get

16/04/2012
16/04/2012
16/04/2012
16/04/2012
18/04/2012
18/04/2012
18/04/2012
18/04/2012
18/04/2012

followed by debug when i run code. It subtracts 1 day from the 19th to
be replaced by 18/04/2012.

Hope this makes sense
Thanks as always for you help

Eddie

Todays date minus 1
 

On Thursday, April 19, 2012 3:01:10 PM UTC+1, webels wrote:
On Apr 18, 1:05*pm, Don Guillett wrote:
On Tuesday, April 17, 2012 5:42:10 PM UTC-5, webels wrote:
Hi
Is it possible to search a column in Excel (Col H). Find any
containing todays() date and subtract 1 from these dates only? I have
searched for something like this but no luck.

Many thanks
Eddie

code like this

Sub findpart()
With Worksheets("yoursheetname").Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).Row)
* * Set C = .Find(date, LookIn:=xlValues)
* * If Not C Is Nothing Then
* * * * firstAddress = C.Address
* * * * Do
c.value=date-1
'or
'c.offset(,1)=date-1

* *Set C = .FindNext(C)
* * * * Loop While Not C Is Nothing And C.Address < firstAddress
* *End If
End With
End Sub

Hi Don
Thanks for this code

it works but I get a debug on the following line of the code

Loop While Not C Is Nothing And C.Address < firstAddress

so if I place the following on column A

16/04/2012
16/04/2012
16/04/2012
16/04/2012
18/04/2012
18/04/2012
19/04/2012
18/04/2012
18/04/2012

I get

16/04/2012
16/04/2012
16/04/2012
16/04/2012
18/04/2012
18/04/2012
18/04/2012
18/04/2012
18/04/2012

followed by debug when i run code. It subtracts 1 day from the 19th to
be replaced by 18/04/2012.

Hope this makes sense
Thanks as always for you help

Eddie

Got this sorted changed the code Don gave me slightly and it works

Sub findpart()
With Worksheets("Sheet1").Range("h1:h" & Cells(Rows.Count, "h").End(xlUp).Row)
Set c = .Find(Date, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Value = Date - 1
'or
'c.offset(,1)=date-1

Set c = .FindNext(c)

If c Is Nothing Then Exit Do
Loop While c.Address < firstaddress


End If
End With
End Sub


Thanks Don for getting me going the right direction

Much appreciated

Eddie