I'm a little rusty with my VBA, so please forgive any obvious errors here.
What I'm trying to do is quite simple - I have a table with 17 columns
(B2-R2), and 126 rows (B2-B128). Within each row, there is one (and only one)
"X". I want the macro to look through each row, find the X, then output the
number of the column containing the "X" into a cell at the end of each row.

Ideally, I'd want a program that could handle multiple "X"'s in each row and
give me the column number of each of them, but I'd be happy with just finding
the single "X" for the moment.

I've put a watch on the column/row counters, and the numbers seem to jump
around a lot, but I'm not sure why.

Here's the code:
Sub checklocation()
Dim SearchRange As Range
Dim rowcount As Integer
Dim columncount As Integer
Dim check As Range
Dim outputcell As Range

Worksheets(1).Range("B2").Activate
Set outputcell = Worksheets(1).Cells(2, 21)
Set check = Worksheets(1).Cells(2, 2)

For rowcount = 2 To 128

For columncount = 2 To 18
Set check = Worksheets(1).Cells(rowcount, columncount)
If check.Value = "X" Then
outputcell.Value = columncount
Else
outputcell.Value = 0
End If
Next columncount
Set outputcell = Worksheets(1).Cells(rowcount, 21)
Next rowcount
End Sub

Stephen wrote:
I'm a little rusty with my VBA, so please forgive any obvious errors here.
What I'm trying to do is quite simple - I have a table with 17 columns
(B2-R2), and 126 rows (B2-B128). Within each row, there is one (and only one)
"X". I want the macro to look through each row, find the X, then output the
number of the column containing the "X" into a cell at the end of each row.

Ideally, I'd want a program that could handle multiple "X"'s in each row and
give me the column number of each of them, but I'd be happy with just finding
the single "X" for the moment.

You might try using the MATCH Function instead. If You enter this
formula in cells S2:S128 it should return the associated column number
for you...=MATCH("x",B2:R2)

Sweet! Thanks!

"Mark" wrote:


Stephen wrote:
I'm a little rusty with my VBA, so please forgive any obvious errors here.
What I'm trying to do is quite simple - I have a table with 17 columns
(B2-R2), and 126 rows (B2-B128). Within each row, there is one (and only one)
"X". I want the macro to look through each row, find the X, then output the
number of the column containing the "X" into a cell at the end of each row.

Ideally, I'd want a program that could handle multiple "X"'s in each row and
give me the column number of each of them, but I'd be happy with just finding
the single "X" for the moment.

You might try using the MATCH Function instead. If You enter this
formula in cells S2:S128 it should return the associated column number
for you...=MATCH("x",B2:R2)