I forgot the line that said

SheetNum = Val(Replace(mySheet, "Sheet", ""))

but I assume that you worked that out as you got it working.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
ups.com...
On Nov 9, 3:47 pm, "Bob Phillips" wrote:
Assuming that the test is on a variable called mySheet

Formula1 = "=min(Sheet1!" & Columns(SheetNum - 2).Address(0, 0) & ")"
Formula2 = "=max(Sheet1!" & Columns(SheetNum - 2).Address(0, 0) & ")"
Formula3 = "=frequency(Sheet1!" & _
Cells(2, SheetNum - 2).Resize(Rows.Count - 1).Address(0,
0) &
_
toshname & ",!A2:A201)"

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

ups.com...



Modifying every formula in the code given below is too time-consuming.
Is there any way to simplify this code? I would really appreciate if
anyone can help me in this. Thanks

Case "Sheet3"
formula1 = "=min(Sheet1!A:A)"
formula2 = "=max(Sheet1!A:A)"
formula3 = "=frequency(Sheet1!A2:A65536," & toshname & "!
A2:A201)"
Case "Sheet4"
formula1 = "=min(Sheet1!B:B)"
formula2 = "=max(Sheet1!B:B)"
formula3 = "=frequency(Sheet1!B2:B65536," & toshname & "!
A2:A201)"
Case "Sheet5"
formula1 = "=min(Sheet1!C:C)"
formula2 = "=max(Sheet1!C:C)"
formula3 = "=frequency(Sheet1!C2:C65536," & toshname & "!
A2:A201)"
Case "Sheet6"
formula1 = "=min(Sheet1!D:D)"
formula2 = "=max(Sheet1!D:D)"
formula3 = "=frequency(Sheet1!D2:D65536," & toshname & "!
A2:A201)"
Case "Sheet7"
formula1 = "=min(Sheet1!E:E)"
formula2 = "=max(Sheet1!E:E)"
formula3 = "=frequency(Sheet1!E2:E65536," & toshname & "!
A2:A201)"
Case "Sheet8"
formula1 = "=min(Sheet1!F:F)"
formula2 = "=max(Sheet1!F:F)"
formula3 = "=frequency(Sheet1!F2:F65536," & toshname & "!
A2:A201)"
Case "Sheet9"
formula1 = "=min(Sheet1!G:G)"
formula2 = "=max(Sheet1!G:G)"
formula3 = "=frequency(Sheet1!G2:G65536," & toshname & "!
A2:A201)"
Case "Sheet10"
formula1 = "=min(Sheet1!H:H)"
formula2 = "=max(Sheet1!H:H)"
formula3 = "=frequency(Sheet1!H2:H65536," & toshname & "!
A2:A201)"
Case "Sheet11"
formula1 = "=min(Sheet1!I:I)"
formula2 = "=max(Sheet1!I:I)"
formula3 = "=frequency(Sheet1!I2:I65536," & toshname & "!
A2:A201)"
.............
..................
...................... Case "Sheet255"- Hide quoted text -

- Show quoted text -

Bob, Thanks a million.. You helped me to save atleast 4-5 hours of
monotonous work.
I made a minor change to formula3 to make things working. There was ,
(comma) which was misplaced

formula3 = "=frequency(Sheet1!" & Cells(2, sheetnum -
2).Resize(Rows.Count - 1).Address(0, 0) & "," & toshname & "!A2:A201)"

Bob, I am currently using wsactive as activesheet, strategy. Since I
didnt know your forgotten line earlier, I used Sheetnum as a variable
in for loop i.e. for SheetNum 4 to 255..

After seeing SheetNum = Val(Replace(mySheet, "Sheet", "")), I think
this would solve my problem in an efficient way. So, please let me
know how to declare mySheet. Thanks


On Nov 10, 7:11 am, "Bob Phillips" wrote:
I forgot the line that said

SheetNum = Val(Replace(mySheet, "Sheet", ""))

but I assume that you worked that out as you got it working.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message

ups.com...



On Nov 9, 3:47 pm, "Bob Phillips" wrote:
Assuming that the test is on a variable called mySheet

