Hi again Howard - a few more comments: "First when I used the exact same string as both the Set variable and the Match variable it said it needed an object.
It did not like identical names I guess?" By Match variable, I assume you mean the string used in the WorksheetFunction.Match expression. That isn't a variable, it's just the text you used as a column heading in your worksheet. The only variable is the one you refer to as the Set variable. VBA doesn't care if they are the same or not. I'm not sure what the problem you had was, but it could have been in the line where you declared your variable, the Dim statement. You may have used the wrong variable type after the As keyword in that line. Speaking of your declarations, instead of declaring the ranges As Object, it is better to declare them as the specific type of object that they are -- Range objects: Dim cBarDate As Range and so forth. "It may be that what I really need to do is remove the .Columns from your expression and then the variable would become an integer and I could use it as an index?" I partially answered this question in the reply I just sent by debugging your code line that determines a cell's color, but I would like to add that. you have a point. For your code so far, the Range variable is fine. A Range variable is very flexible in how you can use it. Referring to a range using a variable that points to it is faster in VBA than using the coordinates of that range (which requires VBA to create a new object each time you do so). However, there are cases where you need coordinates, and if that's the main thing you will need, it may make sense to just store the coordinates. I don't remember (if you said) why you wanted to created these variables. Perhaps they would turn out not to be the optimal solution! Not to confuse things too much, but here is one more alternative to using MATCH, if you're using Excel 2007+: structured table references. See https://office.microsoft.com/en-us/excel-help/using-structured-references-wi th-excel-tables-HA010155686.aspx for info. In short, you can use the Insert>Table command in Excel to make the data on your worksheet into a Table. Excel then handles that data in more efficient ways, and also automatically allows you to refer to all the columns by name, and provides names for the whole table, the headers, each column with it's header, each column without it's header, the data area without the headers, etc. You can use these structured references in cell formulae directly, but you can't use them in VBA directly. You would have to use them something like this in VBA: With Sheets("ChartData") ' The following structured reference returns the data range for the column, excluding the header row and any unused rows below the table. Set cBarDate = .Evaluate("ChartDataTable[BarDate]") Even though you can refer to these columns using the structered reference name from VBA at any time (by way of Evaluate), you should use variables as above if you will be referring to the same range more than once, otherwise your code will run comparitively slow. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of tangledweb Sent: Thursday, March 22, 2012 12:35 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Asa R. I tried what you said for the column addressing but I can not make it work. Can you assist? Asa, I tried what you said but am doing something wrong. First when I used the exact same string as both the Set variable and the Match variable it said it needed an object. It did not like identical names I guess? Then I created different object names and that would compile but I have tried several variations to get a correct simple value returned (what I really want to do will be more complex but I have to crack this fundamental first) and all get errors of different types. It may be that what I really need to do is remove the .Columns from your expression and then the variable would become an integer and I could use it as an index? But even if that is true and what I decide to do I would still like to understand what is wrong here. I have a set of columns and am just trying to get the color value of one of them out of the range of the column BWTStop which should be the object cBWTStop I think? ActiveCell in these attempts was the cell just to the right of one of the colored cells. The current uncommented version keeps returning 0 but the actual color value of the cell I want is 255. The commented out versions get runtime errors or compile errors. Sub Macro1() ' ' Macro1 Macro which sets the column names for the ChartData sheet ' ' Column names Dim BarDate As String Dim BarTime As String Dim Baropen As String Dim Barhigh As String Dim Barlow As String Dim Barclose As String Dim BWTStop As String Dim Short_Long_Cash As String ' Column reference object Dim cBarDate As Object Dim cBarTime As Object Dim cBaropen As Object Dim cBarhigh As Object Dim cBarlow As Object Dim cBarclose As Object Dim cBWTStop As Object Dim cShort_Long_Cash As Object With Sheets("ChartData").UsedRange 'All the columns will only have the used range. Set cBarDate = .Columns(WorksheetFunction.Match("BarDate", .Rows(1), 0)) Set cBarTime = .Columns(WorksheetFunction.Match("BarTime", .Rows(1), 0)) Set cBaropen = .Columns(WorksheetFunction.Match("BarOpen", .Rows(1), 0)) Set cBarhigh = .Columns(WorksheetFunction.Match("BarHigh", .Rows(1), 0)) Set cBarlow = .Columns(WorksheetFunction.Match("BarLow", .Rows(1), 0)) Set cBarclose = .Columns(WorksheetFunction.Match("BarClose", .Rows(1), 0)) Set cBWTStop = .Columns(WorksheetFunction.Match("BWTStop", .Rows(1), 0)) Set cShort_Long_Cash = .Columns(WorksheetFunction.Match("Short_Long_Cash", .Rows(1), 0)) End With ' ' With Selection.Font Dim mycolor ' mycolor = cBWTStop(Cells(ActiveCell.Offset(ActiveCell.Row - 1), ActiveCell.col)).Font.Color ' mycolor = Cells(4, cBWTStop).Font.Color mycolor = cBWTStop.Cells(ActiveCell.Row, ActiveCell.Column).Font.Color Debug.Print mycolor -- 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
