How can I create a macro that works on a number of sheets all at once in
which the lists are mulitple lengths. In other words, one sheet has 134
entries while the second has 524 and so on. I need to create a column in
which a formula recognizes type of worker and then spits out the hours per
week. The formula is done.
How can I get Excel to recognize the differing column lengths and fill in
the formula accordingly? I have tried going to the bottom of the data and
shift-arrowup, but this doesn't work.
Please keep in mind that I don't speak VBA, but record my macros.
Thanks,
Nicole

Nicole - You really can't *record* a macro that is flexible and dynamic. If
you need those virtues, you're going to have to write VBA code.

There are willing, able helpers here who can assist you, given enough
details about what you're trying to accomplish.


"Nicole Seibert" wrote:

> How can I create a macro that works on a number of sheets all at once in
> which the lists are mulitple lengths. In other words, one sheet has 134
> entries while the second has 524 and so on. I need to create a column in
> which a formula recognizes type of worker and then spits out the hours per
> week. The formula is done.
> How can I get Excel to recognize the differing column lengths and fill in
> the formula accordingly? I have tried going to the bottom of the data and
> shift-arrowup, but this doesn't work.
> Please keep in mind that I don't speak VBA, but record my macros.
> Thanks,
> Nicole

What you want to do requires VBA. You can not just record what you want. It
will look something like this...

sub DoSomeStuff()
dim wks as worksheet

for each wks in worksheets
wks.cells(rows.count, "A").end(xlup).offset(1, 0).formula = "=1+2"
next wks
exit sub

This puts the formula =1+2 into the first empty cell in column A of every
sheet. What I need to know is what formula do you need and are there any
sheets that should be excluded.
--
HTH...

Jim Thomlinson


"Nicole Seibert" wrote:

> How can I create a macro that works on a number of sheets all at once in
> which the lists are mulitple lengths. In other words, one sheet has 134
> entries while the second has 524 and so on. I need to create a column in
> which a formula recognizes type of worker and then spits out the hours per
> week. The formula is done.
> How can I get Excel to recognize the differing column lengths and fill in
> the formula accordingly? I have tried going to the bottom of the data and
> shift-arrowup, but this doesn't work.
> Please keep in mind that I don't speak VBA, but record my macros.
> Thanks,
> Nicole

Oh, and can I tell Excel to create a column and put the information there
....after the last column with information in it.
Thanks again.

"Jim Thomlinson" wrote:

> What you want to do requires VBA. You can not just record what you want. It
> will look something like this...
>
> sub DoSomeStuff()
> dim wks as worksheet
>
> for each wks in worksheets
> wks.cells(rows.count, "A").end(xlup).offset(1, 0).formula = "=1+2"
> next wks
> exit sub
>
> This puts the formula =1+2 into the first empty cell in column A of every
> sheet. What I need to know is what formula do you need and are there any
> sheets that should be excluded.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Nicole Seibert" wrote:
>
> > How can I create a macro that works on a number of sheets all at once in
> > which the lists are mulitple lengths. In other words, one sheet has 134
> > entries while the second has 524 and so on. I need to create a column in
> > which a formula recognizes type of worker and then spits out the hours per
> > week. The formula is done.
> > How can I get Excel to recognize the differing column lengths and fill in
> > the formula accordingly? I have tried going to the bottom of the data and
> > shift-arrowup, but this doesn't work.
> > Please keep in mind that I don't speak VBA, but record my macros.
> > Thanks,
> > Nicole

Thank you.