Formula1 = "=min(Sheet1!" & Columns(SheetNum - 2).Address(0, 0) & ")"
Formula2 = "=max(Sheet1!" & Columns(SheetNum - 2).Address(0, 0) & ")"
Formula3 = "=frequency(Sheet1!" & _
Cells(2, SheetNum - 2).Resize(Rows.Count - 1).Address(0,
0) &
_
toshname & ",!A2:A201)"

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

roups.com...

Modifying every formula in the code given below is too time-consuming.
Is there any way to simplify this code? I would really appreciate if
anyone can help me in this. Thanks

Case "Sheet3"
formula1 = "=min(Sheet1!A:A)"
formula2 = "=max(Sheet1!A:A)"
formula3 = "=frequency(Sheet1!A2:A65536," & toshname & "!
A2:A201)"
Case "Sheet4"
formula1 = "=min(Sheet1!B:B)"
formula2 = "=max(Sheet1!B:B)"
formula3 = "=frequency(Sheet1!B2:B65536," & toshname & "!
A2:A201)"
Case "Sheet5"
formula1 = "=min(Sheet1!C:C)"
formula2 = "=max(Sheet1!C:C)"
formula3 = "=frequency(Sheet1!C2:C65536," & toshname & "!
A2:A201)"
Case "Sheet6"
formula1 = "=min(Sheet1!D:D)"
formula2 = "=max(Sheet1!D:D)"
formula3 = "=frequency(Sheet1!D2:D65536," & toshname & "!
A2:A201)"
Case "Sheet7"
formula1 = "=min(Sheet1!E:E)"
formula2 = "=max(Sheet1!E:E)"
formula3 = "=frequency(Sheet1!E2:E65536," & toshname & "!
A2:A201)"
Case "Sheet8"
formula1 = "=min(Sheet1!F:F)"
formula2 = "=max(Sheet1!F:F)"
formula3 = "=frequency(Sheet1!F2:F65536," & toshname & "!
A2:A201)"
Case "Sheet9"
formula1 = "=min(Sheet1!G:G)"
formula2 = "=max(Sheet1!G:G)"
formula3 = "=frequency(Sheet1!G2:G65536," & toshname & "!
A2:A201)"
Case "Sheet10"
formula1 = "=min(Sheet1!H:H)"
formula2 = "=max(Sheet1!H:H)"
formula3 = "=frequency(Sheet1!H2:H65536," & toshname & "!
A2:A201)"
Case "Sheet11"
formula1 = "=min(Sheet1!I:I)"
formula2 = "=max(Sheet1!I:I)"
formula3 = "=frequency(Sheet1!I2:I65536," & toshname & "!
A2:A201)"
.............
..................
...................... Case "Sheet255"- Hide quoted text -

- Show quoted text -

Bob, Thanks a million.. You helped me to save atleast 4-5 hours of
monotonous work.
I made a minor change to formula3 to make things working. There was ,
(comma) which was misplaced

formula3 = "=frequency(Sheet1!" & Cells(2, sheetnum -
2).Resize(Rows.Count - 1).Address(0, 0) & "," & toshname & "!A2:A201)"- Hide quoted text -

- Show quoted text -

In your original code, you had a Select Case statement. That Select Case
would have to work on some value, such as mySheet, which would have been set
somewhere earlier.

You didn't include the Select Case statement, so I didn't know what you were
using a case on, so I used a variable, the mySheet in this case.

I was also working on the assumption that your code was getting a sheet name
from somewhere and was going to act upon that name. IF ... you need to
process all 251 sheets, I think your method is as good as it can be.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
oups.com...
Bob, I am currently using wsactive as activesheet, strategy. Since I
didnt know your forgotten line earlier, I used Sheetnum as a variable
in for loop i.e. for SheetNum 4 to 255..

After seeing SheetNum = Val(Replace(mySheet, "Sheet", "")), I think
this would solve my problem in an efficient way. So, please let me
know how to declare mySheet. Thanks


On Nov 10, 7:11 am, "Bob Phillips" wrote:
I forgot the line that said

