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 -