RE: String Handling Discrepancy 2003 vs. 2007

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


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
.

Relevant Pages

  • Re: Formulas containing hard coded values
    ... Dim R As Range, sdoit As String ... MsgBox "Cell contains hard codes" ... ' Grateful thanks to Rick Rothstein ... Dim Fml As String, LCtext As String ...
    (microsoft.public.excel.programming)
  • Re: Formulas containing hard coded values
    ... Dim R As Range, sdoit As String ... MsgBox "Cell contains hard codes" ... Dim Fml As String, LCtext As String ... Dim UCtext As String, OriginalFormula As String ...
    (microsoft.public.excel.programming)
  • VB, implementing the high score within this code
    ... Private possibleAs String ... Private Function SolvePuzzleAs Boolean ... ' Calculates the possible values for all the cell ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Formulas containing hard coded values
    ... Dim R As Range, sdoit As String ... MsgBox "Cell contains hard codes" ... Dim Fml As String, LCtext As String ... For Each Rng In R.Precedents.Areas ...
    (microsoft.public.excel.programming)
  • Re: Marking points read for coordinates
    ... In "Private Sub GetCoordinates()" comment all the code between ... (ByVal lpClassName As String, ByVal lpWindowName As String) ... ' cell A1 which is the base for all object coordinates on a sheet. ... Dim chtObj As ChartObject ...
    (microsoft.public.excel.programming)