Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Next blank cell in row

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

    Default

    hi-
    To the much appreciation of mudface and Russell, I was able to use a code in my project as follows:
    Private Sub CommandButton3_Click()
    For Each C In Worksheets("BALANCE SHEET").Range("D1:D10000") ' Adjust your range if necessary
    If C.Value = DateForm.Calendar2.Value Then
    C.End(xlToRight).Offset(0, 1).Value = TextBox1.Text

    End If
    Next C

    End Sub
    Im using the calendar control on a userform to seek a date on column "D" of a worksheet.
    My request was how to write a code that will identify the first empty cell in that row(to the right of the date column)so that the text in textbox1 will be posted to that empty cell.
    So this means that the text from textbox1 will be inserted into column "E" on the row that coresponds to the date that was chosen using the calander control(asuming that column "e" is empty).THIS WORKS GREAT!!
    But heres the problem-
    lets say I want to insert that same info from textbox1 to column "K" even though column "E:J" are empty. Now I know I can use the offset methode to change the numbers from (0, 1) to (0, 7), but then the code wont search for the next available cell if column "k" is nonempty- it will just replace the info! Help anyone please?

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-26 06:42, Todd_M wrote:
    hi-
    To the much appreciation of mudface and Russell, I was able to use a code in my project as follows:
    Private Sub CommandButton3_Click()
    For Each C In Worksheets("BALANCE SHEET").Range("D1:D10000") ' Adjust your range if necessary
    If C.Value = DateForm.Calendar2.Value Then
    C.End(xlToRight).Offset(0, 1).Value = TextBox1.Text

    End If
    Next C

    End Sub
    Im using the calendar control on a userform to seek a date on column "D" of a worksheet.
    My request was how to write a code that will identify the first empty cell in that row(to the right of the date column)so that the text in textbox1 will be posted to that empty cell.
    So this means that the text from textbox1 will be inserted into column "E" on the row that coresponds to the date that was chosen using the calander control(asuming that column "e" is empty).THIS WORKS GREAT!!
    But heres the problem-
    lets say I want to insert that same info from textbox1 to column "K" even though column "E:J" are empty. Now I know I can use the offset methode to change the numbers from (0, 1) to (0, 7), but then the code wont search for the next available cell if column "k" is nonempty- it will just replace the info! Help anyone please?
    You can try something like this:

    Code:
    If c.Offset(0, 7).Text = "" Then
        c.Offset(0, 7).Text = TextBox1.Text
    Else
        c.Offset(0, 7).End(xlToRight).Offset(0, 1).Value = TextBox1.Text
    End If
    Hope it helps,

    Russell

    [ This Message was edited by: Russell Hauf on 2002-02-26 08:42 ]

Some videos you may like

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
  •