Todays date minus 1

[webels]

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

 

[Spencer101]

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 t
amend the one already in column H

 

[webels]

Eddie,

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

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

Eddie

 

[Don Guillett]

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

 

[webels]

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

 

[eidde1]

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