SheetNum = Val(Replace(mySheet, "Sheet", ""))

but I assume that you worked that out as you got it working.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

ups.com...



On Nov 9, 3:47 pm, "Bob Phillips" wrote:
Assuming that the test is on a variable called mySheet

Formula1 = "=min(Sheet1!" & Columns(SheetNum - 2).Address(0, 0) &
")"
Formula2 = "=max(Sheet1!" & Columns(SheetNum - 2).Address(0, 0) &
")"
Formula3 = "=frequency(Sheet1!" & _
Cells(2, SheetNum - 2).Resize(Rows.Count -
1).Address(0,
0) &
_
toshname & ",!A2:A201)"

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

roups.com...

Modifying every formula in the code given below is too
time-consuming.
Is there any way to simplify this code? I would really appreciate if
anyone can help me in this. Thanks

Case "Sheet3"
formula1 = "=min(Sheet1!A:A)"
formula2 = "=max(Sheet1!A:A)"
formula3 = "=frequency(Sheet1!A2:A65536," & toshname & "!
A2:A201)"
Case "Sheet4"
formula1 = "=min(Sheet1!B:B)"
formula2 = "=max(Sheet1!B:B)"
formula3 = "=frequency(Sheet1!B2:B65536," & toshname & "!
A2:A201)"
Case "Sheet5"
formula1 = "=min(Sheet1!C:C)"
formula2 = "=max(Sheet1!C:C)"
formula3 = "=frequency(Sheet1!C2:C65536," & toshname & "!
A2:A201)"
Case "Sheet6"
formula1 = "=min(Sheet1!D:D)"
formula2 = "=max(Sheet1!D:D)"
formula3 = "=frequency(Sheet1!D2:D65536," & toshname & "!
A2:A201)"
Case "Sheet7"
formula1 = "=min(Sheet1!E:E)"
formula2 = "=max(Sheet1!E:E)"
formula3 = "=frequency(Sheet1!E2:E65536," & toshname & "!
A2:A201)"
Case "Sheet8"
formula1 = "=min(Sheet1!F:F)"
formula2 = "=max(Sheet1!F:F)"
formula3 = "=frequency(Sheet1!F2:F65536," & toshname & "!
A2:A201)"
Case "Sheet9"
formula1 = "=min(Sheet1!G:G)"
formula2 = "=max(Sheet1!G:G)"
formula3 = "=frequency(Sheet1!G2:G65536," & toshname & "!
A2:A201)"
Case "Sheet10"
formula1 = "=min(Sheet1!H:H)"
formula2 = "=max(Sheet1!H:H)"
formula3 = "=frequency(Sheet1!H2:H65536," & toshname & "!
A2:A201)"
Case "Sheet11"
formula1 = "=min(Sheet1!I:I)"
formula2 = "=max(Sheet1!I:I)"
formula3 = "=frequency(Sheet1!I2:I65536," & toshname & "!
A2:A201)"
.............
..................
...................... Case "Sheet255"- Hide quoted text -

- Show quoted text -

Bob, Thanks a million.. You helped me to save atleast 4-5 hours of
monotonous work.
I made a minor change to formula3 to make things working. There was ,
(comma) which was misplaced

formula3 = "=frequency(Sheet1!" & Cells(2, sheetnum -
2).Resize(Rows.Count - 1).Address(0, 0) & "," & toshname & "!A2:A201)"-
Hide quoted text -

- Show quoted text -

On Nov 10, 2:20 pm, "Bob Phillips" wrote:
In your original code, you had a Select Case statement. That Select Case
would have to work on some value, such as mySheet, which would have been set
somewhere earlier.

You didn't include the Select Case statement, so I didn't know what you were
using a case on, so I used a variable, the mySheet in this case.

I was also working on the assumption that your code was getting a sheet name
from somewhere and was going to act upon that name. IF ... you need to
process all 251 sheets, I think your method is as good as it can be.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message

oups.com...



Bob, I am currently using wsactive as activesheet, strategy. Since I
didnt know your forgotten line earlier, I used Sheetnum as a variable
in for loop i.e. for SheetNum 4 to 255..

