ElseIf Statement problem
 

I have set a condition :

Range("M50").Select
If Range("AB25").Value = "N" Then
ActiveCell = "Drew"
ElseIf Range("AB23").Value = "N" Then
ActiveCell = "Jerry"
Else: ActiveCell = "Alternate"
End If

After this condition, Jerry is put into cell M50 because AB25's value is not
"N"

I then have a condition following this that states:

Range("M5,M38,M44,M50,M57,M63,M69,M74,M79,M85").Se lect
If Range("AB23").Value = "N" And Range("M38,M44,M50,M57,M63,M69,M74,
M79,M85").Value < "Jerry" Then
On Error Resume Next
Selection.Find(What:="Alternate", LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="Alternate", Replacement:="Jerry", LookAt:
=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

My problem lies within the ElseIf statement because when the Selection.Find
runs, it does not see "Jerry" in cell M50 and ends up finding a cell with the
word "Alternate" in it, replacing it with Jerry so my end result is having
two cells with "Jerry" in them.

Help! I have no idea where my problem lies.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

ElseIf Statement problem
 

From what I can tell your code is doing exactly what it is programmed
to do!

Assuming that AB25 contains something other than N and AB23 contains N,
M50 will contain Jerry.

The Find method is searching for Alternate not for Jerry as in
Selection.Find(What:="Alternate",...

So, there is no reason to expect it will find Jerry.

The Replace method is replacing Alternate with Jerry.
ActiveCell.Replace What:="Alternate", Replacement:="Jerry",...

The code is doing exactly what it is programmed to do. It might not be
your intent but that is another story.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <59419c86a8634@uwe, u13156@uwe says...
I have set a condition :

Range("M50").Select
If Range("AB25").Value = "N" Then
ActiveCell = "Drew"
ElseIf Range("AB23").Value = "N" Then
ActiveCell = "Jerry"
Else: ActiveCell = "Alternate"
End If

After this condition, Jerry is put into cell M50 because AB25's value is not
"N"

I then have a condition following this that states:

Range("M5,M38,M44,M50,M57,M63,M69,M74,M79,M85").Se lect
If Range("AB23").Value = "N" And Range("M38,M44,M50,M57,M63,M69,M74,
M79,M85").Value < "Jerry" Then
On Error Resume Next
Selection.Find(What:="Alternate", LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="Alternate", Replacement:="Jerry", LookAt:
=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If

My problem lies within the ElseIf statement because when the Selection.Find
runs, it does not see "Jerry" in cell M50 and ends up finding a cell with the
word "Alternate" in it, replacing it with Jerry so my end result is having
two cells with "Jerry" in them.

Help! I have no idea where my problem lies.

ElseIf Statement problem
 

"Tushar Mehta" wrote in message
om...
From what I can tell your code is doing exactly what it is programmed
to do!

Doesn't it always?

ElseIf Statement problem
 

Well if you look at the If statement prior to the "find" it should recognize
that M50 has "Jerry" in that cell and not run the "find" for alternate. For
some reason it was still running a search for alternate. My objective was to
get it to skip the search since M50 was populated with Jerry.

Tushar Mehta wrote:
From what I can tell your code is doing exactly what it is programmed
to do!

Assuming that AB25 contains something other than N and AB23 contains N,
M50 will contain Jerry.

The Find method is searching for Alternate not for Jerry as in
Selection.Find(What:="Alternate",...

So, there is no reason to expect it will find Jerry.

The Replace method is replacing Alternate with Jerry.
ActiveCell.Replace What:="Alternate", Replacement:="Jerry",...

The code is doing exactly what it is programmed to do. It might not be
your intent but that is another story.

I have set a condition :

[quoted text clipped - 30 lines]

Help! I have no idea where my problem lies.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

ElseIf Statement problem
 

Erik,

Try this change as your range test will not work (I believe!):

If Range("AB23").Value = "N" And
Range("M38,M44,M50,M57,M63,M69,M74,M79,M85").Find( "Jerry", LookIn:=xlValues)
Is Nothing Then


HTH

"Erik K via OfficeKB.com" wrote:

Well if you look at the If statement prior to the "find" it should recognize
that M50 has "Jerry" in that cell and not run the "find" for alternate. For
some reason it was still running a search for alternate. My objective was to
get it to skip the search since M50 was populated with Jerry.

Tushar Mehta wrote:
From what I can tell your code is doing exactly what it is programmed
to do!

Assuming that AB25 contains something other than N and AB23 contains N,
M50 will contain Jerry.

The Find method is searching for Alternate not for Jerry as in
Selection.Find(What:="Alternate",...

So, there is no reason to expect it will find Jerry.

The Replace method is replacing Alternate with Jerry.
ActiveCell.Replace What:="Alternate", Replacement:="Jerry",...

The code is doing exactly what it is programmed to do. It might not be
your intent but that is another story.

I have set a condition :

[quoted text clipped - 30 lines]

Help! I have no idea where my problem lies.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

ElseIf Statement problem
 

OK, so we are no longer talking about the ElseIf or the Find method but
the If statement.

If you use range(discontinuous range).value, you only get the value of
the first range. If the first range contains only 1 cell you get back
that value. If it has multiple cells, you get back an array.

Use the code below and the Locals Window of the VBE to see the
difference:
Sub testValue()
Dim x, y
x = Range("a1:a2,a4").Value
y = Range("a1,a2,a4").Value
Stop
End Sub
Before running the code enter some values into a1:a4, say, the numbers
1, 2, 3, and 4.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <59434696623d0@uwe, u13156@uwe says...
Well if you look at the If statement prior to the "find" it should recognize
that M50 has "Jerry" in that cell and not run the "find" for alternate. For
some reason it was still running a search for alternate. My objective was to
get it to skip the search since M50 was populated with Jerry.

Tushar Mehta wrote:
From what I can tell your code is doing exactly what it is programmed
to do!

Assuming that AB25 contains something other than N and AB23 contains N,
M50 will contain Jerry.

The Find method is searching for Alternate not for Jerry as in
Selection.Find(What:="Alternate",...

So, there is no reason to expect it will find Jerry.

The Replace method is replacing Alternate with Jerry.
ActiveCell.Replace What:="Alternate", Replacement:="Jerry",...

The code is doing exactly what it is programmed to do. It might not be
your intent but that is another story.

I have set a condition :

[quoted text clipped - 30 lines]

Help! I have no idea where my problem lies.