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)"