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