Getting around a circular function??

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

I have a workbook that contains one data entry sheet and
several other sheets that contain forms that pull data
from the data entry sheet.

Example...new activations (one per row) are entered in the
first empty row at the bottom of the data sheet during the
month in no particular date order...in each row is a
billing date column (left empty at this time)...at the end
of each month an invoice is generated using a macro that
sorts the entire data sheet (descending by date of
activation)....this brings the most recent activations to
the top of the data sheet. The invoice sheet then tests
the billing date cell for each row on the data sheet...if
it's empty, it generates the necessary data on the
invoice. There may be as few as just one or as many as 30
rows involved and I only want to apply the current billing
date to that cell on the data entry sheet for those listed
on the invoice sheet and not change the existing dates
from previous billings.

I've tried to automatically fill in the billing date cell
on the data entry sheet using formulas, but this generates
a circular function. I suspect the solution is in the
coding of the macro but I am new to coding and do need
help with that. If it's possible to do what I've outlined
above, I would greatly appreciate your input.

Thanks in Advance,

Don
 
Hi Don
maybe you post the relevant part of your macro / the resulting formula
and some details about your used cells)
 
Hi Frank,

I've had some time to think about this and while I still
need the code, I think it'd be best running it as a
seperate macro, triggered after the invoice is proof read.

All would remain the same (i.e...billing dates empty)
until the second macro (the one below) is triggered. This
would allow for any corrections that might have to be made.

The second macro would do this:

Select the "Data" sheet, unprotect it, select and copy
a Today() cell, then select cell BD4 (the top billing date
cell), paste special/value and move to the next cell down
the BD column until it hits a cell with a date in it.
Protect the sheet and go to the opening page of the
workbook. Posted below is as much as I can do with this
one:

Sub BillingDateEntry()
'
' BillingDateEntry Macro
' Macro recorded 8/6/2004
'
Sheets("Data").Select
ActiveSheet.Unprotect
'Select and copy the "Today()" cell
Range("DV4").Select
Selection.Copy
ActiveWindow.SmallScroll ToRight:=-65
'Select top billing date cell
Range("BD4").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

'Area where If (or Next) loop that moves down one cell
'in the column, checks this cell for a date, if empty,
'fills it with PasteSpecial/Value, if a date exists,
'then it should quit the loop.


Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("Opening_Page").Select
End Sub

Thanks for the response, I really do appreciate the
knowledge you, and others, share on here,

Don
 
Hi again Frank,

Well, with a little manipulating of some code I picked up
on the forum here, I've come up with this (see below). It
works, but applies the current date to the entire defined
Range. I need it to stop when it hits a cell with a date
in it, or at least not change any cells with dates already
in them.

Sub BillingDateEntry()
Sheets("Data").Select
ActiveSheet.Unprotect
Range("DV4").Select
Selection.Copy
ActiveWindow.SmallScroll ToRight:=-65
Range("BD4:BD25").Select
Dim rCell As Range
For Each rCell In Selection
With rCell
If IsEmpty(.Value) Then _
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, Transpose:=False
End With
Next rCell
Application.CutCopyMode = False
Range("BD4").Select
ActiveSheet.Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("Sheet2").Select
End Sub




So close, but not quite there yet.......:)

Hope you have a great weekend,

Don
 
Back
Top