Pasting an unknown range
 

Hello all,

I am working on a macro that will analyze all of my formulas on the
activesheet and when it finds a formula that meets it criteria it will
copy the formula and past it into another worksheet. The macro is
working accept for the fact that I can not get it to past the formula
where I want it to. I want it to past it into the same cell reference
in a different worksheet (i.e. cell C6 in sheet 3 to cell C6 in new
sheet.). Since the reference cells are unknown I can not hard code it.
Any help would be GREATLY appreciated.


NMD

Pasting an unknown range
 

IF you are using FIND then:

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox c.Row & "," & c.Column <== row & column you need
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress

End If
End With

HTH

(If not, post sample of your code)

"nmd030583" wrote:



Hello all,

I am working on a macro that will analyze all of my formulas on the
activesheet and when it finds a formula that meets it criteria it will
copy the formula and past it into another worksheet. The macro is
working accept for the fact that I can not get it to past the formula
where I want it to. I want it to past it into the same cell reference
in a different worksheet (i.e. cell C6 in sheet 3 to cell C6 in new
sheet.). Since the reference cells are unknown I can not hard code it.
Any help would be GREATLY appreciated.


NMD

Pasting an unknown range
 

Supposed you want to paste the formula in the active cell:
Worksheets("newsheetname").Cells(ActiveCell.Row, ActiveCell.Column).Formula
= ActiveCell.Formula


Regards,
Stefi

€žnmd030583€� ezt Ã*rta:



Hello all,

I am working on a macro that will analyze all of my formulas on the
activesheet and when it finds a formula that meets it criteria it will
copy the formula and past it into another worksheet. The macro is
working accept for the fact that I can not get it to past the formula
where I want it to. I want it to past it into the same cell reference
in a different worksheet (i.e. cell C6 in sheet 3 to cell C6 in new
sheet.). Since the reference cells are unknown I can not hard code it.
Any help would be GREATLY appreciated.


NMD

Pasting an unknown range
 

HTH

Thanks for your quick reply. Below is my code (I should have posted
this the first time sorry). I am just not shure where to tell it to
paste to. Thank you.

NMD



Sub FindTheNumbersInFormulas()
Dim lngN As Long
Dim rngAll As Excel.Range
Dim rngCell As Excel.Range
Dim strForm As String
Dim strPart As String

Set rngAll = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
Application.ScreenUpdating = True
Application.CutCopyMode = False
For Each rngCell In rngAll
strForm = rngCell.Formula
For lngN = 1 To Len(strForm)
strPart = Mid$(strForm, lngN, 2)
If strPart Like "+#" Or strPart Like "-#" Then
rngCell.Select
Selection.Copy
Sheets("consant map").Select
ActiveSheet.Paste
Sheets("Sheet6").Select
Exit For
End If
Next 'lngN
Next 'rngCell
Application.ScreenUpdating = True
Set rngAll = Nothing
Set rngCell = Nothing
End Sub