Todays date minus 1

  • Thread starter Thread starter webels
  • Start date Start date
W

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
 
Eddie,

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

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

Eddie
 
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
Click to expand...
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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
Back
Top