I still don't understand.

Are you always filling 4 cells?

Or can the first cell be E2 or E3 or even E4?

If it's always the first row:

Dim NextCol as long
with worksheets("sheetnamehere")
NextCol = .cells(1,.columns.count).end(xltoleft).column + 1

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

Naji wrote:

Hmm, now I am confused...

When I say "the entire column", I mean all cells within that range.
Since we are only talking about A1:H4, once an empty cell is found for
SHIP ABC, which in this example would be in COLUMN E, I would only go
down and fill in the next three rows, E2, E3, E4. That's it.

Your code works great, it just seems to need one more kind worked out,
which would be to have

myEmptyCells.Cells(1).Formula = "whatever you want"

Also be pasted into cells E2, E3, E4....instead of only E1.

I do not care about the last used row. I am explicitly identifying the
last used row by saying stating the range as A1:H4. I have no need to
go down further beyond ROW 4.

I do not understand the code in your most recent reply.

Apologies for the confusion. Maybe I should have laid out the problem
better...hope I clarified a bit?

Thanks for your help and patience.

Dave Peterson wrote:
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

--

Dave Peterson