MS Excel - Offset function when using Macro to enter data?

You can use the Offset parameter in conjunction with End(xlToLeft).

For example, in row 1 the following code line will select the next available column for data entry:

Range("IV1").End(xlToLeft).Offset(0, 1).Select

This tells Excel to locate the last column in row 1 containing data and select the cell '0' rows down and '1' column to the right of that column.

You can use this in a loop for a column. Here is an example that, each time the macro is called will insert the row number in the next available column to the right. Enter random data in column A as far down as you wish. Then run this macro 3 or 4 times to view the 'mechanics'.

Sub Find_LastCol()

Dim i, LastRow

LastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow

Cells(i, "IV").End(xlToLeft).Offset(0, 1).Value = i

Next

End Sub

Your question is extremely confusing in that i'm uncertain what you're attempting to do. You point out 'in Excel fill' -- are you filling the 'countif' formulation down a column, as a result you have distinctive formulation to handle? Do you have the formulation in only one cellular? do you prefer the formulation to continually function on the comparable selection, i.e. A1:E40? Or, do you prefer the kind to be dynamic and alter to comprise new cells extra?

the following should give you the last unused cell in row 1 and place the date in that cell.

dim startcell as range

set startcell = range("IV1").end(xltoleft).offset(0,1)

startcell.value = Date