Find and Replace value using Dictonary

marreco

Well-known Member
Joined
Jan 1, 2011
Messages
609
Office Version
  1. 2010
Platform
  1. Windows
Hi.

I need find a text in column E and F then replace this value using Rng.Offset(, 1).Value
But in column E, I need find any text like "*Cancel*"

Code:
Sub FindAndReplace()
'I try adapt this code made by Fluff
'https://www.mrexcel.com/forum/excel-questions/1067092-excel-vba-dictionary-replacing-text-strings.html
   Dim Rng As Range
   Dim Dic As Object

   Set Dic = CreateObject("scripting.dictionary")
   Dic.Add "Cancel", "1" 'I need lookup [Cancel] value in column E and replace for 1
   Dic.Add "X", "1"      'I need lookup [X] value in column F and replace for 1

   For Each Rng In Range("E2", Range("E" & Rows.Count).End(xlUp))
      If Not (Dic(Rng.Value).Position Like "*Cancel*") Then
          Rng.Offset(, 1).Value = Dic(Rng.Value)
      End If
   Next Rng
End Sub

Thank you!!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi,

Looks like you need to revisit your Test ...

If my understanding is correct : If Rng.Value Like "*Cancel*" Then

Hope this will help
 
Upvote 0
Hi
When I run code only can see Cancel, but I need find something like this:
Cancel. NF-e entrada (referência A1)
Cancelamento saída - Nfe (A2)

<colgroup><col></colgroup><tbody>
</tbody>
 
Upvote 0
My last problem now is try find partial text.

["Cancel. NF-e entrada (referência A1)", "Cancelamento saída - Nfe (A2)"]

Code:
Sub FindAndReplace()
'I try adapt this code made by Fluff
'https://www.mrexcel.com/forum/excel-questions/1067092-excel-vba-dictionary-replacing-text-strings.html
   Dim Rng As Range
   Dim Dic As Object

   Set Dic = CreateObject("scripting.dictionary")
   Dic.Add "Cancel", "1" 'I need lookup any text wich start with [Cancel] value in column E and replace for 1
   Dic.Add "X", "1"      'I need lookup [X] value in column F and replace for 1

   For Each Rng In Range("E2", Range("E" & Rows.Count).End(xlUp))
      If Rng.Value Like "*Cancel" Then 'I don't know how to adapt this line to find ["Cancel. NF-e entrada (referência A1)", "Cancelamento saída - Nfe (A2)"]
          Rng.Offset(, 40).Value = Dic(Rng.Value) 'This line is OK
      End If
   Next Rng

   For Each Rng In Range("F2", Range("F" & Rows.Count).End(xlUp))
       If Rng.Value = "X" Then
           Rng.Offset(, 39).Value = Dic(Rng.Value)
       End If
   Next Rng
End Sub
 
Upvote 0
It seems to me you are just missing an asterisk ...

If Rng.Value Like "*Cancel*" Then

Hope this will help
 
Upvote 0
When I run code this happening in column Rst, but in Correct column is waht I want
NFDatadoc.Dt.lτto.CNCat.NotaFiscalEstornadoRstCorretc
158413x25.02.2019B1Cancel11
15840720.02.201925.02.2019B1xxxNF-e entrada (referΩncia E2)
15840720.02.201925.02.2019B1NF-e entrada (referΩncia E3)
15840920.02.201925.02.2019B1NF-e entrada (referΩncia E4)
98552718.02.201925.02.2019B1XX1
98552718.02.201925.02.2019B1Cancel. NF-e entrada (referência A1)1
98552718.02.201925.02.2019B1Cancelamento saída - Nfe (A2)1
98552718.02.201925.02.2019B1NF-eX1
98552718.02.201925.02.2019B1Cancel NF-e entrada (referΩncia E9)1

<tbody>
</tbody>
My code
Code:
Sub FindAndReplace()
'I try adapt this code made by Fluff
'https://www.mrexcel.com/forum/excel-questions/1067092-excel-vba-dictionary-replacing-text-strings.html
   Dim Rng As Range
   Dim Dic As Object

   Set Dic = CreateObject("scripting.dictionary")
   Dic.Add "Cancel", "1"
   Dic.Add "X", "1"

   For Each Rng In Range("E2", Range("E" & Rows.Count).End(xlUp))
      If Rng.Value Like "Cancel*" Then 'I don't know how to adapt this line to find ["Cancel. NF-e entrada (referência A1)", "Cancelamento saída - Nfe (A2)"]
          Rng.Offset(, 40).Value = Dic(Rng.Value) 'This line is OK
      End If
   Next Rng

   For Each Rng In Range("F2", Range("F" & Rows.Count).End(xlUp))
       If Rng.Value = "X" Then
           Rng.Offset(, 39).Value = Dic(Rng.Value)
       End If
   Next Rng
End Sub
 
Last edited:
Upvote 0
Hello,

You could test following :

If Instr(Rng.Value, "Cancel" ) Then

Hope this will help
 
Upvote 0
Hi James, thanks for help, but is not work.
I run this
Code:
Sub FindAndReplace()
'I try adapt this code made by Fluff
'https://www.mrexcel.com/forum/excel-questions/1067092-excel-vba-dictionary-replacing-text-strings.html
   Dim Rng As Range
   Dim Dic As Object

   Set Dic = CreateObject("scripting.dictionary")
   Dic.Add "Cancel", "1"
   Dic.Add "X", "1"

   For Each Rng In Range("E2", Range("E" & Rows.Count).End(xlUp))
      If InStr(Rng.Value, "Cancel") Then 'I don't know how to adapt this line to find ["Cancel. NF-e entrada (referência A1)", "Cancelamento saída - Nfe (A2)"]
          Rng.Offset(, 40).Value = Dic(Rng.Value) 'This line is OK
      End If
   Next Rng

   For Each Rng In Range("F2", Range("F" & Rows.Count).End(xlUp))
       If Rng.Value = "X" Then
           Rng.Offset(, 39).Value = Dic(Rng.Value)
       End If
   Next Rng
End Sub
Return this:
NFDatadoc.Dt.lτto.CNCat.NotaFiscalEstornadoRstCorretc
158413x25.02.2019B1Cancel 11
15840720.02.201925.02.2019B1xxxNF-e entrada (referΩncia E2)
15840720.02.201925.02.2019B1NF-e entrada (referΩncia E3)
15840920.02.201925.02.2019B1NF-e entrada (referΩncia E4)
98552718.02.201925.02.2019B1XX1
98552718.02.201925.02.2019B1Cancel. NF-e entrada (referência A1) 1
98552718.02.201925.02.2019B1Cancelamento saída - Nfe (A2) 1
98552718.02.201925.02.2019B1NF-eX1
98552718.02.201925.02.2019B1Cancel NF-e entrada (referΩncia E9) 1

<colgroup><col><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Hello,

Hope you have checked there is no error about the range which needs to be analyzed ...

Another test to eliminate Caps and Non-Caps :

If Instr(LCase(Rng.Value), "cancel" ) Then

Hope this will help
 
Upvote 0
HI James, if you don't mind I prepare a file.
Look in column E, I need find partial text which start with Cancel

file link:
https://www.4shared.com/s/fKdVPEK4zda
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top