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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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

S
  • Solved
Find and replace multiple values with other values in a range
Replies
8
Views
430
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
664
OilEconomist
O
D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
130
Denny57
D
T
  • Question Question
Open CSV with VBA, convert to ANSI and a cell "75 %" is changed to format type percent and the value is changed to "0.75"
Replies
1
Views
348
Tanquen
T
D
  • Solved
Numeric Value returned when Time format returned to User Form TextBox
Replies
3
Views
79
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,223,892
Messages
6,175,236
Members
452,621
Latest member
Laura_PinksBTHFT

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