After seeing SheetNum = Val(Replace(mySheet, "Sheet", "")), I think
this would solve my problem in an efficient way. So, please let me
know how to declare mySheet. Thanks

On Nov 10, 7:11 am, "Bob Phillips" wrote:
I forgot the line that said

SheetNum = Val(Replace(mySheet, "Sheet", ""))

but I assume that you worked that out as you got it working.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

roups.com...

On Nov 9, 3:47 pm, "Bob Phillips" wrote:
Assuming that the test is on a variable called mySheet

Formula1 = "=min(Sheet1!" & Columns(SheetNum - 2).Address(0, 0) &
")"
Formula2 = "=max(Sheet1!" & Columns(SheetNum - 2).Address(0, 0) &
")"
Formula3 = "=frequency(Sheet1!" & _
Cells(2, SheetNum - 2).Resize(Rows.Count -
1).Address(0,
0) &
_
toshname & ",!A2:A201)"

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

roups.com...

Modifying every formula in the code given below is too
time-consuming.
Is there any way to simplify this code? I would really appreciate if
anyone can help me in this. Thanks

Case "Sheet3"
formula1 = "=min(Sheet1!A:A)"
formula2 = "=max(Sheet1!A:A)"
formula3 = "=frequency(Sheet1!A2:A65536," & toshname & "!
A2:A201)"
Case "Sheet4"
formula1 = "=min(Sheet1!B:B)"
formula2 = "=max(Sheet1!B:B)"
formula3 = "=frequency(Sheet1!B2:B65536," & toshname & "!
A2:A201)"
Case "Sheet5"
formula1 = "=min(Sheet1!C:C)"
formula2 = "=max(Sheet1!C:C)"
formula3 = "=frequency(Sheet1!C2:C65536," & toshname & "!
A2:A201)"
Case "Sheet6"
formula1 = "=min(Sheet1!D:D)"
formula2 = "=max(Sheet1!D:D)"
formula3 = "=frequency(Sheet1!D2:D65536," & toshname & "!
A2:A201)"
Case "Sheet7"
formula1 = "=min(Sheet1!E:E)"
formula2 = "=max(Sheet1!E:E)"
formula3 = "=frequency(Sheet1!E2:E65536," & toshname & "!
A2:A201)"
Case "Sheet8"
formula1 = "=min(Sheet1!F:F)"
formula2 = "=max(Sheet1!F:F)"
formula3 = "=frequency(Sheet1!F2:F65536," & toshname & "!
A2:A201)"
Case "Sheet9"
formula1 = "=min(Sheet1!G:G)"
formula2 = "=max(Sheet1!G:G)"
formula3 = "=frequency(Sheet1!G2:G65536," & toshname & "!
A2:A201)"
Case "Sheet10"
formula1 = "=min(Sheet1!H:H)"
formula2 = "=max(Sheet1!H:H)"
formula3 = "=frequency(Sheet1!H2:H65536," & toshname & "!
A2:A201)"
Case "Sheet11"
formula1 = "=min(Sheet1!I:I)"
formula2 = "=max(Sheet1!I:I)"
formula3 = "=frequency(Sheet1!I2:I65536," & toshname & "!
A2:A201)"
.............
..................
...................... Case "Sheet255"- Hide quoted text -

- Show quoted text -

Bob, Thanks a million.. You helped me to save atleast 4-5 hours of
monotonous work.
I made a minor change to formula3 to make things working. There was ,
(comma) which was misplaced

formula3 = "=frequency(Sheet1!" & Cells(2, sheetnum -
2).Resize(Rows.Count - 1).Address(0, 0) & "," & toshname & "!A2:A201)"-
Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Bob, your reply made me to re-thin the procedure and I am happy that I
finally got what I wanted.

Initial code:

Dim wsactive As Worksheet
Set wsactive = ActiveSheet

select case wscative.name

case sheet3....
.............

Implementing Bob's code:

