Results 1 to 10 of 10

Thread: Find and Replace value using Dictonary
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular marreco's Avatar
    Join Date
    Jan 2011
    Posts
    604
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find and Replace value using Dictonary

    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 2010 / Windows 7 (work)

  2. #2
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,360
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Find and Replace value using Dictonary

    Hi,

    Looks like you need to revisit your Test ...

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

    Hope this will help

  3. #3
    Board Regular marreco's Avatar
    Join Date
    Jan 2011
    Posts
    604
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and Replace value using Dictonary

    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)

  4. #4
    Board Regular marreco's Avatar
    Join Date
    Jan 2011
    Posts
    604
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and Replace value using Dictonary

    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)

  5. #5
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,360
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Find and Replace value using Dictonary

    It seems to me you are just missing an asterisk ...

    If Rng.Value Like "*Cancel*" Then

    Hope this will help

  6. #6
    Board Regular marreco's Avatar
    Join Date
    Jan 2011
    Posts
    604
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and Replace value using Dictonary

    When I run code this happening in column Rst, but in Correct column is waht I want
    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
    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 by marreco; Apr 4th, 2019 at 04:28 PM.
    Excel 2010 / Windows 7 (work)

  7. #7
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,360
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Find and Replace value using Dictonary

    Hello,

    You could test following :

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

    Hope this will help

  8. #8
    Board Regular marreco's Avatar
    Join Date
    Jan 2011
    Posts
    604
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and Replace value using Dictonary

    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:
    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)

  9. #9
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,360
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Find and Replace value using Dictonary

    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

  10. #10
    Board Regular marreco's Avatar
    Join Date
    Jan 2011
    Posts
    604
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and Replace value using Dictonary

    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)

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •