Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Serach for certain values in a particular row.

Posted on 2013-11-15
Medium Priority
318 Views
I have a spread sheet that I want to search for certain values in a certain row.

The value "GQ-xxx" will alweays be in row 3.

Examples of GQ-xxx values...
GQ-000
GQ-001
GQ-005
GQ-008
GQ-010_2
and so on.

Process...
Read row 3 Cell by Cell from begining to the end.
Searching for GQ-* (wildcard any value starting with GQ-)

For this example just MsgBox the value to the screen.

Thanks
0
Question by:tesla764
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 13

Assisted Solution

Shanan212 earned 1000 total points
ID: 39651408
``````Sub Sample()

Dim x As Long, lRow As Long

lRow = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column

For x = 1 To lRow

If InStr(Cells(3, x).Value, "GQ-") > 0 Then
MsgBox Cells(3, x).Value
End If
Next x

End Sub
``````

So this will run through all your columns and pop a message box for each value that has 'GQ-'

If you want to break the loop, press ctrl+break on your keyboard
0

LVL 6

Accepted Solution

Michael earned 1000 total points
ID: 39651411
Hi there,

one approach is like this:

``````Sub GQ()
Dim rng As Range, cell As Range
Dim lc As Integer

lc = Cells(3, Columns.Count).End(xlToLeft).Column
Set rng = Range(Cells(3, 1), Cells(3, lc))

For Each cell In rng
If cell.Value Like "GQ-*" Then
MsgBox cell.Value
End If
Next
End Sub
``````

Hope this works for you.

Joop
0

Author Closing Comment

ID: 39651502
Yes both of these work very well. Thanks you very much.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…