Macro not working properly

[Brianfree]

Hi, i have the following code in one of my macros which is not working correctly. Because "_new" will appear within both elseif statments the "_newreb" part is being ignored.

Hope this make sense!

ElseIf InStr(ActiveCell.Text, "_NEW") Then
Range("D" & X).Value = "_NEW"
Range("D" & X).Interior.ColorIndex = 3
Range("D" & X).Font.Bold = True
Range("E" & X).Value = "PC146"


ElseIf InStr(ActiveCell.Text, "_NEWREB") Then
Range("D" & X).Value = "_NEWREB"
Range("D" & X).Interior.ColorIndex = 44
Range("D" & X).Font.Bold = True
Range("E" & X).Value = "PC145"

Please can anyone help!!!

Kindest regards,

Brian

 

[ajrimmer]

What about reversing the order of the statements?

ElseIf InStr(ActiveCell.Text, "_NEWREB") Then
                Range("D" & X).Value = "_NEWREB"
                Range("D" & X).Interior.ColorIndex = 44
                Range("D" & X).Font.Bold = True
                Range("E" & X).Value = "PC145" 

ElseIf InStr(ActiveCell.Text, "_NEW") Then
                Range("D" & X).Value = "_NEW"
                Range("D" & X).Interior.ColorIndex = 3
                Range("D" & X).Font.Bold = True
                Range("E" & X).Value = "PC146"

 

[Brianfree]

Hi thanks for replying, i have done this but it still does not work ;-(

it seams that it just detects "_new" and doesn't go as far as "_newreb"

Regards,

Brian

 

[xlbo]

You are not understanding how Instr works. From help file:

Return Values

If InStr returns
string1 is zero-length 0
string1 is Null Null
string2 is zero-length start
string2 is Null Null
string2 is not found 0
string2 is found within string1 Position at which match is found
start > string2 0

You therefore need to test for the RESULT of instr - you can only use the syntax you have if the result of the function is true or false:

ElseIf InStr(ActiveCell.Text, "_NEWREB") [b]<> 0[/b] Then
                Range("D" & X).Value = "_NEWREB"
                Range("D" & X).Interior.ColorIndex = 44
                Range("D" & X).Font.Bold = True
                Range("E" & X).Value = "PC145" 

ElseIf InStr(ActiveCell.Text, "_NEW") [b]<> 0[/b] Then
                Range("D" & X).Value = "_NEW"
                Range("D" & X).Interior.ColorIndex = 3
                Range("D" & X).Font.Bold = True
                Range("E" & X).Value = "PC146"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

"... Because "_new" will appear within both elseif statments the "_newreb" part is being ignored."

THEN don't say Else.

Rather

             If ActiveCell.Text Like "*_NEW*" Then
                Range("D" & X).Value = "_NEW"
                Range("D" & X).Interior.ColorIndex = 3
                Range("D" & X).Font.Bold = True
                Range("E" & X).Value = "PC146"
             End If
              
             If ActiveCell.Text Like "*_NEWREB*" Then
                Range("D" & X).Value = "_NEWREB"
                Range("D" & X).Interior.ColorIndex = 44
                Range("D" & X).Font.Bold = True
                Range("E" & X).Value = "PC145"
             End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Brianfree]

Thanks for all your help - all working now!

Kindest regards,

Brian Free

 

[xlbo]

for future ref, please post vba questions in the VBA forum: Forum707

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question