For Each minor problem

[Susan]

i've done this kind of thing a thousand times, but i can't get this
one to work.........
'===========================
Dim cell as Range

myLastSusanRow = ws3.Cells(10000, 1).End(xlUp).Row + 1
Set rSusan = ws3.Range("a2:a" & myLastSusanRow)

For Each cell In rSusan
If cell.Value = Me.txtBook.Value Then
'minor stuff
Else
'do nothing
End If
Next cell
'=========================
the intellisense will give me the correct information for
me.txtBook.Value, but gives me nothing for cell.value. i've tried
cell.text, that does nothing. the intellisense only works if i just
use "cell", but the comparison doesn't work. i've put in a
msgbox cell.value
test and that gives me the correct information.
the cell range is on Sheet3, and the txtBook textbox is on Sheet2.
the code is held in the Sheet2 area, not in a general module. i'm
using XP 2000xl.
i know it's something minor & stupid, but what am i doing wrong?
thanks in advance!

susan

 

[Gary Keramidas]

not sure, is the cell value a number and the textbox value a string.

maybe str(cell.Value)

 

[Leith Ross]

i've done this kind of thing a thousand times, but i can't get this
one to work.........
'===========================
Dim cell as Range

myLastSusanRow = ws3.Cells(10000, 1).End(xlUp).Row + 1
Set rSusan = ws3.Range("a2:a" & myLastSusanRow)

For Each cell In rSusan
If cell.Value = Me.txtBook.Value Then
'minor stuff
Else
'do nothing
End If
Next cell
'=========================
the intellisense will give me the correct information for
me.txtBook.Value, but gives me nothing for cell.value. i've tried
cell.text, that does nothing. the intellisense only works if i just
use "cell", but the comparison doesn't work. i've put in a
msgbox cell.value
test and that gives me the correct information.
the cell range is on Sheet3, and the txtBook textbox is on Sheet2.
the code is held in the Sheet2 area, not in a general module. i'm
using XP 2000xl.
i know it's something minor & stupid, but what am i doing wrong?
thanks in advance!

susan

Hello Susan,

I don't see where you declared rSusan. VBA will assume the collection
rSusan is a variant and intellsense has to know what type of collection
you have in to display the properties and methods.

Sincerely,
Leith Ross

 

[Susan]

well, that may have solved one unknown problem because yes, the
cell.Value is a number and i know textbox values are strings.
unfortunately the intellisense is still not telling me what Str
(cell.Value) is. and when i deliberately set it up so that Str
(cell.Value) and Me.txtBook are the same, it's not "seeing" that.

any other ideas?
thanks
susan

 

[Susan]

nope, rSusan is properly declared as a range (i just didn't list it,
sorry - i was trying to simplify the problem).

Dim rSusan As Range


susan

 

[Susan]

ok i think i'm on my way to solving it.... i introduced another
variable:

Dim SusanBook as string

For Each cell In rSusan
SusanBook = str(cell.Value)
If SusanBook = Me.txtBook.Value Then
'minor stuff
Else
'do nothing
End If
Next cell

the intellisense still is not triggering for str(cell.Value), but the
if statement is now triggering.
thanks for all your help & if anybody has any additional ideas, feel
free to add them!

susan

 

[Rick Rothstein]

You said in another subthread that the cells contain numbers... are those
cells formatted to show less decimal places than there actually are and are
you trying to compare a TextBox value that matches the formatted value
rather than the actual value in the cell? (If the cell is formatted, the
Value property returns the unformatted number that appears in the Formula
bar, not the value you see in the cell.)

 

[Susan]

yes, rick, that was it - in a way....... you were correct in that the
textbox value was not exactly maching the cell.value, but it wasn't
because of decimals because they were both whole numbers. but after
staring at it long enough it finally penetrated that there was an
extra space in the cell.value. so the cell.value was " 1" and the
textbox.value was "1". so i added another line:

SusanBook = str(cell.Value)
SusanBook = trim(SusanBook) 'added line

that finally took care of the problem.
many thanks to everybody!

susan

 

[Rick Rothstein]

Don't use the Str function (it is a relic from the past), use CStr or
Format$... yes, Format$, as in Format$(cell.Value)... then you won't need
the Trim function call because neither of these methods adds the leading
space for positive numbers. By the way, if SusanBook is declared as a String
or Variant variable, you can also let VB do the conversion for you
automatically by just assigning cell.Value directly to it without using any
function calls. If SusanBook is declared as a Variant, though, the direct
assignment will assign a number to it and the conversion to a String value
will be put off until the If's comparison statement.

--
Rick (MVP - Excel)


yes, rick, that was it - in a way....... you were correct in that the
textbox value was not exactly maching the cell.value, but it wasn't
because of decimals because they were both whole numbers. but after
staring at it long enough it finally penetrated that there was an
extra space in the cell.value. so the cell.value was " 1" and the
textbox.value was "1". so i added another line:

SusanBook = str(cell.Value)
SusanBook = trim(SusanBook) 'added line

that finally took care of the problem.
many thanks to everybody!

susan