Re: Corrupted Macro

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: VJ7777 (VJ7777_at_discussions.microsoft.com)
Date: 02/02/05 

Date: Wed, 2 Feb 2005 05:53:03 -0800

"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 <VJ7777@discussions.microsoft.com>
> 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 haven’t
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 shouldn’t
allow myself to enjoy programming the system, but one has to have some
pleasure in life
- my wife does cryptograms for fun when she isn’t 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.

 I’m 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 it’s 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 don’t 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

 

Relevant Pages

  • Re: Corrupted Macro
    ... I'm glad the macro works as you want. ... You want to sell a completely computerized business system, ... retired and not interested in doing commercial programming that could entail ... >due to a bad loan the partnerships for which I am general partner had made I ...
    (microsoft.public.excel.programming)
  • Re: start up advice wanted!
    ... Is it an industrial/manufacturing community? ... sell, and whom is he going to sell it to? ... A partner worth having will want to know the answers to these. ... Define criteria for "trustworthy". ...
    (rec.crafts.metalworking)
  • Re: Question of property rights
    ... partner. ... she come back and remove her belongings. ... (Interference with Goods) ... friend can sell the goods, he must hand over the proceeds to his ex- ...
    (uk.legal)
  • counting clicks on a hyperlink...
    ... a person i designed a site for is trading links with a partner to try to ... sell a product. ... thanks for any opinions or pointers to resources... ...
    (alt.html)
  • Re: Packard Dell
    ... and whose partner is a bit of cunt with a ... It's a Packard Dell, not much dell left. ... I'll see if I can sell it to a wrinkly. ... No go goose liver and stale genitalia with ruined budgerigar intestines, ...
    (alt.os.windows-xp)