Consulting

Results 1 to 3 of 3

Thread: Help with find/replace loop.

  1. #1

    Help with find/replace loop.

    I thought I had everything setup correctly. But when the find replace function runs, its not recognizing my integers set. I want f = 1 and use $E$f to actually find $E$1, but it is actually searching for "$E$f" in my formula's. I am so stuck on how to get around this. any help would be greatly appreciated.


    Sub Data_Maintenance_Macro()
    '
    ' Data_Maintenance_Macro Macro
    '








    Sheets("OT Export Txt").Select


    Dim i As Integer
    Dim c As Integer
    Dim f As Integer
    Dim r As Integer


    i = 11
    c = 20
    f = 1
    r = 2




    Do While i < 401


    Range("Ai:Hc").Select
    Selection.Replace What:="$E$f", Replacement:="$E$r", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

    Selection.Replace What:="$F$f", Replacement:="$F$r", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False


    Selection.Replace What:="$B$f", Replacement:="$B$r", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False


    Selection.Replace What:="$D$f", Replacement:="$D$r", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False


    Selection.Replace What:="$A$f", Replacement:="$A$r", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False


    Selection.Replace What:="$J$f", Replacement:="$J$r", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False


    Selection.Replace What:="$G$f", Replacement:="$G$r", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

    i = i + 10
    c = c + 10
    f = f + 1
    r = r + 1

    Loop








    End Sub

  2. #2
    Instead of this


    Selection.Replace What:="$E$f", Replacement:="$E$r", LookAt:=xlPart, _

    do this


    Selection.Replace What:="$E$" & f, Replacement:="$E$" & r, LookAt:=xlPart, _

    Also, it's really helpful to others if you can use code tags when posting code.






  3. #3
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    Range("Ai:Hc").Select ---> Range("A" & i & ":H" & c).Select
    It would be better if you used the attached instructions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •