Z
zoola
Hello,
I have been asked by a friend to help with vb/excel, however i have not
touched vb in my life. I have a background in c/c++, so if someone could
point out to me what I might need to do or how I would go about it that
would be great.
Here is the letter from my friend asking for assistance:
What I need help with is writing a macro that will automatically
provide data into a hidden sheet “hide pack summary” from which other
documents can take this data;
1. Sum up the total weight in a carton. E.g. the cells in blue on
the “pack data”.sheet
a. But there can be from 1 up to 8 fish in a carton.
b. The form is not continuous – i.e. need a total over several
sheets. Sheet”pack data”.Range - B22:G52, I22:N52, P22:U52, V22:AB52,
B73:B102, I73:N102, P73:U102, V73:AB102, B124:B153, I124:N153,
P124:U153, V124:AB153, B175:B204, I175:N204, P175:U204, V175:AB204
My thoughts were to use the subtotal(2).
I don’t know how to specify the range(since it changes)
Can I tell it to offset add to a cumulative subtotal if it starts with
value in the carton column until next carton is reached then paste
value & subtotal(2)=0 ??
2. Within a given range (in orange above), count the number of
cartons that satisfy criteria i.e. match species and processing
description (e.g. YF/GG – which will be given in cell reference) Go
through and do the same for subsequent descriptions. E.g. for every
species and description determine the number of cartons. I had a go at
=COUNTIF(x,x) in excel, and subtotal(x) in visual and combinations of
but no luck.
Logic:
worksheet”hide pack summary”.activate
Range”D2”.activate
While
make activecell=descrip
worksheet”packdata”.select range”G22”.select. and then basically with
IF and IFNOT, then, offset(1,0) to see if descrip= ‘SPECIES/FORM
column’ and if a value in ‘Carton column’then count if not then don’t
count.
When the end of the range is reached
worksheet”hide pack summary”.activate
Spit back solution.
Offset(1row,0column)
Wend
Until blank cell.
Wend
3. Give a total weight for each description ((e.g. YF/GG – which
will be given in cell reference or need be typed in) – I think I may
have solved the problem with =sumif(x,x,x)
4. In ”FOB invoice” provide a summary of this data but only for
species/form combinations that have data. E.g. if YF/GG = 3boxes,
ALB/GG=2 but YF/WH = 0 box then only return YF/GG and ALB/GG data.
My email is available upon request, I would be guessing a copy of the
worksheets are required, if so please email me.
Any assitance is greatly greatly appreciated.
edit, attaching workbook, i didnt attach originally out of respect for
my friend as it possibly has confidential information.
http://www.wopr.com/w3tfiles/5-496026-x.zip
I have been asked by a friend to help with vb/excel, however i have not
touched vb in my life. I have a background in c/c++, so if someone could
point out to me what I might need to do or how I would go about it that
would be great.
Here is the letter from my friend asking for assistance:
What I need help with is writing a macro that will automatically
provide data into a hidden sheet “hide pack summary” from which other
documents can take this data;
1. Sum up the total weight in a carton. E.g. the cells in blue on
the “pack data”.sheet
a. But there can be from 1 up to 8 fish in a carton.
b. The form is not continuous – i.e. need a total over several
sheets. Sheet”pack data”.Range - B22:G52, I22:N52, P22:U52, V22:AB52,
B73:B102, I73:N102, P73:U102, V73:AB102, B124:B153, I124:N153,
P124:U153, V124:AB153, B175:B204, I175:N204, P175:U204, V175:AB204
My thoughts were to use the subtotal(2).
I don’t know how to specify the range(since it changes)
Can I tell it to offset add to a cumulative subtotal if it starts with
value in the carton column until next carton is reached then paste
value & subtotal(2)=0 ??
2. Within a given range (in orange above), count the number of
cartons that satisfy criteria i.e. match species and processing
description (e.g. YF/GG – which will be given in cell reference) Go
through and do the same for subsequent descriptions. E.g. for every
species and description determine the number of cartons. I had a go at
=COUNTIF(x,x) in excel, and subtotal(x) in visual and combinations of
but no luck.
Logic:
worksheet”hide pack summary”.activate
Range”D2”.activate
While
make activecell=descrip
worksheet”packdata”.select range”G22”.select. and then basically with
IF and IFNOT, then, offset(1,0) to see if descrip= ‘SPECIES/FORM
column’ and if a value in ‘Carton column’then count if not then don’t
count.
When the end of the range is reached
worksheet”hide pack summary”.activate
Spit back solution.
Offset(1row,0column)
Wend
Until blank cell.
Wend
3. Give a total weight for each description ((e.g. YF/GG – which
will be given in cell reference or need be typed in) – I think I may
have solved the problem with =sumif(x,x,x)
4. In ”FOB invoice” provide a summary of this data but only for
species/form combinations that have data. E.g. if YF/GG = 3boxes,
ALB/GG=2 but YF/WH = 0 box then only return YF/GG and ALB/GG data.
My email is available upon request, I would be guessing a copy of the
worksheets are required, if so please email me.
Any assitance is greatly greatly appreciated.
edit, attaching workbook, i didnt attach originally out of respect for
my friend as it possibly has confidential information.
http://www.wopr.com/w3tfiles/5-496026-x.zip