The fomula is as follows:
=IF(F4="98 NTNI",7.5,IF(F4="GENERIC",0,IF(F4="17 PARTIME
NONEXEMPT",4,IF(F4="18 REGULAR PART TIME NONEXEMPT",4,IF(F4="27 PART TIME
EXEMPT",4,IF(F4="28 REGULAR PART TIME EXEMPT",4,8))))))

The worksheets have been renamed ___________ Supply and there are four of
them book that is 7 to 8 worksheets. Can I get Excel to recognize the name
of a worksheet like it would when I name a column?

"Jim Thomlinson" wrote:

> What you want to do requires VBA. You can not just record what you want. It
> will look something like this...
>
> sub DoSomeStuff()
> dim wks as worksheet
>
> for each wks in worksheets
> wks.cells(rows.count, "A").end(xlup).offset(1, 0).formula = "=1+2"
> next wks
> exit sub
>
> This puts the formula =1+2 into the first empty cell in column A of every
> sheet. What I need to know is what formula do you need and are there any
> sheets that should be excluded.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Nicole Seibert" wrote:
>
> > How can I create a macro that works on a number of sheets all at once in
> > which the lists are mulitple lengths. In other words, one sheet has 134
> > entries while the second has 524 and so on. I need to create a column in
> > which a formula recognizes type of worker and then spits out the hours per
> > week. The formula is done.
> > How can I get Excel to recognize the differing column lengths and fill in
> > the formula accordingly? I have tried going to the bottom of the data and
> > shift-arrowup, but this doesn't work.
> > Please keep in mind that I don't speak VBA, but record my macros.
> > Thanks,
> > Nicole

Nicole -

Your formula is needlessly complicated. It could be much simpler if you
created a table in each worksheet & structured it as a lookup table like this:

Column A Column B
98 NTNI 7.50
GENERIC 0.00
17 PARTIME NONEXEMPT 4.00
18 PARTIME NONEXEMPT 4.00
27 PARTIME NONEXEMPT 4.00
28 PARTIME NONEXEMPT 4.00

Give the range a name, such as Rates

Your formula would then be:

=IF(ISNA(VLOOKUP(F4, Rates,2,0)),8,VLOOKUP(F4, Rates,2,0))

If you can't put this table in each workbook, use this formula instead:

=IF(OR(F4="17 PARTIME NONEXEMPT",F4="18 PARTIME NONEXEMPT",F4="27 PARTIME
NONEXEMPT",F4="28 PARTIME NONEXEMPT"),4,IF(F4="98
NTNI",7.5,IF(F4="GENERIC",0,8)))

Now, for the macro - where do you want this formula to go in each sheet? Is
it always the same column and just the number of rows vary from sheet to
sheet? Try to carefully state the steps you would follow if you were going
to do this manually.


"Nicole Seibert" wrote:

> Oh, and can I tell Excel to create a column and put the information there
> ...after the last column with information in it.
> Thanks again.
>
> "Jim Thomlinson" wrote:
>
> > What you want to do requires VBA. You can not just record what you want. It
> > will look something like this...
> >
> > sub DoSomeStuff()
> > dim wks as worksheet
> >
> > for each wks in worksheets
> > wks.cells(rows.count, "A").end(xlup).offset(1, 0).formula = "=1+2"
> > next wks
> > exit sub
> >
> > This puts the formula =1+2 into the first empty cell in column A of every
> > sheet. What I need to know is what formula do you need and are there any
> > sheets that should be excluded.
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Nicole Seibert" wrote:
> >
> > > How can I create a macro that works on a number of sheets all at once in
> > > which the lists are mulitple lengths. In other words, one sheet has 134
> > > entries while the second has 524 and so on. I need to create a column in
> > > which a formula recognizes type of worker and then spits out the hours per
> > > week. The formula is done.
> > > How can I get Excel to recognize the differing column lengths and fill in
> > > the formula accordingly? I have tried going to the bottom of the data and
> > > shift-arrowup, but this doesn't work.
> > > Please keep in mind that I don't speak VBA, but record my macros.
> > > Thanks,
> > > Nicole

Sorry. Work got nuts and I could not get back to your question. Do you still
need help with this?
--
HTH...

Jim Thomlinson


"Nicole Seibert" wrote:

> Oh, and can I tell Excel to create a column and put the information there
> ...after the last column with information in it.
> Thanks again.
>
> "Jim Thomlinson" wrote:
>
> > What you want to do requires VBA. You can not just record what you want. It
> > will look something like this...
> >
> > sub DoSomeStuff()
> > dim wks as worksheet
> >
> > for each wks in worksheets
> > wks.cells(rows.count, "A").end(xlup).offset(1, 0).formula = "=1+2"
> > next wks
> > exit sub
> >
> > This puts the formula =1+2 into the first empty cell in column A of every
> > sheet. What I need to know is what formula do you need and are there any
> > sheets that should be excluded.
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Nicole Seibert" wrote:
> >
> > > How can I create a macro that works on a number of sheets all at once in
> > > which the lists are mulitple lengths. In other words, one sheet has 134
> > > entries while the second has 524 and so on. I need to create a column in
> > > which a formula recognizes type of worker and then spits out the hours per
> > > week. The formula is done.
> > > How can I get Excel to recognize the differing column lengths and fill in
> > > the formula accordingly? I have tried going to the bottom of the data and
> > > shift-arrowup, but this doesn't work.
> > > Please keep in mind that I don't speak VBA, but record my macros.
> > > Thanks,
> > > Nicole

Thanks to both of you. I think I am going to try and do this before I ask for
any more help.

"Jim Thomlinson" wrote:

> Sorry. Work got nuts and I could not get back to your question. Do you still
> need help with this?
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Nicole Seibert" wrote:
>
> > Oh, and can I tell Excel to create a column and put the information there
> > ...after the last column with information in it.
> > Thanks again.
> >
> > "Jim Thomlinson" wrote:
> >
> > > What you want to do requires VBA. You can not just record what you want. It
> > > will look something like this...
> > >
> > > sub DoSomeStuff()
> > > dim wks as worksheet
> > >
> > > for each wks in worksheets
> > > wks.cells(rows.count, "A").end(xlup).offset(1, 0).formula = "=1+2"
> > > next wks
> > > exit sub
> > >
> > > This puts the formula =1+2 into the first empty cell in column A of every
> > > sheet. What I need to know is what formula do you need and are there any
> > > sheets that should be excluded.
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Nicole Seibert" wrote:
> > >
> > > > How can I create a macro that works on a number of sheets all at once in
> > > > which the lists are mulitple lengths. In other words, one sheet has 134
> > > > entries while the second has 524 and so on. I need to create a column in
> > > > which a formula recognizes type of worker and then spits out the hours per
> > > > week. The formula is done.
> > > > How can I get Excel to recognize the differing column lengths and fill in
> > > > the formula accordingly? I have tried going to the bottom of the data and
> > > > shift-arrowup, but this doesn't work.
> > > > Please keep in mind that I don't speak VBA, but record my macros.
> > > > Thanks,
> > > > Nicole

That is the best way to learn. If you want some help take a look at the
Excel.Programming section (You are in Excel.Worksheet.Functions at the
moment). There are lots of good examples and piles of very accomplished Excel
programmers.
--
HTH...

Jim Thomlinson


"Nicole Seibert" wrote:

> Thanks to both of you. I think I am going to try and do this before I ask for
> any more help.
>
> "Jim Thomlinson" wrote:
>
> > Sorry. Work got nuts and I could not get back to your question. Do you still
> > need help with this?
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Nicole Seibert" wrote:
> >
> > > Oh, and can I tell Excel to create a column and put the information there
> > > ...after the last column with information in it.
> > > Thanks again.
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > What you want to do requires VBA. You can not just record what you want. It
> > > > will look something like this...
> > > >
> > > > sub DoSomeStuff()
> > > > dim wks as worksheet
> > > >
> > > > for each wks in worksheets
> > > > wks.cells(rows.count, "A").end(xlup).offset(1, 0).formula = "=1+2"
> > > > next wks
> > > > exit sub
> > > >
> > > > This puts the formula =1+2 into the first empty cell in column A of every
> > > > sheet. What I need to know is what formula do you need and are there any
> > > > sheets that should be excluded.
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "Nicole Seibert" wrote:
> > > >
> > > > > How can I create a macro that works on a number of sheets all at once in
> > > > > which the lists are mulitple lengths. In other words, one sheet has 134
> > > > > entries while the second has 524 and so on. I need to create a column in
> > > > > which a formula recognizes type of worker and then spits out the hours per
> > > > > week. The formula is done.
> > > > > How can I get Excel to recognize the differing column lengths and fill in
> > > > > the formula accordingly? I have tried going to the bottom of the data and
> > > > > shift-arrowup, but this doesn't work.
> > > > > Please keep in mind that I don't speak VBA, but record my macros.
> > > > > Thanks,
> > > > > Nicole