The formulas would go into cells E1, E2, E3, E4. Given your code, only
cell E1 would be populated which correlates to SHIP ABC. I need E2,
E3, E4 populated for ships DEF, GHI, JKL.

The purpose is to track ship arrival estimates, and to do that would
be to find the first empty column in the range and populate it with
arrival dates for each ship/row. So once an empty column is found, I
need to populate all rows within that column.

I've simplified the range to A1:H4 for this example, but it would be
expanded when I apply it into my spreadsheet.

Dave Peterson wrote:
I don't understand the rules.

If E1:E2 are filled in, where would the 4 formulas go? Are you limited to just
the cells in A1:H4?

Naji wrote:

Thanks for your help Dave, however the solution you provided only
fills E1..how can I explicitly enter formulas for E2, E3, ETC?

The formulas for E 1 thru E4 in this case would be:

=VLOOKUP($A1,Sheet2!$A$1:$D$9,2,FALSE)
=VLOOKUP($A2,Sheet2!$A$1:$D$9,2,FALSE)
=VLOOKUP($A3,Sheet2!$A$1:$D$9,2,FALSE)
=VLOOKUP($A4,Sheet2!$A$1:$D$9,2,FALSE)

Thanks for your help!

Dave Peterson wrote:
One way:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myRow As Range
Dim myEmptyCells As Range

With Worksheets("sheet1")
Set myRng = .Range("A1:H4")
End With

Set myEmptyCells = Nothing
On Error Resume Next
Set myEmptyCells = myRng.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If myEmptyCells Is Nothing Then
MsgBox "no empties!"
Exit Sub
End If

For Each myRow In myRng.Rows
Set myEmptyCells = Nothing
On Error Resume Next
Set myEmptyCells = myRow.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If myEmptyCells Is Nothing Then
'keep looking
Else
myEmptyCells.Cells(1).Formula = "whatever you want"
'stop looking
Exit For
End If
Next myRow

End Sub

Naji wrote:

Hello guys and Happy Friday,

I think this is pretty simple, but I can't figure out how to do this
with columns, as opposed to rows.

Here is a simplified version of my set up to help demonstrate my
needs:

A B C D E F G H
ABC 1/2 1/3 1/4
DEF 1/3 1/4 1/3
GHI 1/5 1/2 1/1
JKL 1/6 1/8 1/9

I need to figure out a macro that looks at this range (A1:H4) and
finds the first empty column in the range, and enters a vlookup
function in the cells. In this case I would need it to go through the
range and then enter vlookup functions for E1:E4, like
=VLOOKUP($A1,Sheet2!$A$1:$D$9,2,FALSE) for E1, and so forth going down
the rows in column E. Running the macro again would then insert the
same formulas for column F with the vlookup dates. Any pointers or
help with this matter would be greatly appreciated!! Thanks!

--

Dave Peterson

--

Dave Peterson