 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / November 2007Please help me simplify the given code
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" 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)"  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > 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 [quoted text clipped - 48 lines] > .................. > ...................... Case "Sheet255" > Assuming that the test is on a variable called mySheet > [quoted text clipped - 71 lines] > > - 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)" I forgot the line that said SheetNum = Val(Replace(mySheet, "Sheet", "")) but I assume that you worked that out as you got it working. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> Assuming that the test is on a variable called mySheet >> [quoted text clipped - 81 lines] > formula3 = "=frequency(Sheet1!" & Cells(2, sheetnum - > 2).Resize(Rows.Count - 1).Address(0, 0) & "," & toshname & "!A2:A201)" 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 > I forgot the line that said > [quoted text clipped - 101 lines] > > - Show quoted text - 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. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Bob, I am currently using wsactive as activesheet, strategy. Since I > didnt know your forgotten line earlier, I used Sheetnum as a variable [quoted text clipped - 115 lines] >> >> - Show quoted text - > 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 [quoted text clipped - 140 lines] > > - 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. Glad you got there mate, and even mores so that you worked it through yourself, a much better learning experience <bg> Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> 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 [quoted text clipped - 179 lines] > > Thanks Bob. 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> [quoted text clipped - 196 lines] > > - Show quoted text - On Nov 11, 10:56 pm, ucanalw...@gmail.com wrote: > YesBob. I agree with you. Thank you again > [quoted text clipped - 205 lines] > > - 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) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.