Change Formulas W/VBA

[jlclyde]

I have put in code to copy formulas to all the correct cells, now I
need to go in and replace all of the ones with what the row number
is. The below code does nto work. Active cell never happens so it
bugs out.

Thanks,
Jay

LstRow = Sht.Range("A65536").End(xlUp).Row
Set Rng = Sht.Range("F3:F" & LstRow)
Sht.Range("F" & LstRow) = Sht.Range("F1").Formula

For Each i In Rng
i = Sht.Range("f1").Formula
ActiveCell.Replace What:="1", Replacement:=i.Row, LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
i = i.Value
Next i

 

[Per Jessen]

Hi

Your code is a bit ambigous.

You use the variable "i" as "counter" in a loop, then to hold a formula.
Two variables are needed!
Is it each cell in the Rng which shall replace "1" with a formula?

I think this is what you need:

Sub Jay()
Dim Rng As Range
Dim cell As Range

Set Sht = Sheets("Sheet1")
LstRow = Sht.Range("A65536").End(xlUp).Row
Set Rng = Sht.Range("F3:F" & LstRow)
Sht.Range("F" & LstRow) = Sht.Range("F1").Formula

Sht.Range("F1").Copy
For Each cell In Rng.Cells
If cell.Value = 1 Then
cell.PasteSpecial xlPasteFormulas
End If
Next
Application.CutCopyMode = xlCopy
End Sub

Regards,
Per