"Myrna Larson" wrote:

I'm glad the macro works as you want. As far as your current situation, I can
understand the problem. However, I can't see how you can sell something that
doesn't yet exist. You want to sell a completely computerized business system,
but you don't yet have that. Sounds to me like you need to list the system
specifications, get financing somewhere, somehow, and hire an Excel
consultant/programmer to write the code.

Chip Pearson and Rob Bovey, both Excel MVPs, do that sort of work. I am
retired and not interested in doing commercial programming that could entail
legal liability.



On Wed, 2 Feb 2005 05:53:03 -0800, VJ7777
wrote:



"Myrna Larson" wrote:

The following looks for the last name entered in column A, and the last
formula entered in column B. Then if fills the formulas down through all of
the rows that have names. Then it goes through the newly filled rows,
replacing the name that was used in the last formula row with the name
that's
in column A of the current row.

As far as more macros are concerned, here's my reaction. Since you are
"designing" a business system, I think it's imperative that YOU understand
how
the macros work and are able to modify or fix them if needed and how to
write
more code for other tasks. Maybe this would be a good point for you to
start
on that aspect of it. Otherwise, since this is a business application, you
probably should hire an Excel consultant who will guarantee his work.

Option Explicit

Sub CopyFormulasDown()
Dim LastFormulaRow As Long
Dim LastNameRow As Long
Dim OldName As String
Dim R As Long

With ActiveSheet
'find last row with a formula -- use column B
LastFormulaRow = .Cells(.Rows.Count, 2).End(xlUp).Row
'find last row with a name -- column A
LastNameRow = .Cells(.Rows.Count, 1).End(xlUp).Row
'if there are no rows without formulas, quit
If LastNameRow <= LastFormulaRow Then Exit Sub

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'fill the formulas down
.Cells(LastFormulaRow, 2) _
.Resize(LastNameRow - LastFormulaRow + 1, 254).FillDown

'get the old name used in those formulas from column A
OldName = .Cells(LastFormulaRow, 1).Value

'go through all rows that were without formulas, replacing that
'old name with what's in column A of the current row
For R = LastFormulaRow + 1 To LastNameRow
.Rows(R).Replace what:=OldName, Replacement:=.Cells(R, 1).Value, _
LookAt:=xlPart, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next R

'recalculate the sheet
.Calculate

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End With
End Sub

On Tue, 1 Feb 2005 06:39:09 -0800, VJ7777

wrote:


Thank you! Thank you!! Thank you!!! It works beautifully. To stretch
my
luck, could you give me a modification of this macro which looks for
(begins
with) the name in A6 and continues copying as long as there is a name in
Column A. This way I could copy and paste a list of names into Column A5
through A whatever. Then I could write formulas in Row 5 from B5 to
whatever.
Then hit the "name" button to begin and the macro would copy beginning at
Row
6 until the end of names in Column A.
I really appreciate this. It is the next-to-final step in what I consider
to be a great system for manufactured home dealerships. There is one more
thing I need. If you aren't sorry you offered to help me by now, perhaps
I
could ask one more thing after this one.
Thanks again,
Vince

Thank you again, Myrna. Your macro works like a dream come true!!! I want
to get
back to you to say you are certainly correct in your suggestion. Let me
tell you why I
haven't done so and to see if you have an interest in the following:

I have been designing and modifying this system for the manufactured home
dealership
for which I am General Partner for five years. When I had to take it over
the dealership
due to a bad loan the partnerships for which I am general partner had made I
concluded
the best way to get our money back was to operate the dealership. Due to
problems in the
industry at that time, etc., we have lost $100,000/year for 5 years; finally
it is turning
around. There was no computer system there nor (apparently) was there any
systems help
available from the state or national associations. So I began slowly to
develop a useable
system and have modified it as we added product lines, changed various
business
practices, etc.

