The range A1:E50 represents an area in which users paste numeric
information from other sheets. Not all the copied cells will have
numbers in them, however. For example, a user may copy the range
A1:E50 from another sheet in which only 4 cells (e.g. A1, B5, C23 and
D14) have numbers in them, and the rest are blank. The next time, she
may paste in a range in which 7 cells (e.g. A20, B4, C13, C2, D12, E38
and E49) have numbers, with the rest being blank. Or, she may paste
in a range which only has numbers in 2 cells (e.g. B5 and C26), and so
forth.

In other words, the number of cells and the cell addresses within that
range that can contain numbers will vary anywhere from no cells at all
to all 250 cells (but usually something in between).

This copy and paste is basically handled by a simple macro so that the
user is always copying from A1:E50 of the other sheet regardless of
which, if any, cells actually have numbers in them.

Once the user has pasted into this area, any blank cell must be filled
in using a Vlookup formula. Entering the formulas into all the blank
cells in this A1:E50 range can be handled with a looping macro, but
I'm wondering if a "formula area" can be over-laid onto this range
such that when pasting that "formula area" in, only the blank cells
receive the paste and the cells which already have numbers in them are
left alone.

In other words, if I set up the range J1:N50 with Vlookup formulas in
each cell, is there a way I can copy that J1:N20 formula "holding"
area and paste it onto A1:E50 so that only the blank cells get filled
in with the formulas and the non-blank cells retain their numeric
values? (Obviously, these formulas would have relative addresses, so
that when I pasted them in from J1:N50, the cell references would
adjust accordingly.)

Many thanks,
Paul

This doesn't cycle cell by cell, but it does cycle area by area.

Option Explicit
Sub testme()

Dim myToRng As Range
Dim myToAreas As Range
Dim myFromRng As Range
Dim myArea As Range

With Worksheets("sheet1")
Set myToRng = .Range("a1:e50")
Set myFromRng = .Range("J1:N50")

Set myToAreas = Nothing
On Error Resume Next
Set myToAreas = myToRng.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If myToAreas Is Nothing Then
'do nothing--they're all filled
Else
For Each myArea In myToAreas.Areas
myArea.Formula _
= myFromRng(1).Offset(myArea.Row - myToRng.Row, _
myArea.Column - myToRng.Column) _
.Resize(myArea.Rows.Count, _
myArea.Columns.Count) _
.Formula
Next myArea
End If
End With
End Sub

You wrote: J1:N50 sometimes, but J1:N20 once. Was that a typo?

(My newsreader crashed--sorry if this is a double post.)

Paul Simon wrote:

The range A1:E50 represents an area in which users paste numeric
information from other sheets. Not all the copied cells will have
numbers in them, however. For example, a user may copy the range
A1:E50 from another sheet in which only 4 cells (e.g. A1, B5, C23 and
D14) have numbers in them, and the rest are blank. The next time, she
may paste in a range in which 7 cells (e.g. A20, B4, C13, C2, D12, E38
and E49) have numbers, with the rest being blank. Or, she may paste
in a range which only has numbers in 2 cells (e.g. B5 and C26), and so
forth.

In other words, the number of cells and the cell addresses within that
range that can contain numbers will vary anywhere from no cells at all
to all 250 cells (but usually something in between).

This copy and paste is basically handled by a simple macro so that the
user is always copying from A1:E50 of the other sheet regardless of
which, if any, cells actually have numbers in them.

Once the user has pasted into this area, any blank cell must be filled
in using a Vlookup formula. Entering the formulas into all the blank
cells in this A1:E50 range can be handled with a looping macro, but
I'm wondering if a "formula area" can be over-laid onto this range
such that when pasting that "formula area" in, only the blank cells
receive the paste and the cells which already have numbers in them are
left alone.

In other words, if I set up the range J1:N50 with Vlookup formulas in
each cell, is there a way I can copy that J1:N20 formula "holding"
area and paste it onto A1:E50 so that only the blank cells get filled
in with the formulas and the non-blank cells retain their numeric
values? (Obviously, these formulas would have relative addresses, so
that when I pasted them in from J1:N50, the cell references would
adjust accordingly.)

Many thanks,
Paul

--

Dave Peterson

Dave,

Thank you very much for the time and effort you put in on this for me
- I appreciate it very much (as well as all the other help you provide
to all of us on a daily basis). It works absolutely perfectly and is
exactly what I was looking for.

Many thanks,
Paul


Dave Peterson wrote in message ...
This doesn't cycle cell by cell, but it does cycle area by area.

Option Explicit
Sub testme()

Dim myToRng As Range
Dim myToAreas As Range
Dim myFromRng As Range
Dim myArea As Range

With Worksheets("sheet1")
Set myToRng = .Range("a1:e50")
Set myFromRng = .Range("J1:N50")

Set myToAreas = Nothing
On Error Resume Next
Set myToAreas = myToRng.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If myToAreas Is Nothing Then
'do nothing--they're all filled
Else
For Each myArea In myToAreas.Areas
myArea.Formula _
= myFromRng(1).Offset(myArea.Row - myToRng.Row, _
myArea.Column - myToRng.Column) _
.Resize(myArea.Rows.Count, _
myArea.Columns.Count) _
.Formula
Next myArea
End If
End With
End Sub

You wrote: J1:N50 sometimes, but J1:N20 once. Was that a typo?

(My newsreader crashed--sorry if this is a double post.)

Paul Simon wrote:

The range A1:E50 represents an area in which users paste numeric
information from other sheets. Not all the copied cells will have
numbers in them, however. For example, a user may copy the range
A1:E50 from another sheet in which only 4 cells (e.g. A1, B5, C23 and
D14) have numbers in them, and the rest are blank. The next time, she
may paste in a range in which 7 cells (e.g. A20, B4, C13, C2, D12, E38
and E49) have numbers, with the rest being blank. Or, she may paste
in a range which only has numbers in 2 cells (e.g. B5 and C26), and so
forth.

In other words, the number of cells and the cell addresses within that
range that can contain numbers will vary anywhere from no cells at all
to all 250 cells (but usually something in between).

This copy and paste is basically handled by a simple macro so that the
user is always copying from A1:E50 of the other sheet regardless of
which, if any, cells actually have numbers in them.

Once the user has pasted into this area, any blank cell must be filled
in using a Vlookup formula. Entering the formulas into all the blank
cells in this A1:E50 range can be handled with a looping macro, but
I'm wondering if a "formula area" can be over-laid onto this range
such that when pasting that "formula area" in, only the blank cells
receive the paste and the cells which already have numbers in them are
left alone.

In other words, if I set up the range J1:N50 with Vlookup formulas in
each cell, is there a way I can copy that J1:N20 formula "holding"
area and paste it onto A1:E50 so that only the blank cells get filled
in with the formulas and the non-blank cells retain their numeric
values? (Obviously, these formulas would have relative addresses, so
that when I pasted them in from J1:N50, the cell references would
adjust accordingly.)

Many thanks,
Paul