Find first empty cell in range and paste formula??

[Naji]

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]

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

 

[Ron de Bruin]

You can use this maybe
http://www.rondebruin.nl/last.htm

Try the last function

 

[Naji]

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]

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]

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]

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???

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.

 

[Naji]

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.

 

[Naji]

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]

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

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.

 

[Naji]

Thank you for your help. I appreciate it. I figured it out, however it
feels like I'm doing it the long way. But all is good. To answer your
question, the first empty cell found is always in row 1..so in this
case it would be in E1, F1, G1, etc. Once that cell is found, it can
use the same column to fill in the remaining rows for the other
ships.Once I find the first EMPTY cell for SHIP ABC, (1/2, 1/3, 1/4,
EMPTY), then I use that column (E) to fill in explicit formulas. I
accomplished this by repeating your sub FOUR times and just changing
this:

Set myRng = .Range("A1:H4")

to

Set myRng = .Range("A1:H1")
Set myRng = .Range("A2:H2")
Set myRng = .Range("A3:H3")
Set myRng = .Range("A4:H4")

For each of the four times. This way, it looks at ship, finds the
first empty cell, and pastes a formula into it.

Thanks for your help and have a great weekend!

 

[Dave Peterson]

Did you try anything based on this suggestion?

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

Thank you for your help. I appreciate it. I figured it out, however it
feels like I'm doing it the long way. But all is good. To answer your
question, the first empty cell found is always in row 1..so in this
case it would be in E1, F1, G1, etc. Once that cell is found, it can
use the same column to fill in the remaining rows for the other
ships.Once I find the first EMPTY cell for SHIP ABC, (1/2, 1/3, 1/4,
EMPTY), then I use that column (E) to fill in explicit formulas. I
accomplished this by repeating your sub FOUR times and just changing
this:

Set myRng = .Range("A1:H4")

to

Set myRng = .Range("A1:H1")
Set myRng = .Range("A2:H2")
Set myRng = .Range("A3:H3")
Set myRng = .Range("A4:H4")

For each of the four times. This way, it looks at ship, finds the
first empty cell, and pastes a formula into it.

Thanks for your help and have a great weekend!