RE: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible
between calls, and reinitializing them if they are not initialized yet. Global variables are usually used sparingly in VBA. Also, if you are initializing variables that will be used later multiple times, you should initialize them to the greatest extent possible. If your purpose here is to get references to specific cells to access their values or properties, you should ordinarily use a Range object and create a reference to the range instead of just saving the index. I'd recommend a specific code design, but without knowing the overall intent of your code, I can't give good advice. Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Tuesday, March 27, 2012 4:54 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible Thanks. Believe me I am a firm believer in KISS. I did not realize the . made that distinction though. Also like getting rid of worksheetfunction. I went the other way and tried to get rid of application and that failed. I thought it was the redundant one not worksheetfunction. So for cross referencing can I create a reference Set pointer = sheets(chartvalue) then use pointer.cells(row,col) for access since what I will be doing is reading cell values from one sheet, performing functions on the values then setting cell values in the other sheet. Or is there more to it than that? On Tue, Mar 27, 2012 at 4:42 PM, dguillett1 dguille...@gmail.com wrote: Sub InitRefs() dim fv As long dim lv As long With Sheets(“ChartData”) fv= .Application.Match(Stopval, .Rows(1), 0) ‘notice the dot (.) before rows End With With Sheets(“Results”) lv = .Application..Match(ReverseDate, .Rows(1), 0) ‘dot NOT needed on ACTIVE sheet but needed here. End With End Sub Try to adhere to the KISS principle Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: tangledweb Sent: Tuesday, March 27, 2012 5:50 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible I have one sheet of raw data with several columns and thousands of rows. I need to process this and I wanted to put the results on a separate sheet with several columns and dozens of rows when done. I saw some example code where it seemed you could do this as long as you qualified the sheet path but doing what I think is the same thing is not working. Also I would like to minimize the full paths I have to use or minimize activations when going back and forth from one sheet to the other to get data, process, then store data which will have to be done thousands and thousands of times. So part of the question is can you set path references that can be used to minimize the typing volume and readability? But first I need to know why I can not get this most basic initialization to work If I interactively select sheet ChartData (it is not an excel chart just a name) then the first initialization works but not the one for Results and visa versa. I have stripped out unnecessary columns for simplicity. I tried both with and without activating sheet Results but get the error Unable to get results of the match property class for the ReverseDate assignment line no matter what I do. Also tried the names with and without quotes to no difference. If in the interface I select sheet Results then the error occurs on the Stopval assignment line. ' Column reference objects Public Stopval As Double ' Public ReverseDate As Double ' Sheet names Const ChartData As String = ChartData Const Results As String = Results Sub InitRefs() ' ' Macro which sets the data column names and misc values for the sheets ' With Sheets(ChartData) Stopval = .Application.WorksheetFunction.Match(Stopval, Rows(1:1), 0) End With ' Sheets(Results).Activate ' With Sheets(Results) ' ReverseDate = .Application.WorksheetFunction.Match(ReverseDate, Rows(1:1), 0) ' End With End Sub -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel
Re: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible
by an environment that has different error handling and many more datatypes than a worksheet does. So... my reccommended revision to the key lines of code is: fv = WorkSheetFunction.Match(Stopval, .Rows(1), 0) lv = WorkSheetFunction.Match(ReverseDate, .Rows(1), 0) Most significantly, notice the absence of a leading .. . . . It looks like you were declaring your variables outside the procedure so that they would be available to multiple procedures during the time that your workbook is open? If I am correct in that guess, you should be aware that various events outside of your control can cause VBA to lose state and your variables could be reset at anytime. So, doing what I think you're doing can complicate things, because if you do that, you should check that your variables are initialized at the start of entry procedures (such as event and key-assigned macro procedures) that might refer to them. Perhaps by calling the initialization routine at the beginning of those procedures, and in the initialization routine itself, check perhaps that just one value has been initialized, and if so, skip the rest of the routine, assuming that they all have... but in most cases this is unneccessarily complex. There are other methods to avoid recalculation, such as waiting to calculate values until needed and passing the calculated values as parameters to routines that require them, although that doesn't help a lot in rapid-fire events. But for that case, don't worry unless you find that performing your calculation is time consuming enough to cause a performance problem. If it is, you can consider declaring those variables locally in that procedure instead of globally, using the Static keyword so that they retain values between calls, and reinitializing them if they are not initialized yet. Global variables are usually used sparingly in VBA. Also, if you are initializing variables that will be used later multiple times, you should initialize them to the greatest extent possible. If your purpose here is to get references to specific cells to access their values or properties, you should ordinarily use a Range object and create a reference to the range instead of just saving the index. I'd recommend a specific code design, but without knowing the overall intent of your code, I can't give good advice. Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Tuesday, March 27, 2012 4:54 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible Thanks. Believe me I am a firm believer in KISS. I did not realize the . made that distinction though. Also like getting rid of worksheetfunction. I went the other way and tried to get rid of application and that failed. I thought it was the redundant one not worksheetfunction. So for cross referencing can I create a reference Set pointer = sheets(chartvalue) then use pointer.cells(row,col) for access since what I will be doing is reading cell values from one sheet, performing functions on the values then setting cell values in the other sheet. Or is there more to it than that? On Tue, Mar 27, 2012 at 4:42 PM, dguillett1 dguille...@gmail.com wrote: Sub InitRefs() dim fv As long dim lv As long With Sheets(“ChartData”) fv= .Application.Match(Stopval, .Rows(1), 0) ‘notice the dot (.) before rows End With With Sheets(“Results”) lv = .Application..Match(ReverseDate, .Rows(1), 0) ‘dot NOT needed on ACTIVE sheet but needed here. End With End Sub Try to adhere to the KISS principle Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: tangledweb Sent: Tuesday, March 27, 2012 5:50 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible I have one sheet of raw data with several columns and thousands of rows. I need to process this and I wanted to put the results on a separate sheet with several columns and dozens of rows when done. I saw some example code where it seemed you could do this as long as you qualified the sheet path but doing what I think is the same thing is not working. Also I would like to minimize the full paths I have to use or minimize activations when going back and forth from one sheet to the other to get data, process, then store data which will have to be done thousands and thousands of times. So part of the question is can you set path references that can be used to minimize the typing volume and readability? But first I need to know why I can not get this most basic initialization to work If I interactively select sheet ChartData (it is not an excel chart just a name) then the first initialization works but not the one for Results and visa versa. I have stripped out unnecessary columns
Re: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible
of the Application object. Anything that's undocumented is not guarnateed to do what you think it does, and not guaranteed to work in future versions of Excel. (2) I've verified (though not the discoverer) that at least one worksheetfunction method -- RoundDown -- gives incorrect results in some circumstances when referred to as Application.RoundDown (3) Error handling is different when you abbreviate worksheetfunction methods to Application.(function). Specifically, an error is not generated, although an error code is in the return value from the function. (4) between reason number 2 and 3, my theory is that the Application version is the version used from worksheet formulas, and the WorksheetFunction version is the only version intended to be used from VBA, and behaves differently as required by an environment that has different error handling and many more datatypes than a worksheet does. So... my reccommended revision to the key lines of code is: fv = WorkSheetFunction.Match(Stopval, .Rows(1), 0) lv = WorkSheetFunction.Match(ReverseDate, .Rows(1), 0) Most significantly, notice the absence of a leading .. . . . It looks like you were declaring your variables outside the procedure so that they would be available to multiple procedures during the time that your workbook is open? If I am correct in that guess, you should be aware that various events outside of your control can cause VBA to lose state and your variables could be reset at anytime. So, doing what I think you're doing can complicate things, because if you do that, you should check that your variables are initialized at the start of entry procedures (such as event and key-assigned macro procedures) that might refer to them. Perhaps by calling the initialization routine at the beginning of those procedures, and in the initialization routine itself, check perhaps that just one value has been initialized, and if so, skip the rest of the routine, assuming that they all have... but in most cases this is unneccessarily complex. There are other methods to avoid recalculation, such as waiting to calculate values until needed and passing the calculated values as parameters to routines that require them, although that doesn't help a lot in rapid-fire events. But for that case, don't worry unless you find that performing your calculation is time consuming enough to cause a performance problem. If it is, you can consider declaring those variables locally in that procedure instead of globally, using the Static keyword so that they retain values between calls, and reinitializing them if they are not initialized yet. Global variables are usually used sparingly in VBA. Also, if you are initializing variables that will be used later multiple times, you should initialize them to the greatest extent possible. If your purpose here is to get references to specific cells to access their values or properties, you should ordinarily use a Range object and create a reference to the range instead of just saving the index. I'd recommend a specific code design, but without knowing the overall intent of your code, I can't give good advice. Asa -Original Message- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Tuesday, March 27, 2012 4:54 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible Thanks. Believe me I am a firm believer in KISS. I did not realize the . made that distinction though. Also like getting rid of worksheetfunction. I went the other way and tried to get rid of application and that failed. I thought it was the redundant one not worksheetfunction. So for cross referencing can I create a reference Set pointer = sheets(chartvalue) then use pointer.cells(row,col) for access since what I will be doing is reading cell values from one sheet, performing functions on the values then setting cell values in the other sheet. Or is there more to it than that? On Tue, Mar 27, 2012 at 4:42 PM, dguillett1 dguille...@gmail.com wrote: Sub InitRefs() dim fv As long dim lv As long With Sheets(“ChartData”) fv= .Application.Match(Stopval, .Rows(1), 0) ‘notice the dot (.) before rows End With With Sheets(“Results”) lv = .Application..Match(ReverseDate, .Rows(1), 0) ‘dot NOT needed on ACTIVE sheet but needed here. End With End Sub Try to adhere to the KISS principle Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: tangledweb Sent: Tuesday, March 27, 2012 5:50 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible I have one sheet of raw data with several columns and thousands of rows. I need to process this and I wanted to put the results on a separate sheet with several columns and dozens of rows when done. I saw
$$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible
I have one sheet of raw data with several columns and thousands of rows. I need to process this and I wanted to put the results on a separate sheet with several columns and dozens of rows when done. I saw some example code where it seemed you could do this as long as you qualified the sheet path but doing what I think is the same thing is not working. Also I would like to minimize the full paths I have to use or minimize activations when going back and forth from one sheet to the other to get data, process, then store data which will have to be done thousands and thousands of times. So part of the question is can you set path references that can be used to minimize the typing volume and readability? But first I need to know why I can not get this most basic initialization to work If I interactively select sheet ChartData (it is not an excel chart just a name) then the first initialization works but not the one for Results and visa versa. I have stripped out unnecessary columns for simplicity. I tried both with and without activating sheet Results but get the error Unable to get results of the match property class for the ReverseDate assignment line no matter what I do. Also tried the names with and without quotes to no difference. If in the interface I select sheet Results then the error occurs on the Stopval assignment line. ' Column reference objects Public Stopval As Double ' Public ReverseDate As Double ' Sheet names Const ChartData As String = ChartData Const Results As String = Results Sub InitRefs() ' ' Macro which sets the data column names and misc values for the sheets ' With Sheets(ChartData) Stopval = .Application.WorksheetFunction.Match(Stopval, Rows(1:1), 0) End With 'Sheets(Results).Activate 'With Sheets(Results) 'ReverseDate = .Application.WorksheetFunction.Match(ReverseDate, Rows(1:1), 0) 'End With End Sub -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible
Sub InitRefs() dim fv As long dim lv As long With Sheets(“ChartData”) fv= .Application.Match(Stopval, .Rows(1), 0) ‘notice the dot (.) before rows End With With Sheets(“Results”) lv = .Application..Match(ReverseDate, .Rows(1), 0) ‘dot NOT needed on ACTIVE sheet but needed here. End With End Sub Try to adhere to the KISS principle Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: tangledweb Sent: Tuesday, March 27, 2012 5:50 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible I have one sheet of raw data with several columns and thousands of rows. I need to process this and I wanted to put the results on a separate sheet with several columns and dozens of rows when done. I saw some example code where it seemed you could do this as long as you qualified the sheet path but doing what I think is the same thing is not working. Also I would like to minimize the full paths I have to use or minimize activations when going back and forth from one sheet to the other to get data, process, then store data which will have to be done thousands and thousands of times. So part of the question is can you set path references that can be used to minimize the typing volume and readability? But first I need to know why I can not get this most basic initialization to work If I interactively select sheet ChartData (it is not an excel chart just a name) then the first initialization works but not the one for Results and visa versa. I have stripped out unnecessary columns for simplicity. I tried both with and without activating sheet Results but get the error Unable to get results of the match property class for the ReverseDate assignment line no matter what I do. Also tried the names with and without quotes to no difference. If in the interface I select sheet Results then the error occurs on the Stopval assignment line. ' Column reference objects Public Stopval As Double ' Public ReverseDate As Double ' Sheet names Const ChartData As String = ChartData Const Results As String = Results Sub InitRefs() ' ' Macro which sets the data column names and misc values for the sheets ' With Sheets(ChartData) Stopval = .Application.WorksheetFunction.Match(Stopval, Rows(1:1), 0) End With 'Sheets(Results).Activate 'With Sheets(Results) 'ReverseDate = .Application.WorksheetFunction.Match(ReverseDate, Rows(1:1), 0) 'End With End Sub -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible
Thanks. Believe me I am a firm believer in KISS. I did not realize the . made that distinction though. Also like getting rid of worksheetfunction. I went the other way and tried to get rid of application and that failed. I thought it was the redundant one not worksheetfunction. So for cross referencing can I create a reference Set pointer = sheets(chartvalue) then use pointer.cells(row,col) for access since what I will be doing is reading cell values from one sheet, performing functions on the values then setting cell values in the other sheet. Or is there more to it than that? On Tue, Mar 27, 2012 at 4:42 PM, dguillett1 dguille...@gmail.com wrote: Sub InitRefs() dim fv As long dim lv As long With Sheets(“ChartData”) fv= .Application.Match(Stopval, .Rows(1), 0) ‘notice the dot (.) before rows End With With Sheets(“Results”) lv = .Application..Match(ReverseDate, .Rows(1), 0) ‘dot NOT needed on ACTIVE sheet but needed here. End With End Sub Try to adhere to the KISS principle Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: tangledweb Sent: Tuesday, March 27, 2012 5:50 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible I have one sheet of raw data with several columns and thousands of rows. I need to process this and I wanted to put the results on a separate sheet with several columns and dozens of rows when done. I saw some example code where it seemed you could do this as long as you qualified the sheet path but doing what I think is the same thing is not working. Also I would like to minimize the full paths I have to use or minimize activations when going back and forth from one sheet to the other to get data, process, then store data which will have to be done thousands and thousands of times. So part of the question is can you set path references that can be used to minimize the typing volume and readability? But first I need to know why I can not get this most basic initialization to work If I interactively select sheet ChartData (it is not an excel chart just a name) then the first initialization works but not the one for Results and visa versa. I have stripped out unnecessary columns for simplicity. I tried both with and without activating sheet Results but get the error Unable to get results of the match property class for the ReverseDate assignment line no matter what I do. Also tried the names with and without quotes to no difference. If in the interface I select sheet Results then the error occurs on the Stopval assignment line. ' Column reference objects Public Stopval As Double ' Public ReverseDate As Double ' Sheet names Const ChartData As String = ChartData Const Results As String = Results Sub InitRefs() ' ' Macro which sets the data column names and misc values for the sheets ' With Sheets(ChartData) Stopval = .Application.WorksheetFunction.Match(Stopval, Rows(1:1), 0) End With ' Sheets(Results).Activate ' With Sheets(Results) ' ReverseDate = .Application.WorksheetFunction.Match(ReverseDate, Rows(1:1), 0) ' End With End Sub -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible
The file data is not available currently. The program for that is being created elsewhere. But essentially it is this: Looping through raw data cells if xy then set flag value in results cells. If other columns meet other conditions then munge data and set other values in other results cells. Not really more complex than that though some of the conditions and loop control may get tricky in some boundary conditions. Something trivial in C but that is not available to me. So trying to learn something new which used to be much easier as I recall long ago. So anyway just wondering what the way is to reduce the reference to the cells to a minimal string. Your other code worked perfectly so one more step along the path, thanks again. On Tue, Mar 27, 2012 at 4:59 PM, dguillett1 dguille...@gmail.com wrote: The dot is necessary within the WITH statement. If desired, reply to this with your file with a complete explanation and before/after examples. Using a worksheet function requires the application but not the worksheet function. You could have used vba FIND. Closing down now. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com -Original Message- From: Domain Admin Sent: Tuesday, March 27, 2012 6:54 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible Thanks. Believe me I am a firm believer in KISS. I did not realize the . made that distinction though. Also like getting rid of worksheetfunction. I went the other way and tried to get rid of application and that failed. I thought it was the redundant one not worksheetfunction. So for cross referencing can I create a reference Set pointer = sheets(chartvalue) then use pointer.cells(row,col) for access since what I will be doing is reading cell values from one sheet, performing functions on the values then setting cell values in the other sheet. Or is there more to it than that? On Tue, Mar 27, 2012 at 4:42 PM, dguillett1 dguille...@gmail.com wrote: Sub InitRefs() dim fv As long dim lv As long With Sheets(“ChartData”) fv= .Application.Match(Stopval, .Rows(1), 0) ‘notice the dot (.) before rows End With With Sheets(“Results”) lv = .Application..Match(ReverseDate, .Rows(1), 0) ‘dot NOT needed on ACTIVE sheet but needed here. End With End Sub Try to adhere to the KISS principle Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: tangledweb Sent: Tuesday, March 27, 2012 5:50 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Difficulty referencing non active sheet but this must be possible I have one sheet of raw data with several columns and thousands of rows. I need to process this and I wanted to put the results on a separate sheet with several columns and dozens of rows when done. I saw some example code where it seemed you could do this as long as you qualified the sheet path but doing what I think is the same thing is not working. Also I would like to minimize the full paths I have to use or minimize activations when going back and forth from one sheet to the other to get data, process, then store data which will have to be done thousands and thousands of times. So part of the question is can you set path references that can be used to minimize the typing volume and readability? But first I need to know why I can not get this most basic initialization to work If I interactively select sheet ChartData (it is not an excel chart just a name) then the first initialization works but not the one for Results and visa versa. I have stripped out unnecessary columns for simplicity. I tried both with and without activating sheet Results but get the error Unable to get results of the match property class for the ReverseDate assignment line no matter what I do. Also tried the names with and without quotes to no difference. If in the interface I select sheet Results then the error occurs on the Stopval assignment line. ' Column reference objects Public Stopval As Double ' Public ReverseDate As Double ' Sheet names Const ChartData As String = ChartData Const Results As String = Results Sub InitRefs() ' ' Macro which sets the data column names and misc values for the sheets ' With Sheets(ChartData) Stopval = .Application.WorksheetFunction.Match(Stopval, Rows(1:1), 0) End With ' Sheets(Results).Activate ' With Sheets(Results) ' ReverseDate = .Application.WorksheetFunction.Match(ReverseDate, Rows(1:1), 0) ' End With End Sub -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3