Please help me simplify the given code

[ucanalways]

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)"
formula2 = "=max(Sheet1!D)"
formula3 = "=frequency(Sheet1!D265536," & 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"

 

[Bob Phillips]

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)

 

[ucanalways]

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)







- 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 Phillips]

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)

 

[ucanalways]

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

 

[Bob Phillips]

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)

 

[ucanalways]

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)








- 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.

 

[Bob Phillips]

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)

 

[ucanalways]

Yes Bob. I agree with you. Thank you again

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)














- Show quoted text -

 

[ucanalways]

YesBob. I agree with you. Thank you again




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