Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dated Fields
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dated Fields
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dated Fields
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().) "stck2mlon <" wrote: Is it possible to have a row with a date in one cell and a dollar amount in another, have the dollar amount move one cell to the right once a certain time has passed? Example: Once 30 days has elapsed, have $10.00 move from column L to M --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dated Fields
This is great...What is a good way to test this and where should it g
in my VBA or someplace else entirely -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dated Fields
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 -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dated Fields
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. "stck2mlon <" wrote: Dave, Would this look for the date in column G, row 3 and move amount in column L to coulmn N? It isn't giving an error, nor is it working. Any 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 Sub --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
colour change of dated box | New Users to Excel | |||
How do I set up 12 months/31 days each dated spreadsheets? | New Users to Excel | |||
A macro that will hide all but the last 20 dated rows. | Excel Discussion (Misc queries) | |||
Charting Dated Data | Charts and Charting in Excel | |||
print spreadsheet with each coming day dated on it for 2006. | Excel Discussion (Misc queries) |