|
Please help me simplify the given code
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)" |
Please help me simplify the given code
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 - |
Please help me simplify the given code
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 - |
Please help me simplify the given code
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. |
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. |
Please help me simplify the given code
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 - |
Please help me simplify the given code
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) |
| All times are GMT +1. The time now is 11:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com