Worksheets("front").Cells(5, Columns.Count).End(xlToRight).Column 1
already have this code down, works great - problem is .. I dont want it to fill in column A, which happens to be blank, so code is working as intended, I need it to skip over and to start taking the next available column AFTER column H... I tried working with some offsetting but was still not able to get it to do what I need.
Any help greatly appreciated! Seems the resources on xlright isnt as common asn xlup :)
Worksheets("front").Cells(5, Columns.Count).End(xlToRight).Column 1
CodePudding user response:
First 'Available' Cell in Row
... i.e. the cell adjacent to the right of the rightmost non-empty cell
Using the End Property
- Keep in mind that this will fail if the column of the 'last' cell is hidden (give it a test so it'll sink in). If that's a possibility, rather use the Find method version.
Sub AddTodayEnd()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Front")
Dim fCell As Range: Set fCell = ws.Range("H5")
Dim lCell As Range
Set lCell = ws.Cells(fCell.Row, ws.Columns.Count).End(xlToLeft)
If lCell.Column < fCell.Column Then Set lCell = fCell
lCell.Offset(, 1).Value = Date
End Sub
Using the Find Method (Recommended)
Sub AddTodayFind()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Front")
Dim fCell As Range: Set fCell = ws.Range("H5")
Dim lCell As Range: Set lCell = fCell.Resize(, ws.Columns.Count _
- fCell.Column 1).Find("*", , xlFormulas, , , xlPrevious)
If lCell Is Nothing Then Set lCell = fCell
lCell.Offset(, 1).Value = Date
End Sub
CodePudding user response:
you could be after this;
Option Explicit
Function GetFirstAvailableColumnIndexAfterColumnH(sh As Worksheet) As Long
With sh
GetFirstAvailableColumnIndexAfterColumnH = WorksheetFunction.Max(8, .Cells(5, .Columns.Count).End(xlToLeft).Column) 1
End With
End Function
to test:
Sub test()
Dim sh As Worksheet
Set sh = Worksheets("front")
sh.Cells(5, GetFirstAvailableColumnIndexAfterColumnH(sh)).Value = Date
End Sub