Edit REplace String

[Tami]

We have a spredsheet tool that is protected therefore we have this macro to
do edit/replace on a Percent-to-Total row.

one of the formulas that requires editing is =if(a$2=0,0,a$1/a$2), then
there's one for column B as well, etc...

We need a macro to edit/replace "$1/" with "$x/" AND "$2)" with "$y)" and
"$2=" with "$y=". We prompt the user for the two row numbers: X and Y

What is the code to conver x to "$x/" in order to to the edit/replace?

sorry if this is confusing...let me know if you have questions.
thanks in advance for any assistance.
tami

Sub percent_to_total_prompt()
'
' edit_replace_v2 Macro
' Macro recorded 7/20/2009 by THalliday

Dim fStr As String
Dim tStr As String
Dim myRng As Range
Dim myUnlockedCells As Range
Dim myCell As Range
Dim myPWD As String
Dim f1Str As String
Dim t1Str As String
Dim t2str As String
Dim den1 As String
Dim den2 As String


myPWD = "paspas"

Dim l As String

Dim num As String
Dim den As String

num = "$1/"
den1 = "$2="
den2 = "$2)"
f1Str = "$" & fStr & "/"
t1Str = "$" & tStr & "="
t2str = "$" & tStr & ")"




l = ActiveCell.Address





lr = ActiveCell.Row


If MsgBox("Are you sure you want to insert a % to Total line where" & _
" your cursor is?", vbCritical + vbYesNo + vbDefaultButton2) = vbYes Then


ActiveSheet.unprotect Password:="paspas"
fStr = InputBox(Prompt:="Enter Row x")


If Trim(fStr) = "" Then
ActiveSheet.Protect Password:="paspas", DrawingObjects:=True, _
Contents:=True, Scenarios:=True, AllowInsertingRows:=False, _
AllowDeletingRows:=False
Exit Sub
End If

tStr = InputBox(Prompt:="Enter Row y")
If Trim(tStr) = "" Then
ActiveSheet.Protect Password:="paspas", DrawingObjects:=True, _
Contents:=True, Scenarios:=True, AllowInsertingRows:=False, _
AllowDeletingRows:=False
Exit Sub
End If

ActiveCell.EntireRow.Select
Range("percent_line").Copy
ActiveCell.Insert Shift:=xlDown
Application.CutCopyMode = False
Cells(lr, Range("view_code_column").Column).Select
ActiveCell.Offset(-1, 0).Select
ActiveCell.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False




ActiveCell.EntireRow.Select
Selection.Replace What:=num, Replacement:=f1Str, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Selection.Replace What:=den1, Replacement:=t1Str, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Selection.Replace What:=den2, Replacement:=t2str, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Range(l).Select
MsgBox "Verify the Edit/Replace worked.", vbCritical



ActiveSheet.Protect Password:="paspas", DrawingObjects:=True, _
Contents:=True, Scenarios:=True, AllowInsertingRows:=False, _
AllowDeletingRows:=True

End If

End Sub

 

[Don Guillett]

Try this idea. Quick

Sub changeformula()
Dim x As String
Dim y As String
'if(a$2=0,0,a$1/a$2),
x = InputBox("Enter row x")
y = InputBox("Enter row y")
Columns(2).Replace "A$2=0", "A$" & x & "=0"
Columns(2).Replace "/A$2", ".A$" & y
End Sub

 

[Tami]

It worked! Thanks so much!

tami

Try this idea. Quick

Sub changeformula()
Dim x As String
Dim y As String
'if(a$2=0,0,a$1/a$2),
x = InputBox("Enter row x")
y = InputBox("Enter row y")
Columns(2).Replace "A$2=0", "A$" & x & "=0"
Columns(2).Replace "/A$2", ".A$" & y
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)