# vba help...ranges in new sheet etc

Z

#### zoola

Hello,

I have been asked by a friend to help with vb/excel, however i have no
touched vb in my life. I have a background in c/c++, so if someone coul
point out to me what I might need to do or how I would go about it tha
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 wit

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 a

=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 th
worksheets are required, if so please email me.

Any assitance is greatly greatly appreciated.

edit, attaching workbook, i didnt attach originally out of respect fo
my friend as it possibly has confidential information.

http://www.wopr.com/w3tfiles/5-496026-x.zi

Z

Z

#### zoola

please, really needing some help with this

J

#### jose luis

Zoola, have you tried to use the Macro Recorder from Excel (Tools
Menu)?. With it you could make the operations you need and then in the
VBA editor watch the instructions Excel used to acomplish the task.

Hope this give you and idea

Regards

Jose Luis