Excel - Alterando um valor usando uma tabela de consulta

Questão

Eu estou procurando ajuda sobre como escrever uma macro que executará uma fórmula de VLOOKUP que irá comparar o valor em G2 na Planilha1, para o valor em A1 na Planilha2. Se os valores coincidirem, gostaria de substituir um valor em G2 na Folha1 pelo valor de B1 na Folha2. O motivo pelo qual preciso substituir o valor na mesma célula é porque nem toda célula terá uma correspondência na tabela de pesquisa. Se este for o caso, o valor original precisa permanecer.

Exemplo...

Antes

 G2 - Folha1 A1 B1 - Folha2 12345 12345 ABCDE 23456 34567 GHIJK 34567 

Depois de

 G2 - Folha1 A1 B1 - Folha2 ABCDE 12345 ABCDE 23456 34567 GHIJK GHIJK 

Isto é o que eu criei até agora, mas tudo que eu obtenho é o valor "0" em todas as células.

 Range ("G2"). Selecione ActiveCell.FormulaR1C1 = "= VLOOKUP (TRIM (RC [0]), WCSSCustItemNumLookupTable! R1C1: R2000C2, 2, 0)" Destino de Selection.AutoFill: = Range ("G2: G" + sPostDelLastRow ) Range ("F1"). Selecione 

Solução

Você pode tentar algo assim

 Dim temp Como Variant temp = "valor dummy dude" On Error Resume Próxima temp = WorksheetFunction.VLookup (Range ("G2"), Folhas ("Folha2"). Range ("A: B"), 2, False) On Error GoTo 0 Se temp "valor dummy dude" Então Range ("G2") = temp End If 

Dito isto, acho que o que você precisa fazer é ter outra coluna para a base temporária. Vamos dizer que sua coluna H na folha 1

Faça uma pesquisa lá

Como

Range("G2").Select 'doing a look up in temp range H With Range("H2:H" & sPostDelLastRow) 'actual look up. If look up fails, then use corresponding value from G column else use the value of vlookup .FormulaR1C1 = "=ISERROR(VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)), RC[-1], VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)" .Copy 'copying .PasteSpecial xlPasteValues 'pasting back as values to eliminate the formula .Copy 'copying again to move to actual G range End With 'copy to G range Range("G2:G" & sPostDelLastRow).PasteSpecial 'clear teamp range Range("H2:H" & sPostDelLastRow).Clear Note Thanks to rizvisa1 for this tip on the forum.

Range("G2").Select 'doing a look up in temp range H With Range("H2:H" & sPostDelLastRow) 'actual look up. If look up fails, then use corresponding value from G column else use the value of vlookup .FormulaR1C1 = "=ISERROR(VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)), RC[-1], VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)" .Copy 'copying .PasteSpecial xlPasteValues 'pasting back as values to eliminate the formula .Copy 'copying again to move to actual G range End With 'copy to G range Range("G2:G" & sPostDelLastRow).PasteSpecial 'clear teamp range Range("H2:H" & sPostDelLastRow).Clear Note Thanks to rizvisa1 for this tip on the forum.

Range("G2").Select 'doing a look up in temp range H With Range("H2:H" & sPostDelLastRow) 'actual look up. If look up fails, then use corresponding value from G column else use the value of vlookup .FormulaR1C1 = "=ISERROR(VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)), RC[-1], VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)" .Copy 'copying .PasteSpecial xlPasteValues 'pasting back as values to eliminate the formula .Copy 'copying again to move to actual G range End With 'copy to G range Range("G2:G" & sPostDelLastRow).PasteSpecial 'clear teamp range Range("H2:H" & sPostDelLastRow).Clear Note Thanks to rizvisa1 for this tip on the forum.

Range("G2").Select 'doing a look up in temp range H With Range("H2:H" & sPostDelLastRow) 'actual look up. If look up fails, then use corresponding value from G column else use the value of vlookup .FormulaR1C1 = "=ISERROR(VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)), RC[-1], VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)" .Copy 'copying .PasteSpecial xlPasteValues 'pasting back as values to eliminate the formula .Copy 'copying again to move to actual G range End With 'copy to G range Range("G2:G" & sPostDelLastRow).PasteSpecial 'clear teamp range Range("H2:H" & sPostDelLastRow).Clear Note Thanks to rizvisa1 for this tip on the forum.

Range("G2").Select 'doing a look up in temp range H With Range("H2:H" & sPostDelLastRow) 'actual look up. If look up fails, then use corresponding value from G column else use the value of vlookup .FormulaR1C1 = "=ISERROR(VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)), RC[-1], VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)" .Copy 'copying .PasteSpecial xlPasteValues 'pasting back as values to eliminate the formula .Copy 'copying again to move to actual G range End With 'copy to G range Range("G2:G" & sPostDelLastRow).PasteSpecial 'clear teamp range Range("H2:H" & sPostDelLastRow).Clear Note Thanks to rizvisa1 for this tip on the forum.

Range("G2").Select 'doing a look up in temp range H With Range("H2:H" & sPostDelLastRow) 'actual look up. If look up fails, then use corresponding value from G column else use the value of vlookup .FormulaR1C1 = "=ISERROR(VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)), RC[-1], VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)" .Copy 'copying .PasteSpecial xlPasteValues 'pasting back as values to eliminate the formula .Copy 'copying again to move to actual G range End With 'copy to G range Range("G2:G" & sPostDelLastRow).PasteSpecial 'clear teamp range Range("H2:H" & sPostDelLastRow).Clear Note Thanks to rizvisa1 for this tip on the forum.