Dim wsactive As Worksheet
Set wsactive = ActiveSheet
Dim sheetnum As Integer
mySheet = wsactive.Name
'MsgBox mySheet
sheetnum = Val(Replace(mySheet, "Sheet", ""))
'MsgBox sheetnum
select case mySheet
case sheet3
..........
............

Thanks Bob.

Glad you got there mate, and even mores so that you worked it through
yourself, a much better learning experience <bg

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
oups.com...
On Nov 10, 2:20 pm, "Bob Phillips" wrote:
In your original code, you had a Select Case statement. That Select Case
would have to work on some value, such as mySheet, which would have been
set
somewhere earlier.

You didn't include the Select Case statement, so I didn't know what you
were
using a case on, so I used a variable, the mySheet in this case.

I was also working on the assumption that your code was getting a sheet
name
from somewhere and was going to act upon that name. IF ... you need to
process all 251 sheets, I think your method is as good as it can be.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

oups.com...



Bob, I am currently using wsactive as activesheet, strategy. Since I
didnt know your forgotten line earlier, I used Sheetnum as a variable
in for loop i.e. for SheetNum 4 to 255..

After seeing SheetNum = Val(Replace(mySheet, "Sheet", "")), I think
this would solve my problem in an efficient way. So, please let me
know how to declare mySheet. Thanks

On Nov 10, 7:11 am, "Bob Phillips" wrote:
I forgot the line that said

SheetNum = Val(Replace(mySheet, "Sheet", ""))

but I assume that you worked that out as you got it working.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

roups.com...

On Nov 9, 3:47 pm, "Bob Phillips" wrote:
Assuming that the test is on a variable called mySheet

Formula1 = "=min(Sheet1!" & Columns(SheetNum - 2).Address(0, 0)
&
")"
Formula2 = "=max(Sheet1!" & Columns(SheetNum - 2).Address(0, 0)
&
")"
Formula3 = "=frequency(Sheet1!" & _
Cells(2, SheetNum - 2).Resize(Rows.Count -
1).Address(0,
0) &
_
toshname & ",!A2:A201)"

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

wrote in message

roups.com...

Modifying every formula in the code given below is too
time-consuming.
Is there any way to simplify this code? I would really appreciate
if
anyone can help me in this. Thanks

Case "Sheet3"
formula1 = "=min(Sheet1!A:A)"
formula2 = "=max(Sheet1!A:A)"
formula3 = "=frequency(Sheet1!A2:A65536," & toshname & "!
A2:A201)"
Case "Sheet4"
formula1 = "=min(Sheet1!B:B)"
formula2 = "=max(Sheet1!B:B)"
formula3 = "=frequency(Sheet1!B2:B65536," & toshname & "!
A2:A201)"
Case "Sheet5"
formula1 = "=min(Sheet1!C:C)"
formula2 = "=max(Sheet1!C:C)"
formula3 = "=frequency(Sheet1!C2:C65536," & toshname & "!
A2:A201)"
Case "Sheet6"
formula1 = "=min(Sheet1!D:D)"
formula2 = "=max(Sheet1!D:D)"
formula3 = "=frequency(Sheet1!D2:D65536," & toshname & "!
A2:A201)"
Case "Sheet7"
formula1 = "=min(Sheet1!E:E)"
formula2 = "=max(Sheet1!E:E)"
formula3 = "=frequency(Sheet1!E2:E65536," & toshname & "!
A2:A201)"
Case "Sheet8"
formula1 = "=min(Sheet1!F:F)"
formula2 = "=max(Sheet1!F:F)"
formula3 = "=frequency(Sheet1!F2:F65536," & toshname & "!
A2:A201)"
Case "Sheet9"
formula1 = "=min(Sheet1!G:G)"
formula2 = "=max(Sheet1!G:G)"
formula3 = "=frequency(Sheet1!G2:G65536," & toshname & "!
A2:A201)"
Case "Sheet10"
formula1 = "=min(Sheet1!H:H)"
formula2 = "=max(Sheet1!H:H)"
formula3 = "=frequency(Sheet1!H2:H65536," & toshname & "!
A2:A201)"
Case "Sheet11"
formula1 = "=min(Sheet1!I:I)"
formula2 = "=max(Sheet1!I:I)"
formula3 = "=frequency(Sheet1!I2:I65536," & toshname & "!
A2:A201)"
.............
..................
...................... Case "Sheet255"- Hide quoted text -

