Find and Replace value using Dictonary

marreco

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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
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:
[TABLE="width: 259"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Cancel. NF-e entrada (referência A1)
[/TD]
[/TR]
[TR]
[TD]Cancelamento saída - Nfe (A2)
[/TD]
[/TR]
</tbody>[/TABLE]
 
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
[TABLE="width: 591"]
<tbody>[TR]
[TD]NF[/TD]
[TD]Datadoc.[/TD]
[TD]Dt.lτto.[/TD]
[TD]CN[/TD]
[TD]Cat.NotaFiscal[/TD]
[TD]Estornado[/TD]
[TD]Rst[/TD]
[TD]Corretc[/TD]
[/TR]
[TR]
[TD="align: right"]158413[/TD]
[TD]x[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD]Cancel[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]158407[/TD]
[TD]20.02.2019[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD="colspan: 2"]xxxNF-e entrada (referΩncia E2)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]158407[/TD]
[TD]20.02.2019[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD]NF-e entrada (referΩncia E3)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]158409[/TD]
[TD]20.02.2019[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD]NF-e entrada (referΩncia E4)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]985527[/TD]
[TD]18.02.2019[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]985527[/TD]
[TD]18.02.2019[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD="colspan: 2"]Cancel. NF-e entrada (referência A1)[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]985527[/TD]
[TD]18.02.2019[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD]Cancelamento saída - Nfe (A2)[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]985527[/TD]
[TD]18.02.2019[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD]NF-e[/TD]
[TD]X[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]985527[/TD]
[TD]18.02.2019[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD="colspan: 2"]Cancel NF-e entrada (referΩncia E9)[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
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:
[TABLE="width: 591"]
<colgroup><col><col span="2"><col><col><col><col><col></colgroup><tbody>[TR]
[TD]NF[/TD]
[TD]Datadoc.[/TD]
[TD]Dt.lτto.[/TD]
[TD]CN[/TD]
[TD]Cat.NotaFiscal[/TD]
[TD]Estornado[/TD]
[TD]Rst[/TD]
[TD]Corretc[/TD]
[/TR]
[TR]
[TD="align: right"]158413[/TD]
[TD]x[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD]Cancel[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]158407[/TD]
[TD]20.02.2019[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD]xxxNF-e entrada (referΩncia E2) [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]158407[/TD]
[TD]20.02.2019[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD]NF-e entrada (referΩncia E3) [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]158409[/TD]
[TD]20.02.2019[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD]NF-e entrada (referΩncia E4) [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]985527[/TD]
[TD]18.02.2019[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]985527[/TD]
[TD]18.02.2019[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD]Cancel. NF-e entrada (referência A1)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]985527[/TD]
[TD]18.02.2019[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD]Cancelamento saída - Nfe (A2)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]985527[/TD]
[TD]18.02.2019[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD]NF-e[/TD]
[TD]X[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]985527[/TD]
[TD]18.02.2019[/TD]
[TD]25.02.2019[/TD]
[TD]B1[/TD]
[TD]Cancel NF-e entrada (referΩncia E9)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
 
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
You must log in or register to reply here.

Similar threads

K
  • Question Question
Range of columns change colors based on cell column value
Replies
3
Views
59
Joe4
Joe4
S
  • Solved
Find and replace multiple values with other values in a range
Replies
8
Views
456
Rick Rothstein
Rick Rothstein
O
  • Solved
VBA Code to Find Duplicate Values in a Range and Add a Number at the End
2
Replies
12
Views
803
OilEconomist
O
D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
189
Denny57
D
T
  • Solved
Prevent changes to a single column
Replies
3
Views
123
tiredofit
T

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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