So it looks like I could look at row 1 to find the next available column?

And when you say the entire column, you really don't mean that do you? All 64k
(or 1m) rows????

I'm gonna guess that you can pick out a column (I'll use column A) to find the
last used row.


Dim LastRow as long
Dim NextCol as long

with worksheets("sheetnamehere")
lastrow = .cells(.rows.count,"A").end(xlup).row
NextCol = .cells(1,.columns.count).end(xltoleft).column + 1

.range(.cells(1,nextcol), .cells(lastrow,nextcol)).formula _
= "=VLOOKUP($A1,Sheet2!$A$1:$D$9,2,FALSE)"
end with

I think???


Naji wrote:

Well, your formula populates CELL E1 with "whatever you want" given
your example. Cells E2, E3, E4 would remain blank. I need the entire
column to be populated with vlookup functions that reference to
SHIPS(ABC,DEF,GHI,etc) in column A. With my example, the first row
gives the COLUMN names and does not exist. I simplified the problem to
make it easier to understand, so no it won't be restricted to cells
A1:H4. Once the first empty column is located, I need to fill every
row in that column with an explicit formula. The 4 Formulas would in
this case go into E1,E2,E3,E4 for corresponding Ships ABC, DEF, GHI,
JKL.

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

--

Dave Peterson