+ Reply to Thread
Results 1 to 3 of 3

Help with simple looping program

  1. #1
    Stephen
    Guest

    Help with simple looping program

    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

  2. #2
    Mark
    Guest

    Re: Help with simple looping program


    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)


  3. #3
    Stephen
    Guest

    Re: Help with simple looping program

    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)
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1