output data to next empty cell in a row

[cccbzg]

Hi,

I want to put the value in DDXit in the next available cell on the active row. As a stop gap for testing, I just hard-coded the column (last two lines), but I'm at the point that I need to get the thing to work.

All help is much appreciated.

Bonnie

With Sheets("DATA ANALYSIS")
Set NextRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
MsgBox NextRow
'find last used cell on the row to the right
ActiveCell.End(xlToRight).Select

'move one cell to the right from the last used cell
ActiveCell.Offset(0, 1).Select
'
End With

Cells(arow, 4) = SErng
Cells(arow, 5) = DDXit

'****************

 

[RickXL]

Hi.

I would try something like this:

Sub FillNextCell()

    Dim NextCell As Range

    With Sheets("DATA ANALYSIS")
        Set NextCell = .Cells.Find("", Range("A1"), , , xlByRows, xlNext)
        NextCell = SErng
        Set NextCell = .Cells.Find("", Range("A1"), , , xlByRows, xlNext)
        NextCell = DDXit
    End With
    
End Sub

The FIND statement locates the next blank cell after cell A1.
I have used FIND twice because it copes with the case when a row becomes full because it automatically moves on to the next row.

 

[cccbzg]

Thanks so very much! That helped, but I need the row to be a variable "arow." I had Cells(arow, 5) = DDXit to put DDXit in col 5 of the variable row "arow." I'm not sure where to put that in the code you were kind enough to provide.

Again, my thanks.
Bonnie

 

[RickXL]

Instead of starting the search at cell A1 as I have you will need to pick a cell in the row you want.

You can use Range or Cells to set the first cell.
Something like this, perhaps:

Sub FillNextCell()

    Dim NextCell As Range
    Dim aRow As Long
    
    SErng = "#####"
    DDXit = "@@@@@"
    
    aRow = 7
    With Sheets("DATA ANALYSIS")
        Set NextCell = .Cells.Find("", .Cells(aRow, 1), , , xlByRows, xlNext)
        NextCell = SErng
        Set NextCell = .Cells.Find("", .Cells(aRow, 1), , , xlByRows, xlNext)
        NextCell = DDXit
    End With
    
End Sub

Instead of .Cells(aRow, 1) you could use .Range("A" & aRow).
In fact, you can use any range object that defines a suitable starting point.