last cell on row

[Terry V]

Hello
Ive been trying desparately to accomplish the following:

I need to check for a value in a row on a cell by cell basis.
Columns B,C,F,G may or may not have a value in them.
If B is empty, then clicking on a commandbutton places the caption text into
the cell and moves to column C; Clicking on a command button again places
its caption text into that cell and jumps to Col F; again clicking the
command button places the caption text in the cell in Column F and again
with G.
When those cells on that row are filled, I then need to have excel find the
last row + 1 to begin it all over again.

Column A has a formula in it down to row 60 and in the 2nd row (A2:K2) have
column heading in them.
Columns D, E, H, I are all hidden columns with formulas.

How can I accomplish this?
Ive tried :
Range("B65536").End(xlUp).Offset(1, 0), but If column B has a value and C
does not, then the offset brings it down another row.
Im thinking that I should be using xlRight somehow, but I can't seem to get
my head around it.

Could anyone shine a little light for me?
Thank you so much

Terry V

 

[Dave Peterson]

So you always fill the row from B to C to F to G.

Option Explicit
Sub testme01()

Dim DestCell As Range
Dim myCols As Range
Dim HowManyFilled As Long

With Worksheets("sheet1")
Set myCols = .Range("b:c,f:g")
Set DestCell = .Cells(.Rows.Count, "B").End(xlUp)
HowManyFilled = Application.CountA(Intersect(DestCell.EntireRow, myCols))
Select Case HowManyFilled
Case Is = 4: Set DestCell = DestCell.Offset(1, 0) 'down a row
Case Is > 1: Set DestCell = DestCell.Offset(0, HowManyFilled + 2)
Case Else: Set DestCell = DestCell.Offset(0, HowManyFilled)
End Select
End With

MsgBox DestCell.Address
DestCell.Value = "whatever you wanted"
End Sub

Another way would just be to check to see if those cells are filled (C,F,G on
the same row as the B cell):

Option Explicit
Sub testme01A()

Dim DestCell As Range
With Worksheets("sheet1")
Set DestCell = .Cells(.Rows.Count, "B").End(xlUp)
With DestCell
If IsEmpty(DestCell.Offset(0, 1)) Then
Set DestCell = DestCell.Offset(0, 1)
ElseIf IsEmpty(DestCell.Offset(0, 4)) Then
Set DestCell = DestCell.Offset(0, 4)
ElseIf IsEmpty(DestCell.Offset(0, 5)) Then
Set DestCell = DestCell.Offset(0, 5)
Else
Set DestCell = DestCell.Offset(1, 0)
End If
End With
End With

MsgBox DestCell.Address
DestCell.Value = "whatever you wanted"
End Sub

 

[Dave Peterson]

And I might have used the .end(xltoright) if there had not been a gap in your
columns.

 

[Terry V]

Wow,
Thats so cool.
Thank you so much; not only did you help me with the particular problem, but
I learned a fair amount from your code.

Thank you so much
Terry V