N
nmd030583
Oaky... I am writing a macro to help me find were I have added (or
subtracted) a hard coded numbers into an equation. The original macro
highlighted the cell when it located a formula that met this criterion.
Now I need it to copy the cell and paste in into another worksheet in
the same cell that it found it. Below is my code. Help would be greatly
appreciated. Thank you in advance.
-NMD
(a.k.a.- VBA Newbie in over his head)
Sub FindTheNumbersInFormulas()
Dim lngN As Long
Dim rngAll As Range
Dim rngCell As Range
Dim strForm As String
Dim strPart As String
Set rngAll = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
Application.ScreenUpdating = True
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
ActiveCell.Select
ActiveSheet.Paste
Sheets("Sheet6").Select
Exit For
End If
Next 'lngN
Next 'rngCell
Set rngAll = Nothing
Set rngCell = Nothing
Application.CutCopyMode = False
End Sub
subtracted) a hard coded numbers into an equation. The original macro
highlighted the cell when it located a formula that met this criterion.
Now I need it to copy the cell and paste in into another worksheet in
the same cell that it found it. Below is my code. Help would be greatly
appreciated. Thank you in advance.
-NMD
(a.k.a.- VBA Newbie in over his head)
Sub FindTheNumbersInFormulas()
Dim lngN As Long
Dim rngAll As Range
Dim rngCell As Range
Dim strForm As String
Dim strPart As String
Set rngAll = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
Application.ScreenUpdating = True
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
ActiveCell.Select
ActiveSheet.Paste
Sheets("Sheet6").Select
Exit For
End If
Next 'lngN
Next 'rngCell
Set rngAll = Nothing
Set rngCell = Nothing
Application.CutCopyMode = False
End Sub