Hi,
Looks like you need to revisit your Test ...
If my understanding is correct : If Rng.Value Like "*Cancel*" Then
Hope this will help
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*"
Thank you!!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
Excel 2010 / Windows 7 (work)
Hi,
Looks like you need to revisit your Test ...
If my understanding is correct : If Rng.Value Like "*Cancel*" Then
Hope this will help
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)
Excel 2010 / Windows 7 (work)
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
Excel 2010 / Windows 7 (work)
It seems to me you are just missing an asterisk ...
If Rng.Value Like "*Cancel*" Then
Hope this will help
When I run code this happening in column Rst, but in Correct column is waht I want
My code
NF Datadoc. Dt.lτto. CN Cat.NotaFiscal Estornado Rst Corretc 158413 x 25.02.2019 B1 Cancel 1 1 158407 20.02.2019 25.02.2019 B1 xxxNF-e entrada (referΩncia E2) 158407 20.02.2019 25.02.2019 B1 NF-e entrada (referΩncia E3) 158409 20.02.2019 25.02.2019 B1 NF-e entrada (referΩncia E4) 985527 18.02.2019 25.02.2019 B1 X X 1 985527 18.02.2019 25.02.2019 B1 Cancel. NF-e entrada (referência A1) 1 985527 18.02.2019 25.02.2019 B1 Cancelamento saída - Nfe (A2) 1 985527 18.02.2019 25.02.2019 B1 NF-e X 1 985527 18.02.2019 25.02.2019 B1 Cancel NF-e entrada (referΩncia E9) 1
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 by marreco; Apr 4th, 2019 at 04:28 PM.
Excel 2010 / Windows 7 (work)
Hello,
You could test following :
If Instr(Rng.Value, "Cancel" ) Then
Hope this will help
Hi James, thanks for help, but is not work.
I run this
Return 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
NF Datadoc. Dt.lτto. CN Cat.NotaFiscal Estornado Rst Corretc 158413 x 25.02.2019 B1 Cancel 1 1 158407 20.02.2019 25.02.2019 B1 xxxNF-e entrada (referΩncia E2) 158407 20.02.2019 25.02.2019 B1 NF-e entrada (referΩncia E3) 158409 20.02.2019 25.02.2019 B1 NF-e entrada (referΩncia E4) 985527 18.02.2019 25.02.2019 B1 X X 1 985527 18.02.2019 25.02.2019 B1 Cancel. NF-e entrada (referência A1) 1 985527 18.02.2019 25.02.2019 B1 Cancelamento saída - Nfe (A2) 1 985527 18.02.2019 25.02.2019 B1 NF-e X 1 985527 18.02.2019 25.02.2019 B1 Cancel NF-e entrada (referΩncia E9) 1
Excel 2010 / Windows 7 (work)
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
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
Excel 2010 / Windows 7 (work)
Like this thread? Share it with others