Excel VBA help to test the activecell.value

[Marie]

I am new to VBA macro's and can not get the following test to work
Range("I4").Select
If ActiveCell.Value = "W" Then GoTo WVEnumber
If ActiveCell.Value = "G" Then GoTo BIGnumber
If ActiveCell.Value = "F" Then GoTo BIFnumber

The Macro branches to WVEnumber - no matter what is in the cell "I4" - can
anyone help?

 

[Mike]

Your code works for me. ???
Sub test()
Range("I4").Select
If ActiveCell.Value = "W" Then MsgBox "WVEnumber"
If ActiveCell.Value = "G" Then MsgBox "BIGnumber"
If ActiveCell.Value = "F" Then MsgBox "BIFnumber"
End Sub

 

[Bob Phillips]

Perhaps you are on the wrong sheet

Try

With Worksheets("Sheet1").Range("I4")
If .Value = "W" Then GoTo WVEnumber
If .Value = "G" Then GoTo BIGnumber
If .Value = "F" Then GoTo BIFnumber
End With

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Alan]

If EVEnumber, BIGnumber, & BIFnumber are range names then go to them by
selecting or activating that range:

If ActiveCell.Value = "W" Then Range("WVEnumber").Select ' or activate


Alan

 

[Marie]

Yippee ! - at last

This works fine - thankyou for your help

 

[Chip Pearson]

As you are new to VBA, you still have time to learn good practices and
unlearn bad ones. Try to avoid GoTo statements, and instead structure your
code with conditional statements (e.g., the If statement) and/or by calling
other procedures. GoTo in nearly universally avoided by professional
programmers.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Fays]

Hello everyone

This is great that you guys help everyone.

I am looking for a code where I can check certain text in one cell and than
apply the condition on that.

For Example:

IF ActiveCell.Value Contains / Exists "," than seperate the text.

I actually want to break the text which is longer than 60 character but in a
way that I want break the word and for that I need a code which could check
in the cell something like , or ; or / and than break that text and write in
to the next cell the remaining part.

Your help is really appreciated.

Many thanks

 

[Bob Phillips]

Sub test()
Dim aryChars As Variant
Dim pos As Long
Dim i As Long

aryChars = Array(",", ";", "/")

With ActiveCell

For i = LBound(aryChars) To UBound(aryChars)
pos = InStr(.Value, aryChars(i))
If pos > 0 Then

.Offset(0, 1).Value = Right$(.Value, Len(.Value) - pos)
.Value = Left$(.Value, pos - 1)
Exit For
End If
Next i
End With

End Sub