Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Handling Discrepancy 2003 vs. 2007
Hello, I was able to run a macro perfectly well using Strings that included spaces and dashes using the following code within Excel 2007, but not within Excel 2003. In Excel 2003 the following code only worked for Single character strings with no spaces, dashes, etc. Is there an easy change of the following code to make it work for a string such as "MD - Not Started", i.e., with spaces, so it will work in 2003 as well. This is a surprising and strange discrepancy between the two versions. [ Sub Final_colorcode_norecord() 'Variables for counting through cells Dim RowCount As Integer Dim ColumnCount As Integer Dim RowMax As Integer Dim ColMax As Integer Dim colorRed As String Dim colorYellow As String Dim colorGreen As String 'find the max row and col of the active worksheet RowMax = ActiveSheet.UsedRange.Rows.Count ColMax = ActiveSheet.UsedRange.Columns.Count 'Ask for text to account for colorRed = InputBox("Enter the string of text that will result in a RED Cell Coloring:", "Red Coloring") colorYellow = InputBox("Enter the string of text that will result in a YELLOW Cell Coloring:", "Yellow Coloring") colorGreen = InputBox("Enter the string of text that will result in a GREEN Cell Coloring:", "Green Coloring") 'nested for loops for counting through 2d cell grid For RowCount = 1 To RowMax For ColumnCount = 1 To ColMax 'check cell text for color red string If Cells(RowCount, ColumnCount).Text = colorRed Then Cells(RowCount, ColumnCount).Interior.Color = RGB(255, 0, 0) 'Red End If 'check cell text for color yellow string If Cells(RowCount, ColumnCount).Text = colorYellow Then Cells(RowCount, ColumnCount).Interior.Color = RGB(255, 255, 0) 'Yellow End If 'check cell text for color green string If Cells(RowCount, ColumnCount).Text = colorGreen Then Cells(RowCount, ColumnCount).Interior.Color = RGB(0, 255, 0) 'Green End If Next ColumnCount Next RowCount End Sub ] TIA, Brian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Handling Discrepancy 2003 vs. 2007
Brian- I pasted your code into XL2003, and it works perfectly on cells that have spaces and dashes. Things to check: 1. Do you want the code to be case sensitive? If not, take both the input string and the cell text and compare them both as ucase or lcase, e.g. if lcase(colorRed)= lcase(cell.text) then... 2. Where does your data come from? If it is a report (or a scraped HTML file), then the spaces may not be chr(32), even though it looks the same. Dashes might be the short dash or long dash, etc. Try using asc("<value") on any possible problem areas to see if they are really the character they appear to be. HTH, Keith "Brian B" wrote: Hello, I was able to run a macro perfectly well using Strings that included spaces and dashes using the following code within Excel 2007, but not within Excel 2003. In Excel 2003 the following code only worked for Single character strings with no spaces, dashes, etc. Is there an easy change of the following code to make it work for a string such as "MD - Not Started", i.e., with spaces, so it will work in 2003 as well. This is a surprising and strange discrepancy between the two versions. [ Sub Final_colorcode_norecord() 'Variables for counting through cells Dim RowCount As Integer Dim ColumnCount As Integer Dim RowMax As Integer Dim ColMax As Integer Dim colorRed As String Dim colorYellow As String Dim colorGreen As String 'find the max row and col of the active worksheet RowMax = ActiveSheet.UsedRange.Rows.Count ColMax = ActiveSheet.UsedRange.Columns.Count 'Ask for text to account for colorRed = InputBox("Enter the string of text that will result in a RED Cell Coloring:", "Red Coloring") colorYellow = InputBox("Enter the string of text that will result in a YELLOW Cell Coloring:", "Yellow Coloring") colorGreen = InputBox("Enter the string of text that will result in a GREEN Cell Coloring:", "Green Coloring") 'nested for loops for counting through 2d cell grid For RowCount = 1 To RowMax For ColumnCount = 1 To ColMax 'check cell text for color red string If Cells(RowCount, ColumnCount).Text = colorRed Then Cells(RowCount, ColumnCount).Interior.Color = RGB(255, 0, 0) 'Red End If 'check cell text for color yellow string If Cells(RowCount, ColumnCount).Text = colorYellow Then Cells(RowCount, ColumnCount).Interior.Color = RGB(255, 255, 0) 'Yellow End If 'check cell text for color green string If Cells(RowCount, ColumnCount).Text = colorGreen Then Cells(RowCount, ColumnCount).Interior.Color = RGB(0, 255, 0) 'Green End If Next ColumnCount Next RowCount End Sub ] TIA, Brian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Handling Discrepancy 2003 vs. 2007
Short dash and long dash are 45 and 150, respectively. "ker_01" wrote: Brian- I pasted your code into XL2003, and it works perfectly on cells that have spaces and dashes. Things to check: 1. Do you want the code to be case sensitive? If not, take both the input string and the cell text and compare them both as ucase or lcase, e.g. if lcase(colorRed)= lcase(cell.text) then... 2. Where does your data come from? If it is a report (or a scraped HTML file), then the spaces may not be chr(32), even though it looks the same. Dashes might be the short dash or long dash, etc. Try using asc("<value") on any possible problem areas to see if they are really the character they appear to be. HTH, Keith "Brian B" wrote: Hello, I was able to run a macro perfectly well using Strings that included spaces and dashes using the following code within Excel 2007, but not within Excel 2003. In Excel 2003 the following code only worked for Single character strings with no spaces, dashes, etc. Is there an easy change of the following code to make it work for a string such as "MD - Not Started", i.e., with spaces, so it will work in 2003 as well. This is a surprising and strange discrepancy between the two versions. [ Sub Final_colorcode_norecord() 'Variables for counting through cells Dim RowCount As Integer Dim ColumnCount As Integer Dim RowMax As Integer Dim ColMax As Integer Dim colorRed As String Dim colorYellow As String Dim colorGreen As String 'find the max row and col of the active worksheet RowMax = ActiveSheet.UsedRange.Rows.Count ColMax = ActiveSheet.UsedRange.Columns.Count 'Ask for text to account for colorRed = InputBox("Enter the string of text that will result in a RED Cell Coloring:", "Red Coloring") colorYellow = InputBox("Enter the string of text that will result in a YELLOW Cell Coloring:", "Yellow Coloring") colorGreen = InputBox("Enter the string of text that will result in a GREEN Cell Coloring:", "Green Coloring") 'nested for loops for counting through 2d cell grid For RowCount = 1 To RowMax For ColumnCount = 1 To ColMax 'check cell text for color red string If Cells(RowCount, ColumnCount).Text = colorRed Then Cells(RowCount, ColumnCount).Interior.Color = RGB(255, 0, 0) 'Red End If 'check cell text for color yellow string If Cells(RowCount, ColumnCount).Text = colorYellow Then Cells(RowCount, ColumnCount).Interior.Color = RGB(255, 255, 0) 'Yellow End If 'check cell text for color green string If Cells(RowCount, ColumnCount).Text = colorGreen Then Cells(RowCount, ColumnCount).Interior.Color = RGB(0, 255, 0) 'Green End If Next ColumnCount Next RowCount End Sub ] TIA, Brian |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Handling Discrepancy 2003 vs. 2007
Interesting. It turns out there's no discrepancy between the two versions. It's just that in the '07 spreadsheet the cell contained only the Searched String. the '03 cell would contain both the searched string and another string. What it boils down to is I have to use the InStr function effectively. I'm currently having trouble trying to convert "Cells(RowCount, ColumnCount).Text" into a String so as to be able to evaluate it against the "colorRed" string, etc. using the Instr function. It appears to be a bit difficult. Thanks for your assistance, Brian "ker_01" wrote: Short dash and long dash are 45 and 150, respectively. "ker_01" wrote: Brian- I pasted your code into XL2003, and it works perfectly on cells that have spaces and dashes. Things to check: 1. Do you want the code to be case sensitive? If not, take both the input string and the cell text and compare them both as ucase or lcase, e.g. if lcase(colorRed)= lcase(cell.text) then... 2. Where does your data come from? If it is a report (or a scraped HTML file), then the spaces may not be chr(32), even though it looks the same. Dashes might be the short dash or long dash, etc. Try using asc("<value") on any possible problem areas to see if they are really the character they appear to be. HTH, Keith "Brian B" wrote: Hello, I was able to run a macro perfectly well using Strings that included spaces and dashes using the following code within Excel 2007, but not within Excel 2003. In Excel 2003 the following code only worked for Single character strings with no spaces, dashes, etc. Is there an easy change of the following code to make it work for a string such as "MD - Not Started", i.e., with spaces, so it will work in 2003 as well. This is a surprising and strange discrepancy between the two versions. [ Sub Final_colorcode_norecord() 'Variables for counting through cells Dim RowCount As Integer Dim ColumnCount As Integer Dim RowMax As Integer Dim ColMax As Integer Dim colorRed As String Dim colorYellow As String Dim colorGreen As String 'find the max row and col of the active worksheet RowMax = ActiveSheet.UsedRange.Rows.Count ColMax = ActiveSheet.UsedRange.Columns.Count 'Ask for text to account for colorRed = InputBox("Enter the string of text that will result in a RED Cell Coloring:", "Red Coloring") colorYellow = InputBox("Enter the string of text that will result in a YELLOW Cell Coloring:", "Yellow Coloring") colorGreen = InputBox("Enter the string of text that will result in a GREEN Cell Coloring:", "Green Coloring") 'nested for loops for counting through 2d cell grid For RowCount = 1 To RowMax For ColumnCount = 1 To ColMax 'check cell text for color red string If Cells(RowCount, ColumnCount).Text = colorRed Then Cells(RowCount, ColumnCount).Interior.Color = RGB(255, 0, 0) 'Red End If 'check cell text for color yellow string If Cells(RowCount, ColumnCount).Text = colorYellow Then Cells(RowCount, ColumnCount).Interior.Color = RGB(255, 255, 0) 'Yellow End If 'check cell text for color green string If Cells(RowCount, ColumnCount).Text = colorGreen Then Cells(RowCount, ColumnCount).Interior.Color = RGB(0, 255, 0) 'Green End If Next ColumnCount Next RowCount End Sub ] TIA, Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print Preview Discrepancy | Excel Discussion (Misc queries) | |||
Finance functions discrepancy | Excel Worksheet Functions | |||
Format Cells - Accounting - Discrepancy? | Excel Discussion (Misc queries) | |||
Font discrepancy | New Users to Excel | |||
Vlookup data discrepancy | Excel Discussion (Misc queries) |