Searching for text and adding cells

[MATT.B]

I have never worked with vbs before and i need a script made of help making a script.
Example 1 is what I have now EXAMPLE1 ESTIMATE.xls
I'm looking for a vbs that will search 20B-32B for the letters A,B,C,D,E,F, OR G, Take column J(parts),L(labor),and M(misc) for each row matching A,B,C,D,E,F, OR G add them together then import them into the bottom for A F42(parts)F41(labor),F43(misc)
there could be 1 Row - 12 Rows for A that need to be added up and some times B,C,D,E,F, or G will not appear on the sheet
The layout of the sheet will not change just the data added.
the calculations should be done on the fly so no button or key will need to be used.
and the end results for this example should be:EXAMPLE2 ESTIMATE.xls
repair line A Row F
F41 LABOR $220
F42 PARTS $21.79
F43 MISC $20.00
Tax and total will update on its own using a simple formula
freight will be added manually at bottom
0 values are hidden with conditional formatting that has been disabled in the example
refer to example 2 for what i want it to look like using the formula/vbs
EXAMPLE1 ESTIMATE.xlsEXAMPLE2 ESTIMATE.xls
thank you for any help
if any questions please ask ill do my best to give a quick reply

[Jack7774]

Try this out

Please Login or Register  to view this content.

[daffodil11]

Trying to understand your form design, but this could all be done pretty seamlessly with normal formulas.

SUMPRODUCT or SUMIF could both accomplish this:

A41
=SUMPRODUCT(($B$21:$C$32=F$40)*($L$21:$M$32))
A42
=SUMPRODUCT(($B$21:$C$32=F$40)*($J$21:$J$32))
A43
=SUMPRODUCT(($B$21:$C$32=F$40)*($N$21:$N$32))

and copy formulas across

[Jack7774]

Trying to understand your form design, but this could all be done pretty seamlessly with normal formulas.

SUMPRODUCT or SUMIF could both accomplish this:

A41
=SUMPRODUCT(($B$21:$C$32=F$40)*($L$21:$M$32))
A42
=SUMPRODUCT(($B$21:$C$32=F$40)*($J$21:$J$32))
A43
=SUMPRODUCT(($B$21:$C$32=F$40)*($N$21:$N$32))

and copy formulas across

He is correct this is a much easier process

[MATT.B]

I think your missing the search for a b c d e f and g

[MATT.B]

Try this out

Please Login or Register  to view this content.

i need it to know the diferance between A B C D E F G and seperate each

[MATT.B]

He is correct this is a much easier process

this will not work column B is a user imput value and does not stay the same one estimate could have 1 line for A while another would have 12 lines for A its based on the repair work being done to the Vehicle just like 1 est could have repair lines A-C while another will only have A the only Row that formula will work for is row 20


the way this sheet works at it starts blank then we add the customer name, vin, ro# and unit# depending on the truck.
and then depending on the ro depends on what goes on it
lets say you bring in a truck and the wipers dont work the horn doesnt work and you need an oil change
repair line A is wipers dont work and repair line B is horn inop and C would be oil change
well A and B would be 2-3 rows each and C would be 6 rows
what needs to be done is
search for A in column B between 21 and 32 add value of column J(for this row) to partsAvar add value of column L(for this row) to laborAvar add value of column N(for this row) in MiscAvar
find next A in column B between 21 and 32 and repeat
then search for B in column B between 21 and 32 add value of column J(for this row) to partsBvar add value of column L(for this row) to laborBvar add value of column N(for this row) in MiscBvar
find next B in column B between 21 and 32 and repeat
do this for C,D,E,F,G as well
then display laborAvar in F41 PartsAvar in F42 miscAvar in F43
LaborBvar in G41 partsBvar in G42 miscBvar in G43
same for C, D, E, F, G
but in columns I , J, K, L, N
I hope this explains it better

[Jack7774]

i need it to know the diferance between A B C D E F G and seperate each

I'm not following what you mean. It matches the answer sheet given.

[Jack7774]

I ran the code a few times and can't seem to figure out what you mean however I did find a loop error though maybe that will fix what you were talking about.

Please Login or Register  to view this content.

[MATT.B]

I ran the code a few times and can't seem to figure out what you mean however I did find a loop error though maybe that will fix what you were talking about.

Please Login or Register  to view this content.

I'm not following what you mean. It matches the answer sheet given.

refer to example 3 and example 4 to see how this sheet changes depending on customer.
EXAMPLE 3 ESTMATE.xlsEXAMPLE 4 ESTMATE.xls

[Jack7774]

I didn't see an issue except in cells F40 where you normally had A through "x" you didn't have anything. I put an A in there and Boom the numbers appeared. Same thing for exp 4.

[MATT.B]

i dont understand the coding but ill take a look and let you know if there are any errors
but i dont have excel at home so it will have to wait till i return to work

[MATT.B]

I didn't see an issue except in cells F40 where you normally had A through "x" you didn't have anything. I put an A in there and Boom the numbers appeared. Same thing for exp 4.

seems to work so far ill give it a try and thank you
if i just copy this script to my original it shold work or is there more i will need to do?

[MATT.B]

seems to work so far ill give it a try and thank you
if i just copy this script to my original it shold work or is there more i will need to do?

another thing i want to change misc to freight what would i need to do to the code to make this change?
im going to rename N20 to freight: or Frt
and change the output to row 44

[MATT.B]

apparently both ways will work thank you

[Jack7774]

Here is what the macro does. It starts in cell B21 and says whatever this cell ='s it grabs the data and searches for that cells ='s in cells F40 through N40. If is doesn't find anything it won't add anything so that is something to keep in mind however if it does find something it will add to whatever value is there and then replace the sum value in those cells. So hypothetically you could run that macro 100 times and the value would just keep growing. I can add in a clear feature before the macro runs each time if you think you might run into that issue. Changing that from Misc to freight won't break the macro either. Let me know if you need anything else. Sorry I wasn't a faster response.