Please help me simplify the given code
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.
|