Next blank cell in row

Todd_M

Board Regular
Joined
Feb 24, 2002
Messages
117
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?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top