quick way to control find replace?

[tonyjyoo]

Hello,

I have a file that I change some linking for at the beginning of every month, but it has THOUSANDS of rows it changes.

What I've been doing so far is a control find replace (for example, replace "S:/Jan example" to "S:/Feb example"... etc. but it takes me a very long time.

I tried implementing a macro to find and replace but it still takes same amount of time.

 

[L. Howard]

Post the code you are using, the one that apparently works, but is slow.

Might be some way to speed it up.

Howard

 

[tonyjyoo]

Post the code you are using, the one that apparently works, but is slow.

Might be some way to speed it up.

Howard

Hey Howard. This is the code. I used ranges "C1" and "C2" as place holders for changing months (e.g. January and February).

Sub Find_Replace()


'Optimus Prime
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
Dim Findtext As String
Dim Replacetext As String
Findtext = Range("C1").Value
Replacetext = Range("C2").Value
Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


'Turn on Screen Updates
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
End Sub

 

[L. Howard]

And I should have asked for a sample of the row data.

Can you post, say, five maybe ten, example rows of the data that is being searched... and their placement on the sheet. What rows/columns and such.

Howard

 

[tonyjyoo]

And I should have asked for a sample of the row data.

Can you post, say, five maybe ten, example rows of the data that is being searched... and their placement on the sheet. What rows/columns and such.

Howard

Hey Howard,

Here's a sample of a formula within a cell:

VLOOKUP($B301,'S:\NPH Accounting\NPH Accounting Department\Treasury\2017\2 MONTHLY\Cash Position - Mnthly\NPH\[02 NPH Cash Position 17.xlsm]NPH'!$A$10:$AF$512,MATCH(E$8,'S:\NPH Accounting\NPH Accounting Department\Treasury\2017\2 MONTHLY\Cash Position - Mnthly\NPH\[02 NPH Cash Position 17.xlsm]NPH'!$A$6:$AF$6,FALSE),FALSE)

Where "B" is looking at a description, and "E" is looking at each date within the month... note that there are roughly 400 rows with 31 columns (for 31 days)... Hope this helps.

 

[L. Howard]

No, that is no help, if I understand what you are trying to do. The formula is of no use to me as far as I can tell.

You are searching cells in a column for the string "S:/Jan" to be replaced with "S:/Feb". (Feb for Jan, next month Mar for Feb) And you have the "Search FOR" string in C1 and the "Replace WITH" string in C2.

Your code is looking in Cells.

On my test sheet I have 10,000+ rows of text like these in column A. It is merely some text that includes Jan in it, using the second row as another example where Jan is also in it but in a different place. Repeated down 10k rows.

replace "S:/Jan example
find replace (for example, replace "S:/Jan example

The code replaces the Jan with Feb using cells C1 and C2 as the holders for the Jan & Feb. (You would change them to Feb & Mar next month)

The result of my code applied to column A and referring to C1 & C2 produces this. The 10,000+ rows are replaced in less than one second on my computer.

replace "S:/Feb example
find replace (for example, replace "S:/Feb example

So I am wanting example text of the cells/rows where the replacements are to occur, and what rows and column. I want to accurately apply this code to your sheet. I use column G as a helper column, but the helper column can be way off screen as needed to not interfere with the current sheet data.

Option Explicit
Sub C1_C2_Rplc()
  Dim lRowCount&
    Dim OneRng As Range

  lRowCount = Cells(Rows.Count, "A").End(xlUp).Row ''Note is column A for row count
 ' MsgBox lRowCount
 
  With Range("G2").Resize(lRowCount - 1) 'Returning values in column G
    .Formula = "=REPLACE(A2,FIND($C$1,A2),6,$C$2)": .Value = .Value
  End With
  
  'copy column G to A2 and clear column G
  Set OneRng = Range("G2", Range("G2").End(xlDown))
  OneRng.Copy Range("A2")
  OneRng.ClearContents
End Sub

Howard

 

[shg]

How about just Data > Edit Links?