Last month I posted regarding an issue I had encountered with <a
href="http://groups.google.com/group/
microsoft.public.excel.programming/browse_thread/thread/
299627eb5d36ff66/12ae5022656f1dfb?
hl=en&lnk=raot#12ae5022656f1dfb"date behavior changing</a in my VBA
code. I was able to resolve that issue but now I want to go to the
next level.

I understand from various guides and books that Functions cannot alter
any cell other than the one in which they are called. So the function
I created allows me to Flag a row as containing invalid information.
But I would like to make a subroutine with the same functionality, but
with additional code to take action to resolve certain flagged errors,
rather than making the user fix them manually.

Sometimes its the simplest concepts that really seem to throw me.
With the following code, assume we're working with a variable sized
data set, which is currently occupying the area A2:B100 where column A
is InvDate and column B is DepDate. I want to do the following:

'Insertion Point for new Automatic Date Value Correction Sequence
'Primary Objectives:
'#1. VERIFY THE PRESENCE OF VALUE
' If IsBlank(DepDate) = True Then
' Select Case IsBlank(InvDate)
' Case True
' InvDate = DateValue(Today()).Value
' DepDate = DateValue(Today()).Value
' Case False
' DepDate = DateValue(Today()).Value
' End Select
' End If
'
'#2 VERIFY THE PRESENCE OF DATE FORMAT
' If IsDate(DepDate) = False Then
' Select Case IsDate(InvDate)
' Case True
' DepDate = InvDate
' Case False
' InvDate = DateValue(Today()).Value
' DepDate = DateValue(Today()).Value
' End Select
' End If
'
'#3 CHECK FOR FUTURE DATED TRANSACTIONS
' If DateValue(DepDate) Now
' Select Case DateValue(InvDate)
' Case <= Now
' DepDate = InvDate
' Case Now
' DepDate = DateValue(Today()).Value
' End Select
' End If

As you can see I basically cut the code right out of my functioning
macro. The idifficulty I'm encountering is that my macro is always
referencing the cell from which it is being called, so I have a
relative starting point. In a subroutine the reference is not fixed,
so I'm having a hard time declaring the parameters to constrain my
subroutine. See the awful not-working code below:

' Dim RowCounter As Integer, CellValue As String
' Dim FirstRow As Integer, LastRow As Integer
' Dim InvDateValue As Date, DepDateValue As Date
'
' Let FirstRow = 2
' Let LastRow = Cells(2, 1).End(xlDown).Row
'
' For RowCounter = FirstRow To LastRow
' With Workbooks("Travel.xls").Worksheets(1).Range("H" &
RowCounter)
' If IsDate(InvDateValue) = False Then
' Let InvDateValue =
CellValue.SpecialCells(xlCellTypeVisible).FormulaR 1C1 =
"=DATEVALUE(TODAY())"
' If DateValue(DepDate) Now Then
' Let Result = "Future Date"
' If IsMissing(InvDate) = False And DepDate = "" Then
' Let Result = "Use InvDate"
' End If
'
'
' End With
' Next RowCounter

Any recommendations about how to best accomplish these three
objectives listed above? I'm so used to making functions that I can't
seem to think outside the self-referencing box on this one. Any help/
advice is greatly appreciated.

Damian