|
|
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
|
|
not sure, is the cell value a number and the textbox value a string.
maybe str(cell.Value)
--
Gary
"Susan" <bogenexcel[ at ]aol.com> wrote in message
news:702a809f-0fd4-49dc-9450-3599c0c49299[ at ]w1g2000prk.googlegroups.com...
[Quoted Text] > 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
|
|
Susan;163759 Wrote:
> i've done this kind of thing a thousand times, but i can't get this
[Quoted Text] > 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
--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=45505
|
|
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
On Dec 31, 11:50 am, "Gary Keramidas" <GKeramidasAtMsn.com> wrote:
[Quoted Text] > not sure, is the cell value a number and the textbox value a string.
>
> maybe str(cell.Value)
>
> --
>
> Gary
>
> "Susan" <bogenex...[ at ]aol.com> wrote in message
>
> news:702a809f-0fd4-49dc-9450-3599c0c49299[ at ]w1g2000prk.googlegroups.com...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -
|
|
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
On Dec 31, 11:50 am, Leith Ross <Leith.Ross.3la...[ at ]thecodecage.com>
wrote:
[Quoted Text] > Susan;163759 Wrote: > > > > > > > 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 > > -- > Leith Ross > ------------------------------------------------------------------------ > Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=45505- Hide quoted text - > > - Show quoted text -
|
|
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
On Dec 31, 12:10 pm, Susan <bogenex...[ at ]aol.com> wrote:
[Quoted Text] > 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 > > On Dec 31, 11:50 am, Leith Ross <Leith.Ross.3la...[ at ]thecodecage.com> > wrote: > > > > > Susan;163759 Wrote: > > > > 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 > > > -- > > Leith Ross > > ------------------------------------------------------------------------ > > Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75> > View this thread: http://www.thecodecage.com/forumz/showthread.php?t=45505-Hide quoted text - > > > - Show quoted text -- Hide quoted text - > > - Show quoted text -
|
|
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.)
--
Rick (MVP - Excel)
"Susan" <bogenexcel[ at ]aol.com> wrote in message
news:702a809f-0fd4-49dc-9450-3599c0c49299[ at ]w1g2000prk.googlegroups.com...
[Quoted Text] > 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
|
|
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!
:D
susan
On Dec 31, 12:56 pm, "Rick Rothstein"
<rick.newsNO.S...[ at ]NO.SPAMverizon.net> wrote:
[Quoted Text] > 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.)
>
> --
> Rick (MVP - Excel)
>
> "Susan" <bogenex...[ at ]aol.com> wrote in message
>
> news:702a809f-0fd4-49dc-9450-3599c0c49299[ at ]w1g2000prk.googlegroups.com...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -
|
|
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)
"Susan" <bogenexcel[ at ]aol.com> wrote in message
news:213b366f-5077-452f-bbc4-4db9fd4cd9c8[ at ]q18g2000vbn.googlegroups.com...
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!
:D
susan
On Dec 31, 12:56 pm, "Rick Rothstein"
<rick.newsNO.S...[ at ]NO.SPAMverizon.net> wrote:
[Quoted Text] > 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.)
>
> --
> Rick (MVP - Excel)
>
> "Susan" <bogenex...[ at ]aol.com> wrote in message
>
> news:702a809f-0fd4-49dc-9450-3599c0c49299[ at ]w1g2000prk.googlegroups.com...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -
|
|
|