In 1957, yes, 57. I began as (an adding machine salesman, then) a
bookkeeping machine
salesperson who had to design the system and program the machines I sold.
Throughout
later years as a product manager, etc. I learned Basic and Fortran (and
could read some
Cobol) by studying the programs for which I was manager. But, Virtual Basic
is like
Sanskrit to me. I have glanced at the manual multiple times and havent
found a place to
start. On the other hand, I have been able to accomplish my system goals
with only a
limited knowledge of Excel. My mantra has been €œGet the job done now and
enhance it
later.€�

Long story short, I enjoy doing the system in Excel. I know, as a €œmanager€�
I shouldnt
allow myself to enjoy programming the system, but one has to have some
pleasure in life
- my wife does cryptograms for fun when she isnt helping me with the
partnership work -
and I try to improve my system design for fun. I have been able to do
almost everything I
needed to do without outside assistance. The system handles everything from
the time a
customer walks into the dealership until the home is installed and paid for.
But a couple
of reporting capabilities needed a little help.

Im at the point where, after one more macro or formula to create a daily
€œTo Do€� report,
I will be able to spend some time on preparing to sell it to the 7,000
dealerships in the
U.S. I can demonstrate it. It is in use daily. Therefor I should be able
to sell it. If I can
sell it, then I will have the money to pay for whatever level of programming
competence
is needed.

Chicken or egg; you ask. I understand that my approach may not be the most
logical
business approach but its the approach I can handle right now. This, plus
my other
duties, keeps me working during most of 12 hours each day, 6 or 7 days/week.

I am aware I will need a partner or partners. I really dont have time to
€œsell.€� I have
limited capability and time to make system modifications for a new user; I
believe I will
need to find a sales organization which can promote the product; therefore
maybe a
partner to find the actual sales organization and interface with it (and
sell the state
associations and national association to promote the system so that they can
gather
statistics on our future Internet output), and a partner to deal with system
modifications
and manage a staff to make modifications for clients. My objective is to
accomplish this
with €œpartners€� rather than a front-end outlay of capital.

Do you (or anyone in whom you have confidence) want to propose a joint
venture?

Best regards,
Vince

Hi, Myna:

Thank you for the names of the the Excel MVPs, Chip Pearson and Rob Bovey.

Somehow I have given you the impression the system is not complete. The
manufactured
home dealership business is very complex and my system has been used for all
facets of
the business for approximately five years. The reports have been available
almost from
the beginning. The 1st macro makes it easier for me to add names to the
Prospective
Business Report and the Installation Report as the year progresses; the
second macro is to
make it easier for me to make a quick and dirty report for my own use now or
for
whatever might be required in the future.

The thing my system lacks (which would be automatic in Access) is the
ability to add
customer names to all reports automatically. Instead, I, or a system
administrator, must
add them.

The macro I need now is only because salespersons are poor record keepers.
My reports
tell them what functions are delinquent as of the report date. Just like a
bunch of
kindergarteners, they dont pay attention to a simple comprehensive report.
To make
them pay attention, I want a report that tells them what must be done TODAY.
Ridiculous? Yes! Necessary? Shouldnt be but, Yes!!!

In case you are willing to do one more macro I will define a daily report.

Each row will handle one customer (File Name). (10 rows, 10 customers) I
will use your
macro to add customers to the report as the year progresses.

Column A = File Name

Column B = Sales activity that should be done today. Source: If any cell
in (FileName)
B26 through B68 equals TODAY() put the adjacent words from C26 through C68
in this
cell. if more than one date equals TODAY() concatenate the adjacent words
from C26
through C68 after printing a semicolon space between each date/comment
..
I will make the format in this cell wrap and shrink to fit.

Column C = Construction activities which should be completed today. If any
cell in
(FileName) M2 through M33 equals TODAY(), and if N2 through N33 equals €œ?€�,
put
the adjacent words from K2 through K33 into this cell; if more than one date
equals
TODAY() and adjacent contents of N equals €œ?€�, concatenate the adjacent
words from
K2 through K33 after printing a semicolon space.
I will make the format in this cell wrap and shrink to fit.

Columns D through whatever = my formulae to look at other pertinent dates in
the
customer file. Your 1st macro will be used to add names to each
salesperson's Daily Activity Report.

Thanks again for listening to me.
Vince