- Show quoted text -

Bob, Thanks a million.. You helped me to save atleast 4-5 hours of
monotonous work.
I made a minor change to formula3 to make things working. There was
,
(comma) which was misplaced

formula3 = "=frequency(Sheet1!" & Cells(2, sheetnum -
2).Resize(Rows.Count - 1).Address(0, 0) & "," & toshname &
"!A2:A201)"-
Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Bob, your reply made me to re-thin the procedure and I am happy that I
finally got what I wanted.

Initial code:

Dim wsactive As Worksheet
Set wsactive = ActiveSheet

select case wscative.name

case sheet3....
............

Implementing Bob's code:

Dim wsactive As Worksheet
Set wsactive = ActiveSheet
Dim sheetnum As Integer
mySheet = wsactive.Name
'MsgBox mySheet
sheetnum = Val(Replace(mySheet, "Sheet", ""))
'MsgBox sheetnum
select case mySheet
case sheet3
.........
...........

Thanks Bob.

Yes Bob. I agree with you. Thank you again

On Nov 11, 3:25 pm, "Bob Phillips" wrote:
Glad you got there mate, and even mores so that you worked it through
yourself, a much better learning experience <bg

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message

oups.com...



On Nov 10, 2:20 pm, "Bob Phillips" wrote:
In your original code, you had a Select Case statement. That Select Case
would have to work on some value, such as mySheet, which would have been
set
somewhere earlier.

You didn't include the Select Case statement, so I didn't know what you
were
using a case on, so I used a variable, the mySheet in this case.

I was also working on the assumption that your code was getting a sheet
name
from somewhere and was going to act upon that name. IF ... you need to
process all 251 sheets, I think your method is as good as it can be.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

groups.com...

Bob, I am currently using wsactive as activesheet, strategy. Since I
didnt know your forgotten line earlier, I used Sheetnum as a variable
in for loop i.e. for SheetNum 4 to 255..

After seeing SheetNum = Val(Replace(mySheet, "Sheet", "")), I think
this would solve my problem in an efficient way. So, please let me
know how to declare mySheet. Thanks

On Nov 10, 7:11 am, "Bob Phillips" wrote:
I forgot the line that said

SheetNum = Val(Replace(mySheet, "Sheet", ""))

but I assume that you worked that out as you got it working.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

roups.com...

On Nov 9, 3:47 pm, "Bob Phillips" wrote:
Assuming that the test is on a variable called mySheet

Formula1 = "=min(Sheet1!" & Columns(SheetNum - 2).Address(0, 0)
&
")"
Formula2 = "=max(Sheet1!" & Columns(SheetNum - 2).Address(0, 0)
&
")"
Formula3 = "=frequency(Sheet1!" & _
Cells(2, SheetNum - 2).Resize(Rows.Count -
1).Address(0,
0) &
_
toshname & ",!A2:A201)"

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

wrote in message

roups.com...

Modifying every formula in the code given below is too
time-consuming.
Is there any way to simplify this code? I would really appreciate
if
anyone can help me in this. Thanks

