VBA Excel Question

games0802
games0802 used Ask the Experts™
on
I am attempting to make a simple macro for an excel spreadsheet.  For starters, my background is in C/C++ with some java, and vb experience peppered in.

Basically, the simple problem I am having is:

How do I test values in a particular cell to see if it contains certain text?

Here's what I have so far...
=================================================
Private Sub CommandButton1_Click()
   Dim MyCell As Range
   Dim NumRows As Integer
   Dim CellValue As Variant
   
   Set MyCell = Worksheets(1).[a1]
   
   NumRows = Application.CountA(ActiveSheet.Range("A:A"))
   
   For Q = 2 To LastRow
      CellValue = MyCell(Q, 3).Value
      If CellValue.Text.Contains("ABC") Then
         MsgBox "Found one"
      End If
   Next Q

===================================================

Basically, I get errors as soon as it enters the loop.  I'm sure this is a simple thing to do, but I'm just not used to the VBA language and I'm having a hard time finding examples.  Thanks in advance.

Eric
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006

Commented:
Hello games0802,

you could try

Private Sub CommandButton1_Click()
  Dim MyCell As Range
  Dim NumRows As Integer
  Dim CellValue As Variant
  Dim Q As Long
   
  Set MyCell = Worksheets(1).[a1]
 
  NumRows = Application.CountA(ActiveSheet.Range("A:A"))
 
  For Q = 2 To NumRows
     CellValue = MyCell(Q, 2).Value
     If CellValue = "ABC" Then
        MsgBox "Found one"
     End If
  Next Q
 
End Sub

btw interesting syntax to get the numrows didn't think of that one

HAGD:O)Bruintje

Author

Commented:
I probably wasn't specific enough in my original question.  Basically, the cell may contain a long string like "word blah abc more etc" and I need to do a search for one word within the string.  For example (this is the real deal here):

The cell values are part numbers such as RAM-PC32, RAM-PC64, etc.  I need to be able to go through the entire list of 1100 parts and pick off all of the part numbers that start with RAM or that contain RAM.  I hope that clarifies my question.

games0802
Top Expert 2006
Commented:
you could try

Private Sub CommandButton1_Click()
  Dim MyCell As Range
  Dim NumRows As Integer
  Dim CellValue As Variant
  Dim Q As Long
   
  Set MyCell = Worksheets(1).[a1]
 
  NumRows = Application.CountA(ActiveSheet.Range("A:A"))
 
  For Q = 2 To NumRows
     CellValue = MyCell(Q, 2).Value
     If InStr(1, CellValue, "RAM", vbBinaryCompare) Then
        MsgBox "Found one"
     End If
  Next Q
 
End Sub

Author

Commented:
That did it.  Thanks very much.

Eric

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial