PDA

View Full Version : Help with find/replace loop.



NUTSUECOW
11-28-2018, 10:23 AM
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

rlv
11-28-2018, 11:53 AM
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.


https://www.access-programmers.co.uk/forums/attachment.php?attachmentid=46223&stc=1&d=1358570334


http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=46224&stc=1&d=1358570334

大灰狼1976
12-07-2018, 01:08 AM
Range("Ai:Hc").Select ---> Range("A" & i & ":H" & c).Select
It would be better if you used the attached instructions.