Case "Sheet3"
formula1 = "=min(Sheet1!A:A)"
formula2 = "=max(Sheet1!A:A)"
formula3 = "=frequency(Sheet1!A2:A65536," & toshname & "!
A2:A201)"
Case "Sheet4"
formula1 = "=min(Sheet1!B:B)"
formula2 = "=max(Sheet1!B:B)"
formula3 = "=frequency(Sheet1!B2:B65536," & toshname & "!
A2:A201)"
Case "Sheet5"
formula1 = "=min(Sheet1!C:C)"
formula2 = "=max(Sheet1!C:C)"
formula3 = "=frequency(Sheet1!C2:C65536," & toshname & "!
A2:A201)"
Case "Sheet6"
formula1 = "=min(Sheet1!D:D)"
formula2 = "=max(Sheet1!D:D)"
formula3 = "=frequency(Sheet1!D2:D65536," & toshname & "!
A2:A201)"
Case "Sheet7"
formula1 = "=min(Sheet1!E:E)"
formula2 = "=max(Sheet1!E:E)"
formula3 = "=frequency(Sheet1!E2:E65536," & toshname & "!
A2:A201)"
Case "Sheet8"
formula1 = "=min(Sheet1!F:F)"
formula2 = "=max(Sheet1!F:F)"
formula3 = "=frequency(Sheet1!F2:F65536," & toshname & "!
A2:A201)"
Case "Sheet9"
formula1 = "=min(Sheet1!G:G)"
formula2 = "=max(Sheet1!G:G)"
formula3 = "=frequency(Sheet1!G2:G65536," & toshname & "!
A2:A201)"
Case "Sheet10"
formula1 = "=min(Sheet1!H:H)"
formula2 = "=max(Sheet1!H:H)"
formula3 = "=frequency(Sheet1!H2:H65536," & toshname & "!
A2:A201)"
Case "Sheet11"
formula1 = "=min(Sheet1!I:I)"
formula2 = "=max(Sheet1!I:I)"
formula3 = "=frequency(Sheet1!I2:I65536," & toshname & "!
A2:A201)"
.............
..................
...................... Case "Sheet255"- Hide quoted text -

- Show quoted text -

Bob, Thanks a million.. You helped me to save atleast 4-5 hours of
monotonous work.
I made a minor change to formula3 to make things working. There was
,
(comma) which was misplaced

formula3 = "=frequency(Sheet1!" & Cells(2, sheetnum -
2).Resize(Rows.Count - 1).Address(0, 0) & "," & toshname &
"!A2:A201)"-
Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Bob, your reply made me to re-thin the procedure and I am happy that I
finally got what I wanted.

Initial code:

Dim wsactive As Worksheet
Set wsactive = ActiveSheet

select case wscative.name

case sheet3....
............

Implementing Bob's code:

Dim wsactive As Worksheet
Set wsactive = ActiveSheet
Dim sheetnum As Integer
mySheet = wsactive.Name
'MsgBox mySheet
sheetnum = Val(Replace(mySheet, "Sheet", ""))
'MsgBox sheetnum
select case mySheet
case sheet3
.........
...........

Thanks Bob.- Hide quoted text -

- Show quoted text -

On Nov 11, 10:56 pm, wrote:
YesBob. I agree with you. Thank you again

On Nov 11, 3:25 pm, "BobPhillips" wrote:



Glad you got there mate, and even mores so that you worked it through
yourself, a much better learning experience <bg

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message

roups.com...

On Nov 10, 2:20 pm, "BobPhillips" wrote:
In your original code, you had a Select Case statement. That Select Case
would have to work on some value, such as mySheet, which would have been
set
somewhere earlier.

You didn't include the Select Case statement, so I didn't know what you
were
using a case on, so I used a variable, the mySheet in this case.

I was also working on the assumption that your code was getting a sheet
name
from somewhere and was going to act upon that name. IF ... you need to
process all 251 sheets, I think your method is as good as it can be.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

groups.com...

Bob, I am currently using wsactive as activesheet, strategy. Since I
didnt know your forgotten line earlier, I used Sheetnum as a variable
in for loop i.e. for SheetNum 4 to 255..

After seeing SheetNum = Val(Replace(mySheet, "Sheet", "")), I think
this would solve my problem in an efficient way. So, please let me
know how to declare mySheet. Thanks

On Nov 10, 7:11 am, "BobPhillips" wrote:
I forgot the line that said

SheetNum = Val(Replace(mySheet, "Sheet", ""))

but I assume that you worked that out as you got it working.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

wrote in message

roups.com...

On Nov 9, 3:47 pm, "BobPhillips" wrote:
Assuming that the test is on a variable called mySheet

