VBA - Code for entering data one at a time for first blank offset cell

[sidt87]

Hi All,

I am having a hard time trying to make my code find the first blank offset cell and only entering data to that first blank cell. So in this case, I want the data to be only entered in B1 because that is the first blank offset cell to column A. But what I'm having issues with is that it would also enter it to B3 or any offset cell to column A that has data in it. Any Help will be appreciated!

	A	B	C
1	4555
2
3	4556
4

<tbody>
</tbody>

This is my code:

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">PrivateSub CommandButton1_Click()

Dim iRow As Range
Dim i AsLong
Dim ws As Worksheet
Set ws = Worksheets("RewinderData")
Set iRow = Range("A1:B10")


ForEach cell In iRow
If cell.Value <>""And cell.Offset(0,1).Value =""Then
cell.Offset(0,1).Value =Me.textbox_quantity.Value
EndIf
Next</code>

 

[My Aswer Is This]

Try this:

Sub Test()
'Modified 3/1/17 9:02 PM EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Cells(Rows.Count, "B").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, 1).Value <> "" Then
            Cells(Lastrowa, 2).Value = ActiveSheet.textbox_quantity.Text
            Lastrowa = Lastrowa + 1
        End If
    Next
Application.ScreenUpdating = True
End Sub

 

[sidt87]

My Aswer Is This,

I adjusted your code a little and got it to enter 1 cell at a time, but its not hitting the offset cell. Its going down the blank row 1 at a time. Here's what I have:

Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim lastrowa As Long
Lastrow = Cells(Rows.Count, "a").End(xlUp).Row
lastrowa = Cells(Rows.Count, "b").End(xlUp).Row
For i = 1 To Lastrow
If Cells(i, 1).Value <> "" Then
Cells(lastrowa, 2).Value = Me.textbox_quantity.Value
lastrowa = lastrowa + 1
End If
Next
Application.ScreenUpdating = True

 

[My Aswer Is This]

I never really knew what you were referring to when you said:

and got it to enter 1 cell at a time, but its not hitting the offset cell.

What is a offset cell?

Tell me where in what column do you want the values entered into.

Offset cell I do not understand

Offset cell could mean offset down offset up offset left offset right
It would be easier to tell me what column

In your script I see this:

Cells(lastrowa, 2).Value = Me.textbox_quantity.Value


I see nothing about a offset cell in this script

 

[sidt87]

The last code I posted was a test of the code you provided to see if it helped so it may not be correct for what I'm trying to do. Although it did work with entering 1 cell at a time which is a good start.

But referring back to my original post, I'm trying to enter data one at a time on column B but only next to (or what I keep referring to Offset (0,1)) of a cell in Column A that has something in it. So if I had data in A3 and A5, I want the code to enter the data to only B3. Then the next time I enter the data, it would show up in B5 because that is the next blank cell that is the offset of A5. The trouble I'm having is, if have multiple cells in column A with something, and its offset cell in column B is blank, they all get the same value entered to column B.

I hope I didn't confuse you too much and understand what I'm trying to achieve..hopefully its possible.

Thanks!