How can I cut+paste formulas+formats?

[colwyn]

I've got code which transfers:
a. all formulas and formats to another location.
b. specific desired formulas but not cell formats to another location.

The s/s is over 330,000 rows deep which makes manual changes
impossible.

I attach small s/s containing both codes.

What I want to do is copy+paste both formulas and formats from only
those cells which have a number in the cell below to cells 4 columns
along and two rows down. The s/s illustrates this.

Can anyone help please?
Big thanks.
Colwyn.


+-------------------------------------------------------------------+
|Filename: example.xls |
|Download: http://www.thecodecage.com/attachment.php?attachmentid=13|
+-------------------------------------------------------------------+

 

[shg]

Maybe like this:

Code:
--------------------
Sub macro2()
Dim cell As Range

For Each cell In Range("F1", Cells(Rows.Count, "F").End(xlUp))
If cell.HasFormula And IsNumeric(cell.Offset(1).Value) Then
cell.Copy Destination:=cell.Offset(2, 4)
End If
Next cell
End Sub

 

[colwyn]

SHG -thanks for your reply. It works a treat. All I had to do was chang
"copy" to "cut" (fifth line of code). Very helpful!
Big thanks.
Colwyn.

ps, have looked for 'thread tools' in order to mark "solved" but canno
locate (??

 

[colwyn]

shg - sorry but an error has come to light.
The problem is, ALL cells in column F containing formulas are being
acted on.
I only want those cells containing the bottom-most formula in each
series acted upon.
If you see macro2, this does what I want except that it doesn't
transfer the format over.
Your code seems to do what macro1 does.
Is there a way to get macro2 to cut/paste formats along with the
formulas??
Big thanks.
Colwyn.


+-------------------------------------------------------------------+
|Filename: example 2.xls |
|Download: http://www.thecodecage.com/attachment.php?attachmentid=14|
+-------------------------------------------------------------------+

 

[shg]

Perhaps ...

Code:
--------------------
Sub x()
Dim cell As Range

For Each cell In Range("F1", Cells(Rows.Count, "F").End(xlUp))
With cell
If .HasFormula And Not .Offset(1).HasFormula Then
.Cut Destination:=.Offset(2, 4)
End If
End With
Next cell
End Sub

 

[colwyn]

That works perfect.
Fully appreciate your help and patience.
Big thanks.
Colwyn.