Use VBA macro to populate formula result in 'next blank cell'

[Guest]

In a business spreadsheet, every time a predetermined condition is met I need
to record information on a "running" record. Ie. When condition 'A' is met,
copy data to; 'Sheet1!E {nextblank}. As there will be a few things I need to
record, I would like it all on the same row. Each data point will go in a
corresponding column.
*Note; the remainder of the macro will be constructed using the macro
recorder.

 

[Otto Moehrbach]

I take it that you want this to happen automatically when this
"predetermined condition is met ". You will have to specify when Excel
should check for this condition. Something like; When the contents of cell
XX change, check this and that and act if such and such is the case.
Or;
When the contents of this cell is larger than that cell, act.
Or
Etc.
Post back and provide details about this predetermined condition and how it
is met. HTH Otto

 

[Guest]

Ok Otto.

Here is what I am trying to accomplish. I am tracking rental units. They
can be rented for a term > daily to yearly. Everytime a unit is vacated, I
have a macro assigned to a command button to clear the unit information and
populate the "date unit vacated" cell (by referencing a cell containing
{'=today()'}). I also need this command button (same macro) to record some
key info about the former "renter" on a list. Items like; name, phone
number, rental period, etc. Later, this list could be used to reference on a
number of functions of analysis. In essence, it would track the history of
every unit for every day in the past once launched. Make sense? Any idea's?

Thanks,

Lonpuz

 

[Otto Moehrbach]

I'm not sure I understand what you want help with. You say you want to copy
some data, I don't know what data, and you want to paste it somewhere, I
don't know where. You did say that you want the information pasted in the
first blank row. I can help you with finding that row. Below is a snippet
of code for finding that row in Column A of sheet "The Sheet".
With Sheets("The Sheet")
Set Dest=.Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Post back if you need more. Otto

 

[Guest]

I think that might work Otto. here is some more "background" to clarify.
When a unit is vacated, I want to attatch a macro to a command button for the
user to clear the unit. The initial part of the macro is easy 4 me, as all I
need to do is tell it to erase some info in a few cells and apply today's
date in the "Unit Vacated" cell. The second part of the macro is the issue.
When the macro runs, I also want it to take a few pieces of information and
record in on a "running" list of unit's vacated. This way the unit history
is recorded, and I can easily identify how many "move in's and move out's"
the business had in; a day. week, quarter, etc... If you need a clearer
example Otto, you can e-mail me @ (e-mail address removed). I will forward a brief
sample of what I am trying to do.