locked
Ajuda Macro Excel Localiza e Substitui RRS feed

  • Pergunta

  • Srs,

     

    Boa tarde,

     

    Estou enfrentando um problema com uma macro, sei que é algo meio "bobo", porém, já estou há dois dias procurando na net e não consegui.

     

    Criei uma planilha habilitada para macros no excel 2007 e nela inseri a seguinte macro para localizar e substituir valores:

     

    Sub LocalizarSubstituir()
    
    Sheets("NotaFiscal").Select
    Cells.Replace What:="0000018569", Replacement:="WEST", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    
    Sheets("NotaFiscal").Select
    Cells.Replace What:="0000018562", Replacement:="MORU", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    
    Sheets("NotaFiscal").Select
    Cells.Replace What:="0000018567", Replacement:="PAULISTA", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    
    Sheets("NotaFiscal").Select
    Cells.Replace What:="0000018568", Replacement:="CENESP", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    
    Sheets("NotaFiscal").Select
    Cells.Replace What:="0000019755", Replacement:="SP.MARKET", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    
    Sheets("NotaFiscal").Select
    Cells.Replace What:="0000018571", Replacement:="CNORTE", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    
    Sheets("NotaFiscal").Select
    Cells.Replace What:="0000018572", Replacement:="TATUAPE", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    
    Sheets("NotaFiscal").Select
    Cells.Replace What:="0000019762", Replacement:="ARICANDUVA", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    
    Sheets("NotaFiscal").Select
    Cells.Replace What:="0000018565", Replacement:="LIGHT", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    
    Sheets("NotaFiscal").Select
    Cells.Replace What:="0000018564", Replacement:="BOA VISTA", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    
    Sheets("NotaFiscal").Select
    Cells.Replace What:="0000018570", Replacement:="SANTA CRUZ", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    
    Sheets("NotaFiscal").Select
    Cells.Replace What:="0000018566", Replacement:="IBIRAPUERA", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    
    Sheets("NotaFiscal").Select
    Cells.Replace What:="0000019763", Replacement:="GUARULHOS", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    
    Sheets("NotaFiscal").Select
    Cells.Replace What:="0000018563", Replacement:="AV.PAULISTA", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    
    Sheets("NotaFiscal").Select
    Cells.Replace What:="0000019761", Replacement:="CENTRAL PLAZA", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    
    End Sub

    A questão é a seguinte, esta planilha que criei é para ser uma "planilha mestre", ou seja, diversas vezes terei outras planilhas nas quais precisarei utilizar esta macro, porém, se eu abro por exemplo a planilha "Relatório" com esta planilha "Macro" aberta também, e na planilha "Relatório" eu tento utilizar a macro de localizar e substituir, nada acontece, ou seja, ela não está conseguindo utilizar a macro que está em outra planilha.

     

    Já tentei utilizar ActiveWorkbook.Sheets("NotaFiscal").Select

    como também ActiveWorkbook.ActiveSheet.Select, porém, não consegui solucionar a questão.

     

    Minha dúvida é, como fazer com que uma macro em uma planilha "X" funcione em outras planilhas também??

     

    Obrigado pela atenção.

    sexta-feira, 25 de junho de 2010 19:15

Respostas

  • Olá Dhiego como vai,

    Segue a sua ajuda,

     

    Sub LocalizarSubstituir()

    ActiveSheet.Cells.Replace What:="0000018569", Replacement:="WEST", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018562", Replacement:="MORU", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018567", Replacement:="PAULISTA", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018568", Replacement:="CENESP", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000019755", Replacement:="SP.MARKET", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018571", Replacement:="CNORTE", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018572", Replacement:="TATUAPE", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000019762", Replacement:="ARICANDUVA", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018565", Replacement:="LIGHT", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018564", Replacement:="BOA VISTA", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018570", Replacement:="SANTA CRUZ", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018566", Replacement:="IBIRAPUERA", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000019763", Replacement:="GUARULHOS", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018563", Replacement:="AV.PAULISTA", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000019761", Replacement:="CENTRAL PLAZA", LookAt:=xlPart, SearchOrder:=xlByRows

    End Sub

     

    Ela irá funcionar para todas as suas Planilhas ativas como "Nota Fiscal" entre outras, é só deixar ativado a planilha e rode esse algoritmo no VBA Macros,

     

    Abraços,

    Baldini

     

     


    Baldini Fabio
    • Sugerido como Resposta Baldini sexta-feira, 25 de junho de 2010 21:28
    • Marcado como Resposta Dhiego Spinillo segunda-feira, 28 de junho de 2010 11:55
    sexta-feira, 25 de junho de 2010 21:28

Todas as Respostas

  • Olá Dhiego como vai,

    Segue a sua ajuda,

     

    Sub LocalizarSubstituir()

    ActiveSheet.Cells.Replace What:="0000018569", Replacement:="WEST", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018562", Replacement:="MORU", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018567", Replacement:="PAULISTA", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018568", Replacement:="CENESP", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000019755", Replacement:="SP.MARKET", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018571", Replacement:="CNORTE", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018572", Replacement:="TATUAPE", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000019762", Replacement:="ARICANDUVA", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018565", Replacement:="LIGHT", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018564", Replacement:="BOA VISTA", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018570", Replacement:="SANTA CRUZ", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018566", Replacement:="IBIRAPUERA", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000019763", Replacement:="GUARULHOS", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000018563", Replacement:="AV.PAULISTA", LookAt:=xlPart, SearchOrder:=xlByRows

    ActiveSheet.Cells.Replace What:="0000019761", Replacement:="CENTRAL PLAZA", LookAt:=xlPart, SearchOrder:=xlByRows

    End Sub

     

    Ela irá funcionar para todas as suas Planilhas ativas como "Nota Fiscal" entre outras, é só deixar ativado a planilha e rode esse algoritmo no VBA Macros,

     

    Abraços,

    Baldini

     

     


    Baldini Fabio
    • Sugerido como Resposta Baldini sexta-feira, 25 de junho de 2010 21:28
    • Marcado como Resposta Dhiego Spinillo segunda-feira, 28 de junho de 2010 11:55
    sexta-feira, 25 de junho de 2010 21:28
  • Prezado Fábio, agradeço por sua resposta!

    Funcionou perfeitamente, foi de grande ajuda.

    Muitíssimo obrigado!

     

    =)

    segunda-feira, 28 de junho de 2010 11:56
  • Imagina Dhiego estamos aqui para ajudar sempre!

     

    Abração!


    Baldini Fabio
    segunda-feira, 28 de junho de 2010 18:55