Search Row Multiple Values

[ianripping]

In row 2 from A to Z I have numbers from 1 to 12.

Some numbers appear more than once, sometime 4 times.

I want to have an equation that will be able to look through the ro
and pic out all of the cells which have the number 4 and return thei
column number.

Any ideas

 

[Don Guillett]

One way is to use data>filter>autofilter

 

[JMay]

Try: (in a Standard Module)

Sub demo()

Dim rSource As Range ' the area to be "viewed"
Dim rCell As Range ' for looping through the view
Dim rValue As Integer 'value you are searching for
Dim msg As String ' body of the message
Dim title As String ' title of the message

Set rSource = Range("A2:Z2")
rValue = InputBox("What number are you looking for?")

For Each rCell In rSource

If rCell = rValue Then
' initialise message variables
title = "row: %R"
msg = "Column: %C "

' populate message variables
title = Replace(title, "%R", rCell.Row)
msg = Replace(msg, "%C", rCell.Column)

' show message & test for user cancel
If MsgBox(msg, vbOKCancel, title) = _
vbCancel Then
Exit For
End If
End If
Next
End Sub

HTH
JMay

 

[Guest]

Here's a non-VBA way. I had cells that didn't match your criteria ("4") return the text value of "not 4", so you would see results instead of blanks. You can replace the "not 4" with "", if you'd rather not see anything for non-matches

You can do it down a column, but you'll have to use 2 formulas and drag down the number of cells that your values go across (since you are going from a2:z2, you'd have to drag the second formula down to include 25 cells--the first formula handles the first potential match, for a total of 26

Note: I've chosen A5 arbitrarily as the location of the first formula
The first one
=IF(A2=4,COLUMN(A2),"not four"
For all the other cells, assuming the above formula is in cell A5
=IF(OFFSET(A$2,0,(COUNTA($A$5:A5)))=4,COLUMN(OFFSET(A$2,0,(COUNTA($A$5:A5)))),"not 4"

t


----- ianripping > wrote: ----

In row 2 from A to Z I have numbers from 1 to 12

Some numbers appear more than once, sometime 4 times

I want to have an equation that will be able to look through the ro
and pic out all of the cells which have the number 4 and return thei
column number

Any ideas

 

[Guest]

As a follow-up to ianripping's original post, can the code you provided by modified to enter the column values into cells in the workbook? I'm tryong to pick up some VBA skills on the side, but the company I work for currently doesn't allow us to use our own code., and I haven't been able to edit what you provided here to get the column numbers to appear in cells--say from A5 down until no more matches were found? Can this be done

t

----- JMay wrote: ----

Try: (in a Standard Module

Sub demo(

Dim rSource As Range ' the area to be "viewed
Dim rCell As Range ' for looping through the vie
Dim rValue As Integer 'value you are searching fo
Dim msg As String ' body of the messag
Dim title As String ' title of the messag

Set rSource = Range("A2:Z2"
rValue = InputBox("What number are you looking for?"

For Each rCell In rSourc

If rCell = rValue The
' initialise message variable
title = "row: %R
msg = "Column: %C

' populate message variable
title = Replace(title, "%R", rCell.Row
msg = Replace(msg, "%C", rCell.Column

' show message & test for user cance
If MsgBox(msg, vbOKCancel, title) =
vbCancel The
Exit Fo
End I
End I
Nex
End Su

HT
JMa

 

[JMay]

Modified as follows

Sub demo()

Dim rSource As Range ' the area to be "viewed"
Dim rCell As Range ' for looping through the view
Dim rValue As Integer 'value you are searching for
Dim ColNum As Integer ' The Column Number

Set rSource = Range("A7:Z7")
rValue = Range("A10").Value 'In Cell A10 enter your desired
Value/Number
Range("B11").Select
For Each rCell In rSource
If rCell = rValue Then
ColNum = rCell.Column
ActiveCell.Value = ColNum
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub
HTH
JMay

As a follow-up to ianripping's original post, can the code you provided by

modified to enter the column values into cells in the workbook? I'm tryong
to pick up some VBA skills on the side, but the company I work for currently
doesn't allow us to use our own code., and I haven't been able to edit what
you provided here to get the column numbers to appear in cells--say from A5
down until no more matches were found? Can this be done?

 

[Guest]

Thanks

tj

 

[ianripping]

JMay. I nearly have what I need. Instead of a message box, could th
information be placed in cell A3 A4 A5 A6 etc

 

[JMay]

In a standard Module put:

Sub demo()
Dim rSource As Range ' the area to be "viewed"
Dim rCell As Range ' for looping through the view
Dim rValue As Integer 'value you are searching for
Dim ColNum As Integer ' The Column Number

Set rSource = Range("A2:Z2")
rValue = 4
Range("A3").Select
For Each rCell In rSource
If rCell = rValue Then
ColNum = rCell.Column
ActiveCell.Value = ColNum
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub
HTH,
JMay

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook:

=INDEX(ArrayMatch(4,$A$2:$Z$2),ROW(A1),2)

filled down as far as required to accommodate all occurrences of 4.

Alan Beban

 

[ianripping]

JMay, I get a debug error on

If rCell = rValue The

 

[ianripping]

Ive just realised that the cell where the first value is posted (A3)
must be below row2 for it to work. Not to sure why