Re: Corrupted Macro
From: Myrna Larson (anonymous_at_discussions.microsoft.com)
Date: 02/02/05
- Next message: anonymous_at_discussions.microsoft.com: "Re: Logging activity"
- Previous message: Hari Prasadh: "Re: Difference between Word and Excel"
- In reply to: VJ7777: "Re: Corrupted Macro"
- Next in thread: VJ7777: "Re: Corrupted Macro"
- Reply: VJ7777: "Re: Corrupted Macro"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 02 Feb 2005 11:28:57 -0600
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 <VJ7777@discussions.microsoft.com>
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
<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
>
>
>
>
>
>
>
>
>
>
>
- Next message: anonymous_at_discussions.microsoft.com: "Re: Logging activity"
- Previous message: Hari Prasadh: "Re: Difference between Word and Excel"
- In reply to: VJ7777: "Re: Corrupted Macro"
- Next in thread: VJ7777: "Re: Corrupted Macro"
- Reply: VJ7777: "Re: Corrupted Macro"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