Formula1 = "=min(Sheet1!" & Columns(SheetNum - 2).Address(0, 0)
&
")"
Formula2 = "=max(Sheet1!" & Columns(SheetNum - 2).Address(0, 0)
&
")"
Formula3 = "=frequency(Sheet1!" & _
Cells(2, SheetNum - 2).Resize(Rows.Count -
1).Address(0,
0) &
_
toshname & ",!A2:A201)"

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

wrote in message

roups.com...

Modifying every formula in the code given below is too
time-consuming.
Is there any way to simplify this code? I would really appreciate
if
anyone can help me in this. Thanks

Case "Sheet3"
formula1 = "=min(Sheet1!A:A)"
formula2 = "=max(Sheet1!A:A)"
formula3 = "=frequency(Sheet1!A2:A65536," & toshname & "!
A2:A201)"
Case "Sheet4"
formula1 = "=min(Sheet1!B:B)"
formula2 = "=max(Sheet1!B:B)"
formula3 = "=frequency(Sheet1!B2:B65536," & toshname & "!
A2:A201)"
Case "Sheet5"
formula1 = "=min(Sheet1!C:C)"
formula2 = "=max(Sheet1!C:C)"
formula3 = "=frequency(Sheet1!C2:C65536," & toshname & "!
A2:A201)"
Case "Sheet6"
formula1 = "=min(Sheet1!D:D)"
formula2 = "=max(Sheet1!D:D)"
formula3 = "=frequency(Sheet1!D2:D65536," & toshname & "!
A2:A201)"
Case "Sheet7"
formula1 = "=min(Sheet1!E:E)"
formula2 = "=max(Sheet1!E:E)"
formula3 = "=frequency(Sheet1!E2:E65536," & toshname & "!
A2:A201)"
Case "Sheet8"
formula1 = "=min(Sheet1!F:F)"
formula2 = "=max(Sheet1!F:F)"
formula3 = "=frequency(Sheet1!F2:F65536," & toshname & "!
A2:A201)"
Case "Sheet9"
formula1 = "=min(Sheet1!G:G)"
formula2 = "=max(Sheet1!G:G)"
formula3 = "=frequency(Sheet1!G2:G65536," & toshname & "!
A2:A201)"
Case "Sheet10"
formula1 = "=min(Sheet1!H:H)"
formula2 = "=max(Sheet1!H:H)"
formula3 = "=frequency(Sheet1!H2:H65536," & toshname & "!
A2:A201)"
Case "Sheet11"
formula1 = "=min(Sheet1!I:I)"
formula2 = "=max(Sheet1!I:I)"
formula3 = "=frequency(Sheet1!I2:I65536," & toshname & "!
A2:A201)"
.............
..................
...................... Case "Sheet255"- Hide quoted text -

- Show quoted text -

Bob, Thanks a million.. You helped me to save atleast 4-5 hours of
monotonous work.
I made a minor change to formula3 to make things working. There was
,
(comma) which was misplaced

formula3 = "=frequency(Sheet1!" & Cells(2, sheetnum -
2).Resize(Rows.Count - 1).Address(0, 0) & "," & toshname &
"!A2:A201)"-
Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Bob, your reply made me to re-thin the procedure and I am happy that I
finally got what I wanted.

Initial code:

Dim wsactive As Worksheet
Set wsactive = ActiveSheet

select case wscative.name

case sheet3....
............

ImplementingBob'scode:

Dim wsactive As Worksheet
Set wsactive = ActiveSheet
Dim sheetnum As Integer
mySheet = wsactive.Name
'MsgBox mySheet
sheetnum = Val(Replace(mySheet, "Sheet", ""))
'MsgBox sheetnum
select case mySheet
case sheet3
.........
...........

ThanksBob.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Hi Bob,

I am trying to achieve
=Sheet1!A1 for sheet3,
=Sheet1!B1 for sheet4,
=Sheet1!C1 for sheet5.
etc.........

I am using the formula given below to get what I want. Is this correct
way of doing it? Please clarify.

Dim a as string
Dim sheetnum as integer

a = "=Sheet1!" & Cells(1, sheetnum - 2).Resize(Rows.Count -
65535).Address(0, 0)