special account numbers different formulas macro???

  • Thread starter Thread starter gr777
  • Start date Start date
G

gr777

I have a worksheet that has 1000 account numbers. Most of the account
numbers have a simple formula, although 25 of them use a different
formula because they are given a discount. My account numbers are 8
digits long and are in column A. My formula is in column J. So I've
poplulated all of column J with the formula that is correct for most of
the accounts. This is what I'm trying to accomplish. I want a macro
that looks for specific account numbers, not a range. Then I want it to
plug in the new formula. Any advice. Thanks.
 
gr777

'although 25 of them use a different formula'

Are these accounts easy to identify? Do they start with a different
letter/number? Are they a different length? You need find a way to identify
these accounts, on their own.

Andy.


gr777 said:
I have a worksheet that has 1000 account numbers. Most of the account
numbers have a simple formula, although 25 of them use a different
formula because they are given a discount. My account numbers are 8
digits long and are in column A. My formula is in column J. So I've
poplulated all of column J with the formula that is correct for most of
the accounts. This is what I'm trying to accomplish. I want a macro
that looks for specific account numbers, not a range. Then I want it to
plug in the new formula. Any advice. Thanks.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Yes, the account numbers are different. Here is an example of the dat
that I'll be using.

Column A column J
80489856 a1*.0025
75689458 a2*.0025
65984850 (.75*a3)*.0025
12584853 a4*.0025
48509653 (.75*a5)*.0025

So I know the 25 account numbers that have the seperate formula. The
get a price break. So I'm just shooting for a macro that will look fo
the 20 account numbers, and plug the formula in column j. Thanks fo
any and all help
 
I think if you're going to intermingle formulas, you're looking for trouble.

It really becomes a pain when you want to insert rows and copy down formulas.
You'll have lots of headaches if you do. (I know it scares the heck out of me
when I do it!)

It looks like your formulas are almost all the same--except for the rate.

I think I'd add an additional column that evaluates to the rate paid.

1.00
0.75
1.00
0.75
(and even more when you apply future discounts.)


Then put that factor in your formula in column J.

=(A2*.0025)*K2
(if K2 held the rate for that account.)

And to make life easier later, I'd insert a new worksheet (call it Rate) that
consisted of the account numbers and the going rate:

80489856 1
75689458 1
65984850 .75
etc

Then in K2, you could use a formula like:

=vlookup(A2,rate!a:b,2,false)

Then when one of your rates changed (or you get a new customer code), you just
fix/add it to the rate sheet and your price sheet gets updated quickly.
 
Back
Top