Range("G2").Select 'doing a look up in temp range H With Range("H2:H" & sPostDelLastRow) 'actual look up. If look up fails, then use corresponding value from G column else use the value of vlookup .FormulaR1C1 = "=ISERROR(VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)), RC[-1], VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)" .Copy 'copying .PasteSpecial xlPasteValues 'pasting back as values to eliminate the formula .Copy 'copying again to move to actual G range End With 'copy to G range Range("G2:G" & sPostDelLastRow).PasteSpecial 'clear teamp range Range("H2:H" & sPostDelLastRow).Clear Note Thanks to rizvisa1 for this tip on the forum.

Range("G2").Select 'doing a look up in temp range H With Range("H2:H" & sPostDelLastRow) 'actual look up. If look up fails, then use corresponding value from G column else use the value of vlookup .FormulaR1C1 = "=ISERROR(VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)), RC[-1], VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)" .Copy 'copying .PasteSpecial xlPasteValues 'pasting back as values to eliminate the formula .Copy 'copying again to move to actual G range End With 'copy to G range Range("G2:G" & sPostDelLastRow).PasteSpecial 'clear teamp range Range("H2:H" & sPostDelLastRow).Clear Note Thanks to rizvisa1 for this tip on the forum.

Range("G2").Select 'doing a look up in temp range H With Range("H2:H" & sPostDelLastRow) 'actual look up. If look up fails, then use corresponding value from G column else use the value of vlookup .FormulaR1C1 = "=ISERROR(VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)), RC[-1], VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)" .Copy 'copying .PasteSpecial xlPasteValues 'pasting back as values to eliminate the formula .Copy 'copying again to move to actual G range End With 'copy to G range Range("G2:G" & sPostDelLastRow).PasteSpecial 'clear teamp range Range("H2:H" & sPostDelLastRow).Clear Note Thanks to rizvisa1 for this tip on the forum.

Range("G2").Select 'doing a look up in temp range H With Range("H2:H" & sPostDelLastRow) 'actual look up. If look up fails, then use corresponding value from G column else use the value of vlookup .FormulaR1C1 = "=ISERROR(VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)), RC[-1], VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)" .Copy 'copying .PasteSpecial xlPasteValues 'pasting back as values to eliminate the formula .Copy 'copying again to move to actual G range End With 'copy to G range Range("G2:G" & sPostDelLastRow).PasteSpecial 'clear teamp range Range("H2:H" & sPostDelLastRow).Clear Note Thanks to rizvisa1 for this tip on the forum.

Range("G2").Select 'doing a look up in temp range H With Range("H2:H" & sPostDelLastRow) 'actual look up. If look up fails, then use corresponding value from G column else use the value of vlookup .FormulaR1C1 = "=ISERROR(VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)), RC[-1], VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)" .Copy 'copying .PasteSpecial xlPasteValues 'pasting back as values to eliminate the formula .Copy 'copying again to move to actual G range End With 'copy to G range Range("G2:G" & sPostDelLastRow).PasteSpecial 'clear teamp range Range("H2:H" & sPostDelLastRow).Clear Note Thanks to rizvisa1 for this tip on the forum.

Range("G2").Select 'doing a look up in temp range H With Range("H2:H" & sPostDelLastRow) 'actual look up. If look up fails, then use corresponding value from G column else use the value of vlookup .FormulaR1C1 = "=ISERROR(VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)), RC[-1], VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)" .Copy 'copying .PasteSpecial xlPasteValues 'pasting back as values to eliminate the formula .Copy 'copying again to move to actual G range End With 'copy to G range Range("G2:G" & sPostDelLastRow).PasteSpecial 'clear teamp range Range("H2:H" & sPostDelLastRow).Clear Note Thanks to rizvisa1 for this tip on the forum.

Range("G2").Select 'doing a look up in temp range H With Range("H2:H" & sPostDelLastRow) 'actual look up. If look up fails, then use corresponding value from G column else use the value of vlookup .FormulaR1C1 = "=ISERROR(VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)), RC[-1], VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)" .Copy 'copying .PasteSpecial xlPasteValues 'pasting back as values to eliminate the formula .Copy 'copying again to move to actual G range End With 'copy to G range Range("G2:G" & sPostDelLastRow).PasteSpecial 'clear teamp range Range("H2:H" & sPostDelLastRow).Clear Note Thanks to rizvisa1 for this tip on the forum.

Range("G2").Select 'doing a look up in temp range H With Range("H2:H" & sPostDelLastRow) 'actual look up. If look up fails, then use corresponding value from G column else use the value of vlookup .FormulaR1C1 = "=ISERROR(VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)), RC[-1], VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)" .Copy 'copying .PasteSpecial xlPasteValues 'pasting back as values to eliminate the formula .Copy 'copying again to move to actual G range End With 'copy to G range Range("G2:G" & sPostDelLastRow).PasteSpecial 'clear teamp range Range("H2:H" & sPostDelLastRow).Clear Note Thanks to rizvisa1 for this tip on the forum.

Range("G2").Select 'doing a look up in temp range H With Range("H2:H" & sPostDelLastRow) 'actual look up. If look up fails, then use corresponding value from G column else use the value of vlookup .FormulaR1C1 = "=ISERROR(VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)), RC[-1], VLOOKUP(TRIM(RC[-1]), WCSSCustItemNumLookupTable!R1C1:R2000C2, 2, 0)" .Copy 'copying .PasteSpecial xlPasteValues 'pasting back as values to eliminate the formula .Copy 'copying again to move to actual G range End With 'copy to G range Range("G2:G" & sPostDelLastRow).PasteSpecial 'clear teamp range Range("H2:H" & sPostDelLastRow).Clear Note Thanks to rizvisa1 for this tip on the forum.

Artigo Anterior Próximo Artigo

Principais Dicas