Query - Formula (Test for string)

[mralmackay]

Hi,

Can someone help with the following @ all. I've created the following
VBA which works as expected the first time, however I only want it to
check for the value once, whereas this will add in the prefix more
than once when I run it?

e.g. this is meant to check B4, if this contains CD- don't do anything
if it doesn't add it in.

Any suggestions?

Cheers, Al.

Sub Prefix()
Const Prefix = "CD-"

If Left("B4", 3) = "CD-" Then
Range("B4").Select
ActiveCell.Value = Prefix & ActiveCell.Value
Else
End If
End Sub

 

[Ron Coderre]

Sorry, but I'm confused...

This is what your code is doing:

1) Defines the constant "Prefix" to equal: "CD-"
2) Checks if the left 3 chars of the string "B4" = "CD-"
(which it never will, of course. Did you want it to check cell B4?)
2a) But, if it does....then select cell B4
2b) Next, using the active cell (which can only be B4)...
Prepend the Prefix to its value.
3) Otherwise, do nothing

What did you intend the code to do?

--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Incidental]

Hi there

i think the problem is that you are not checking the value held in the
cell, i have modified the code below and i seems to work fine. i have
dim'ed a range called MyCell this allows you to check the value and
change the value of the cell without having to select it which is
neater, i have also changed ="CD-" in the If statement to look to see
if it holds something other than "CD-" by using <> instead of = which
means i can do without using an else statement in the if

Sub Prefix()

Dim MyCell As Range
Const Prefix = "CD-"

Set MyCell = [B4]

If Left(MyCell.Value, 3) <> "CD-" Then
MyCell.Value = Prefix & MyCell.Value
End If

End Sub


hope this helps

Steve

 

[mralmackay]

Hi Ron,

Apologies, I noticed I'd done an error on this and then tried to add
an updated text but it doesn't appear to have posted.

What in essence I'm trying to achive is:
1) If B4 contains any data (which is prefixed "CD-") don't do
anything.
2) If it contains data, e.g. 123 but not prefixed with CD- then add
it in.
3) If it contains no data, don't do anything.

HTH, Thanks Al.

 

[mralmackay]

Hi Steve,

This works brilliantly.

Thanks for your help on this.

Cheers, Al.

 

[michael.beckinsale]

Hi mralmac,

Try this

If Left(Range("B4").Value, 3) = "CD-" Then

Regards

Michael Beckinsale