#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    9
    Rep Power
    0

    searching a row in excel


    I am having problems searching for an item on a row in a spreadsheet.
    I have initialised a variable current_cell as the starting point, setting it to A1

    Set current_cell = Sheets("Site Performance Data").Range("A1")

    the data is on the first row, starting at A1.

    I would like to test that there is data in each cell, using isempty function, if there is this value is put into a combo box.
    After the cell ahs been checked current cell is moved onto the next column.

    I want to continue going along the row until there is no more data to the right of the current cell. I thought of setting the right border of the last column to be a double line and check each cell for this.

    The code I have so far is as follows :


    Sub Pop_KPI_CmBx(cmbx As ComboBox)

    Dim current_cell As Range

    Set current_cell = Sheets("Site Performance Data").Range("A1")
    current_cell.Select

    While Not (current_cell.Borders(xlEdgeRight).LineStyle = xlDouble)

    If Not IsEmpty(current_cell) Then
    cmbx.AddItem current_cell.value
    End If

    current_cell = current_cell.Offset(, 2)


    Wend

    End Sub



    when i run this it goes into an infinite loop. can anyone tell me where ive gone wrong or what i can do differently?

    Thanks
    Andy
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    Cheltenham, England.
    Posts
    101
    Rep Power
    15
    Try changing "current_cell = current_cell.Offset(, 2) " to "SET current_cell = current_cell.Offset(, 2)".

IMN logo majestic logo threadwatch logo seochat tools logo