Dated Fields

S

stck2mlon

Is it possible to have a row with a date in one cell and a dollar amoun
in another, have the dollar amount move one cell to the right once
certain time has passed?

Example:
Once 30 days has elapsed, have $10.00 move from column L to
 
D

Dave Peterson

Yes.

Option Explicit
Sub testme()
With Worksheets("sheet1")
If .Range("a1").Value < Date - 30 Then
If .Range("M1").Value = "" Then
.Range("M1").Value = .Range("L1").Value
.Range("l1").ClearContents
End If
End If
End With
End Sub


You could even go through a range of cells:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range


With Worksheets("sheet1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells
With myCell
If IsDate(.Value) Then
If .Value < Date - 30 Then
If .Cells(.Row, "M").Value = "" Then
.Cells(.Row, "M").Value = .Cells(.Row, "L").Value
.Cells(.Row, "L").ClearContents
End If
End If
End If
End With
Next myCell
End With
End Sub

(I added one more check--isdate().)
 
S

stck2mlon

This is great...What is a good way to test this and where should it g
in my VBA or someplace else entirely
 
S

stck2mlon

Dave,

Would this look for the date in column G, row 3 and move amount i
column L to coulmn N? It isn't giving an error, nor is it working. An
suggestions?

Sub UserForm_initialize()
Dim myCell As Range
Dim myRng As Range
With Worksheets("Active Collection")
Set myRng = .Range("g3", .Cells(.Rows.Count, "G").End(xlUp))
For Each myCell In myRng.Cells
With myCell
If IsDate(.Value) Then
If .Value < Date - 30 Then
If .Cells(.Row, "M").Value = "" Then
.Cells(.Row, "M").Value = .Cells(.Row
"L").Value
.Cells(.Row, "L").ClearContents
End If
End If
End If
End With
Next myCell
End With
End Su
 
D

Dave Peterson

Sorry, I missed your followups.

But I'd put the code into a general module and run it via:
tools|macro|macros...
(or plop a button from the forms toolbar onto the worksheet and assign this
macro to the button.)

but I think you got hit by the "With/End with" structure (and I'm not sure if
leading dots were included in your code. Sometimes posting to the newsgroups
via those web based interfaces seems to cause problems with formatting and first
characters in the line.)

Anyway:
this portion looks like trouble under some circumstances (and to make matters
worse, you copied from my post! I didn't notice that until now. Darn!)

With myCell
If IsDate(.Value) Then
If .Value < Date - 30 Then
If .Cells(.Row, "M").Value = "" Then
Cells(.Row, "M").Value = .Cells(.Row,"L").Value
Cells(.Row, "L").ClearContents
End If
End If
End If
End With


This portion ".cells(.row,"M").value" refers to the previous With/End with. The
previous With in this case is with myCell.

Try this short test.

sub OhOh()
Dim mycell as range
set mycell = activesheet.range("c3")
with mycell
msgbox .cells(.row,"M").address
end with
end sub

I get $o$5 in the message box--not what I intended.

That example was equivalent to:
msgbox activesheet.range("c3").cells(3,"M").address
down 2 rows (.cells(x,y) is one's based).
and over 12 columns.

I could have removed that leading dot--but that may not be safe either:

With myCell
If IsDate(.Value) Then
If .Value < Date - 30 Then
If Cells(.Row, "M").Value = "" Then
Cells(.Row, "M").Value = Cells(.Row,"L").Value
Cells(.Row, "L").ClearContents
End If
End If
End If
End With

Without a dot (.cells), this is called an unqualified range. If the code is in
a general module, it'll refer to the active worksheet--and that might not always
be "Active Collection".

So it's better to qualify those cells with something like:

Option Explicit
Sub testme01()
Dim myCell As Range
Dim myRng As Range
With Worksheets("Active Collection")
Set myRng = .Range("g3", .Cells(.Rows.Count, "G").End(xlUp))
For Each myCell In myRng.Cells
If IsDate(myCell.Value) Then
If myCell.Value < Date - 30 Then
If .Cells(myCell.Row, "M").Value = "" Then
.Cells(myCell.Row, "M").Value _
= .Cells(myCell.Row, "L").Value
.Cells(myCell.Row, "L").ClearContents
End If
End If
End If
Next myCell
End With
End Sub

The .cells() stuff refers back to the previous with/end with. And now it refers
to that "active collection" worksheet.

Again, sorry about the bad code.
 
Top