Finding the next empty cell (Even if a formula exists)
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Finding the next empty cell (Even if a formula exists)

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi-

    matchcol = Cells(x, "cb").End(xlToLeft).Column

    This code finds the next empty cell to the left of column "CB". But if theres a formula (even though the cell is blank)the code chooses that cell. How can I rearange that code so that it wont reconize a formula in the cell, but just the first cell with actuall text?

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If there is always this cell with a formula at the end then you can just offset over 1:

    matchcol = Cells(x, "cb").End(xlToLeft).Columns.Offset(0, -1).Select

    or put a loop after it:

    matchcol = Cells(x, "cb").End(xlToLeft).Columns.Select
    Do While ActiveCell.Formula <> ""
    On Error GoTo 1
    ActiveCell.Offset(0, -1).Select
    Loop
    1



    _________________
    Hope this helps.
    Kind regards, Al.

    [ This Message was edited by: Al Chara on 2002-04-10 13:26 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Al- Thanks for responding, All the cells that this code uses contian formulas. The formula pulls text from another part of the sheet. (ex: cell BX3 has a formula that reads =T3) So if cell T3 contains a number then that duplicate number is copied in cell BX3. All cells that the code uses consists of these type formulas. The cells maybe blank, but the code doesnt read it that way. How would you suggest that I could still use:


    matchcol = Cells(x, "cb").End(xlToLeft).Column

    and find the next empty cell (even though theres a formula in it)


  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cells(x, "cb").select
    Do While ActiveCell.Formula <> ""
    On Error GoTo 1
    ActiveCell.Offset(0, -1).Select
    Loop
    1
    Won't think work for you.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No unfortanatly it wont, thanks anyway

  6. #6
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Explain, why it won't work. I can edit to fit your situation.

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok heres the full code:


    Private Sub CommandButton6_Click()
    Dim lastrow As Long, x As Long
    Dim matchday As Integer, matchcol As Integer
    Dim myarr(1 To 7)

    matchday = WorksheetFunction.Match(Format(Date, "dddd"), Rows("1:1"), 0)

    lastrow = Cells(Rows.Count, "bt").End(xlUp).Row
    For x = 2 To lastrow
    matchcol = Cells(x, "cb").End(xlToLeft).Column

    If matchcol <> matchday Then GoTo E
    MsgBox Cells(x, 72) & " gets paid today."
    E:
    Next x
    End Sub


    What Im try to acomplish is a work schedual for employees. The schedual is actually written in another part of the same sheet. I cant use the code on the original part becouse theres to many varibles and spaces. So what im trying to do is consolidate the important info in another part of the sheet by just having those cells in question using a formula (=T1). The code works well when I manualy enter numbers(Times), but the times have to be posted automatically from the first part of the sheet(using the formulas). Thats why the section of cells that the code reads all contain formulas, even though some cells are blank.

  8. #8
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    I apologize, I was reading what you wanted to do completely backwards. I think this is closer to a solution. The do loop works, I didn't test the rest.

    Code:
    Private Sub CommandButton6_Click()
    Dim lastrow As Long, x As Long
    Dim matchday As Integer, matchcol As Integer
    Dim myarr(1 To 7)
    
    matchday = WorksheetFunction.Match(Format(Date, "dddd"), Rows("1:1"), 0)
    
    lastrow = Cells(Rows.Count, "bt").End(xlUp).Row
    For x = 2 To lastrow
    Cells(x, "cb").Select
    Do
    On Error GoTo 1
    ActiveCell.Offset(0, -1).Select
    Loop Until Application.WorksheetFunction.IsNumber(ActiveCell.Value) = True And ActiveCell.Value <> 0
    1
    If matchcol <> matchday Then GoTo E
    MsgBox Cells(x, 72) & " gets paid today."
    E:
    Next x
    End Sub
    _________________
    Hope this helps.
    Kind regards, Al.

    [ This Message was edited by: Al Chara on 2002-04-10 15:47 ]

    [ This Message was edited by: Al Chara on 2002-04-10 15:48 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com