Copying Formulas in Excel 97

[zp162002]

Does anyone have an idea on how to select a range in a particular row (Ex. U9A9) and to loop through the range testing if there is a formula in each cell. If there is a formula, I would like to copy the formula to each cell in the column.

 

[SkipVought]

zp,

dim c as range
for each in range("U9:DA9")
  with c
    if .value <> .formula then
      .copy
      range(cells(1, .column), cells(cells.rows.count, .column)).pastespecial xlPasteFormulas
    end if
  end with
next

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[zp162002]

Hi Skip,

Thanks for the help. The code you provided did work but only for the first column in the range. I still need to copy each formula in U9A9 down its respective column. Is there an easy way to loop through this?

 

[SkipVought]

sorry, I left out the range object

Dim c As Range
For Each c In Range("U9:DA9")
  With c
    If .Value <> .Formula Then
      .Copy
      Range(Cells(1, .Column), Cells(Cells.Rows.Count, .Column)).PasteSpecial xlPasteFormulas
    End If
  End With
Next

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[zp162002]

Hi Skip. I tried but the code only worked for the first row and it copied the formula from rows 9 - 65536. I need it to stop at the value set by NumOfRows. I also need it to work for columns V9, W9, X9, etc. Thanks a lot for your help. Here is the code I have so far:

Public Function CopyColumns()
NumOfRows, NumOfCol as long
Dim c As Range

NumOfRows = CountRows()
NumOfRows = NumOfRows + 9

Windows("wfjrpla.xls").Close
Set ColRng = Sheets("results").Range("A9A9")
NumOfCol = ColRng.Columns.Count

For Each c In Range("U9A9")
With c
If .Value <> .Formula Then
.Copy
Range(Cells(1, NumOfRows), Cells(Cells.Rows.Count, .Column)).PasteSpecial xlPasteFormulas
End If
End With
Next

End Function

 

[SkipVought]

Public Function CopyColumns()
NumOfRows, NumOfCol as long
Dim c As Range

NumOfRows = CountRows()
NumOfRows = NumOfRows + 9

Windows("wfjrpla.xls").Close
Set ColRng = Sheets("results").Range("A9:DA9")
NumOfCol = ColRng.Columns.Count

For Each c In Range("U9:DA9")
  With c
    If .Value <> .Formula Then
      .Copy
      Range(Cells(9, .Column), Cells(NumOfRows, .Column)).PasteSpecial xlPasteFormulas
    End If
  End With
Next
    
End Function

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

Hi zp162002,

I don't see why Skip's code should only work on a single column, BUT ..

[blue].Formula[/blue] always returns a string, and
[blue].Value[/blue] returns the contents of the cell, which may, or may not, be a string

.. so the two are not necessarily comparable. It would be better to code ..

[blue]If [red]CStr([/red].Value[red])[/red] <> .Formula Then[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[SkipVought]

Tony,

Thanx! That worked the way it should!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[zp162002]

I owe you guys my life. Thanks so much.

 

[xlbo]

Just another way but making use of inbuilt properties

you could change:

With c
    If .Value <> .Formula Then

to

With c
    If .hasformula = true Then

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[TonyJollans]

Quite right, Geoff. That's better!

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[SkipVought]

can't you feel the synergy!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

Ah'm feelin' it Skip ;-)
Not sure if that's available for all versions of excel though......

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[SkipVought]

ahhhhhhhh.....,,,,,

I am waxing meloncholy for the good 'ol days of '95, those waking moments, days of early discovery, XLODBC, Dialog Sheets and even getting mawkishly maudlin about REAL MACROS!

shedding a tear

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884