Duvida para diminuir codigo e deixar automatico VLOOKUP

Duvida para diminuir codigo e deixar automatico VLOOKUP#47459

Por Nogsxd - 28 Ago 2019 às 16:50

- 28 Ago 2019 às 16:50 #47459

Eaee pessoal.

Por favor como posso deixar automatico esse codigo para nao precisar ficar escrevendo a celula do VLOOKUP que sera a base de pesquisa?

Gostaria que fizesse automaticamente a contagem das linhas e que o codigo seguisse sem eu ter que ficar alterando como no exemplo abaixo.

Muito obrigado galera!

Sub procuraracomp()
Planilha9.Range("C4:W39").ClearContents
'ultimaLinha = Planilha9.Cells(Rows.Count, "a").End(xlUp).Row
'lin = 4
'For i = 4 To ultimaLinha

Dim dat As Date
Dim entrega As Date

dat = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 2, 0)
produto = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 7, 0)
cliente = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 8, 0)
projeto = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 9, 0)
entrega = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 5, 0)
PBS = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 10, 0)
VIG = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 11, 0)
VIG2 = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 12, 0)
PDD = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 13, 0)
PDE = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 14, 0)
PTD = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 15, 0)
PTE = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 16, 0)
PFD = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 17, 0)
PFE = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 18, 0)
FTD = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 19, 0)
FTE = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 20, 0)
FMD = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 21, 0)
FME = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 22, 0)
FDD = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 23, 0)
FDE = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 24, 0)
FXD = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 25, 0)
FXE = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 26, 0)
TTP = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 27, 0)
TDM = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 28, 0)
TDF = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 29, 0)
TTF = Application.WorksheetFunction.VLookup(Range("B4"), Planilha1.Range("D3:AG250"), 30, 0)

Range("C4") = dat
Range("D4") = produto
Range("E4") = cliente
Range("F4") = projeto
Range("I4") = entrega
Range("J4") = PBS
Range("K4") = VIG
Range("L4") = VIG2
Range("M4") = PDD
Range("N4") = PDE
Range("O4") = PTD
Range("P4") = PTE
Range("Q4") = PFD
Range("R4") = PFE
Range("S4") = FTD
Range("T4") = FTE
Range("U4") = FMD
Range("V4") = FME
Range("W4") = FDD
Range("X4") = FDE
Range("Y4") = FXD
Range("Z4") = FXE
Range("AA4") = TTP
Range("AB4") = TDM
Range("AC4") = TDF
Range("AD4") = TTF

dat = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 2, 0)
produto = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 7, 0)
cliente = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 8, 0)
projeto = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 9, 0)
entrega = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 5, 0)
PBS = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 10, 0)
VIG = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 11, 0)
VIG2 = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 12, 0)
PDD = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 13, 0)
PDE = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 14, 0)
PTD = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 15, 0)
PTE = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 16, 0)
PFD = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 17, 0)
PFE = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 18, 0)
FTD = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 19, 0)
FTE = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 20, 0)
FMD = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 21, 0)
FME = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 22, 0)
FDD = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 23, 0)
FDE = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 24, 0)
FXD = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 25, 0)
FXE = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 26, 0)
TTP = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 27, 0)
TDM = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 28, 0)
TDF = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 29, 0)
TTF = Application.WorksheetFunction.VLookup(Range("B5"), Planilha1.Range("D3:AG250"), 30, 0)

Range("C5") = dat
Range("D5") = produto
Range("E5") = cliente
Range("F5") = projeto
Range("I5") = entrega
Range("J5") = PBS
Range("K5") = VIG
Range("L5") = VIG2
Range("M5") = PDD
Range("N5") = PDE
Range("O5") = PTD
Range("P5") = PTE
Range("Q5") = PFD
Range("R5") = PFE
Range("S5") = FTD
Range("T5") = FTE
Range("U5") = FMD
Range("V5") = FME
Range("W5") = FDD
Range("X5") = FDE
Range("Y5") = FXD
Range("Z5") = FXE
Range("AA5") = TTP
Range("AB5") = TDM
Range("AC5") = TDF
Range("AD5") = TTF

dat = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 2, 0)
produto = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 7, 0)
cliente = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 8, 0)
projeto = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 9, 0)
entrega = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 5, 0)
PBS = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 10, 0)
VIG = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 11, 0)
VIG2 = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 12, 0)
PDD = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 13, 0)
PDE = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 14, 0)
PTD = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 15, 0)
PTE = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 16, 0)
PFD = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 17, 0)
PFE = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 18, 0)
FTD = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 19, 0)
FTE = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 20, 0)
FMD = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 21, 0)
FME = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 22, 0)
FDD = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 23, 0)
FDE = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 24, 0)
FXD = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 25, 0)
FXE = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 26, 0)
TTP = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 27, 0)
TDM = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 28, 0)
TDF = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 29, 0)
TTF = Application.WorksheetFunction.VLookup(Range("B6"), Planilha1.Range("D3:AG250"), 30, 0)

Range("C6") = dat
Range("D6") = produto
Range("E6") = cliente
Range("F6") = projeto
Range("I6") = entrega
Range("J6") = PBS
Range("K6") = VIG
Range("L6") = VIG2
Range("M6") = PDD
Range("N6") = PDE
Range("O6") = PTD
Range("P6") = PTE
Range("Q6") = PFD
Range("R6") = PFE
Range("S6") = FTD
Range("T6") = FTE
Range("U6") = FMD
Range("V6") = FME
Range("W6") = FDD
Range("X6") = FDE
Range("Y6") = FXD
Range("Z6") = FXE
Range("AA6") = TTP
Range("AB6") = TDM
Range("AC6") = TDF
Range("AD6") = TTF

dat = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 2, 0)
produto = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 7, 0)
cliente = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 8, 0)
projeto = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 9, 0)
entrega = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 5, 0)
PBS = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 10, 0)
VIG = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 11, 0)
VIG2 = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 12, 0)
PDD = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 13, 0)
PDE = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 14, 0)
PTD = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 15, 0)
PTE = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 16, 0)
PFD = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 17, 0)
PFE = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 18, 0)
FTD = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 19, 0)
FTE = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 20, 0)
FMD = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 21, 0)
FME = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 22, 0)
FDD = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 23, 0)
FDE = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 24, 0)
FXD = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 25, 0)
FXE = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 26, 0)
TTP = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 27, 0)
TDM = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 28, 0)
TDF = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 29, 0)
TTF = Application.WorksheetFunction.VLookup(Range("B7"), Planilha1.Range("D3:AG250"), 30, 0)

Range("C7") = dat
Range("D7") = produto
Range("E7") = cliente
Range("F7") = projeto
Range("I7") = entrega
Range("J7") = PBS
Range("K7") = VIG
Range("L7") = VIG2
Range("M7") = PDD
Range("N7") = PDE
Range("O7") = PTD
Range("P7") = PTE
Range("Q7") = PFD
Range("R7") = PFE
Range("S7") = FTD
Range("T7") = FTE
Range("U7") = FMD
Range("V7") = FME
Range("W7") = FDD
Range("X7") = FDE
Range("Y7") = FXD
Range("Z7") = FXE
Range("AA7") = TTP
Range("AB7") = TDM
Range("AC7") = TDF
Range("AD7") = TTF

dat = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 2, 0)
produto = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 7, 0)
cliente = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 8, 0)
projeto = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 9, 0)
entrega = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 5, 0)
PBS = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 10, 0)
VIG = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 11, 0)
VIG2 = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 12, 0)
PDD = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 13, 0)
PDE = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 14, 0)
PTD = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 15, 0)
PTE = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 16, 0)
PFD = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 17, 0)
PFE = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 18, 0)
FTD = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 19, 0)
FTE = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 20, 0)
FMD = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 21, 0)
FME = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 22, 0)
FDD = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 23, 0)
FDE = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 24, 0)
FXD = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 25, 0)
FXE = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 26, 0)
TTP = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 27, 0)
TDM = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 28, 0)
TDF = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 29, 0)
TTF = Application.WorksheetFunction.VLookup(Range("B8"), Planilha1.Range("D3:AG250"), 30, 0)

Range("C8") = dat
Range("D8") = produto
Range("E8") = cliente
Range("F8") = projeto
Range("I8") = entrega
Range("J8") = PBS
Range("K8") = VIG
Range("L8") = VIG2
Range("M8") = PDD
Range("N8") = PDE
Range("O8") = PTD
Range("P8") = PTE
Range("Q8") = PFD
Range("R8") = PFE
Range("S8") = FTD
Range("T8") = FTE
Range("U8") = FMD
Range("V8") = FME
Range("W8") = FDD
Range("X8") = FDE
Range("Y8") = FXD
Range("Z8") = FXE
Range("AA8") = TTP
Range("AB8") = TDM
Range("AC8") = TDF
Range("AD8") = TTF

dat = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 2, 0)
produto = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 7, 0)
cliente = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 8, 0)
projeto = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 9, 0)
entrega = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 5, 0)
PBS = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 10, 0)
VIG = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 11, 0)
VIG2 = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 12, 0)
PDD = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 13, 0)
PDE = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 14, 0)
PTD = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 15, 0)
PTE = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 16, 0)
PFD = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 17, 0)
PFE = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 18, 0)
FTD = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 19, 0)
FTE = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 20, 0)
FMD = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 21, 0)
FME = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 22, 0)
FDD = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 23, 0)
FDE = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 24, 0)
FXD = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 25, 0)
FXE = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 26, 0)
TTP = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 27, 0)
TDM = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 28, 0)
TDF = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 29, 0)
TTF = Application.WorksheetFunction.VLookup(Range("B9"), Planilha1.Range("D3:AG250"), 30, 0)

Range("C9") = dat
Range("D9") = produto
Range("E9") = cliente
Range("F9") = projeto
Range("I9") = entrega
Range("J9") = PBS
Range("K9") = VIG
Range("L9") = VIG2
Range("M9") = PDD
Range("N9") = PDE
Range("O9") = PTD
Range("P9") = PTE
Range("Q9") = PFD
Range("R9") = PFE
Range("S9") = FTD
Range("T9") = FTE
Range("U9") = FMD
Range("V9") = FME
Range("W9") = FDD
Range("X9") = FDE
Range("Y9") = FXD
Range("Z9") = FXE
Range("AA9") = TTP
Range("AB9") = TDM
Range("AC9") = TDF
Range("AD9") = TTF

dat = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 2, 0)
produto = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 7, 0)
cliente = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 8, 0)
projeto = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 9, 0)
entrega = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 5, 0)
PBS = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 10, 0)
VIG = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 11, 0)
VIG2 = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 12, 0)
PDD = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 13, 0)
PDE = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 14, 0)
PTD = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 15, 0)
PTE = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 16, 0)
PFD = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 17, 0)
PFE = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 18, 0)
FTD = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 19, 0)
FTE = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 20, 0)
FMD = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 21, 0)
FME = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 22, 0)
FDD = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 23, 0)
FDE = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 24, 0)
FXD = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 25, 0)
FXE = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 26, 0)
TTP = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 27, 0)
TDM = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 28, 0)
TDF = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 29, 0)
TTF = Application.WorksheetFunction.VLookup(Range("B10"), Planilha1.Range("D3:AG250"), 30, 0)

Range("C10") = dat
Range("D10") = produto
Range("E10") = cliente
Range("F10") = projeto
Range("I10") = entrega
Range("J10") = PBS
Range("K10") = VIG
Range("L10") = VIG2
Range("M10") = PDD
Range("N10") = PDE
Range("O10") = PTD
Range("P10") = PTE
Range("Q10") = PFD
Range("R10") = PFE
Range("S10") = FTD
Range("T10") = FTE
Range("U10") = FMD
Range("V10") = FME
Range("W10") = FDD
Range("X10") = FDE
Range("Y10") = FXD
Range("Z10") = FXE
Range("AA10") = TTP
Range("AB10") = TDM
Range("AC10") = TDF
Range("AD10") = TTF

dat = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 2, 0)
produto = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 7, 0)
cliente = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 8, 0)
projeto = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 9, 0)
entrega = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 5, 0)
PBS = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 10, 0)
VIG = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 11, 0)
VIG2 = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 12, 0)
PDD = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 13, 0)
PDE = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 14, 0)
PTD = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 15, 0)
PTE = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 16, 0)
PFD = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 17, 0)
PFE = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 18, 0)
FTD = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 19, 0)
FTE = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 20, 0)
FMD = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 21, 0)
FME = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 22, 0)
FDD = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 23, 0)
FDE = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 24, 0)
FXD = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 25, 0)
FXE = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 26, 0)
TTP = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 27, 0)
TDM = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 28, 0)
TDF = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 29, 0)
TTF = Application.WorksheetFunction.VLookup(Range("B11"), Planilha1.Range("D3:AG250"), 30, 0)

Range("C11") = dat
Range("D11") = produto
Range("E11") = cliente
Range("F11") = projeto
Range("I11") = entrega
Range("J11") = PBS
Range("K11") = VIG
Range("L11") = VIG2
Range("M11") = PDD
Range("N11") = PDE
Range("O11") = PTD
Range("P11") = PTE
Range("Q11") = PFD
Range("R11") = PFE
Range("S11") = FTD
Range("T11") = FTE
Range("U11") = FMD
Range("V11") = FME
Range("W11") = FDD
Range("X11") = FDE
Range("Y11") = FXD
Range("Z11") = FXE
Range("AA11") = TTP
Range("AB11") = TDM
Range("AC11") = TDF
Range("AD11") = TTF

dat = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 2, 0)
produto = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 7, 0)
cliente = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 8, 0)
projeto = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 9, 0)
entrega = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 5, 0)
PBS = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 10, 0)
VIG = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 11, 0)
VIG2 = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 12, 0)
PDD = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 13, 0)
PDE = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 14, 0)
PTD = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 15, 0)
PTE = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 16, 0)
PFD = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 17, 0)
PFE = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 18, 0)
FTD = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 19, 0)
FTE = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 20, 0)
FMD = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 21, 0)
FME = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 22, 0)
FDD = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 23, 0)
FDE = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 24, 0)
FXD = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 25, 0)
FXE = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 26, 0)
TTP = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 27, 0)
TDM = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 28, 0)
TDF = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 29, 0)
TTF = Application.WorksheetFunction.VLookup(Range("B12"), Planilha1.Range("D3:AG250"), 30, 0)

Range("C12") = dat
Range("D12") = produto
Range("E12") = cliente
Range("F12") = projeto
Range("I12") = entrega
Range("J12") = PBS
Range("K12") = VIG
Range("L12") = VIG2
Range("M12") = PDD
Range("N12") = PDE
Range("O12") = PTD
Range("P12") = PTE
Range("Q12") = PFD
Range("R12") = PFE
Range("S12") = FTD
Range("T12") = FTE
Range("U12") = FMD
Range("V12") = FME
Range("W12") = FDD
Range("X12") = FDE
Range("Y12") = FXD
Range("Z12") = FXE
Range("AA12") = TTP
Range("AB12") = TDM
Range("AC12") = TDF
Range("AD12") = TTF

dat = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 2, 0)
produto = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 7, 0)
cliente = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 8, 0)
projeto = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 9, 0)
entrega = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 5, 0)
PBS = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 10, 0)
VIG = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 11, 0)
VIG2 = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 12, 0)
PDD = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 13, 0)
PDE = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 14, 0)
PTD = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 15, 0)
PTE = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 16, 0)
PFD = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 17, 0)
PFE = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 18, 0)
FTD = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 19, 0)
FTE = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 20, 0)
FMD = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 21, 0)
FME = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 22, 0)
FDD = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 23, 0)
FDE = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 24, 0)
FXD = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 25, 0)
FXE = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 26, 0)
TTP = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 27, 0)
TDM = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 28, 0)
TDF = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 29, 0)
TTF = Application.WorksheetFunction.VLookup(Range("B13"), Planilha1.Range("D3:AG250"), 30, 0)

Range("C13") = dat
Range("D13") = produto
Range("E13") = cliente
Range("F13") = projeto
Range("I13") = entrega
Range("J13") = PBS
Range("K13") = VIG
Range("L13") = VIG2
Range("M13") = PDD
Range("N13") = PDE
Range("O13") = PTD
Range("P13") = PTE
Range("Q13") = PFD
Range("R13") = PFE
Range("S13") = FTD
Range("T13") = FTE
Range("U13") = FMD
Range("V13") = FME
Range("W13") = FDD
Range("X13") = FDE
Range("Y13") = FXD
Range("Z13") = FXE
Range("AA13") = TTP
Range("AB13") = TDM
Range("AC13") = TDF
Range("AD13") = TTF

dat = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 2, 0)
produto = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 7, 0)
cliente = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 8, 0)
projeto = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 9, 0)
entrega = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 5, 0)
PBS = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 10, 0)
VIG = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 11, 0)
VIG2 = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 12, 0)
PDD = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 13, 0)
PDE = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 14, 0)
PTD = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 15, 0)
PTE = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 16, 0)
PFD = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 17, 0)
PFE = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 18, 0)
FTD = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 19, 0)
FTE = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 20, 0)
FMD = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 21, 0)
FME = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 22, 0)
FDD = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 23, 0)
FDE = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 24, 0)
FXD = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 25, 0)
FXE = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 26, 0)
TTP = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 27, 0)
TDM = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 28, 0)
TDF = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 29, 0)
TTF = Application.WorksheetFunction.VLookup(Range("B14"), Planilha1.Range("D3:AG250"), 30, 0)

Range("C14") = dat
Range("D14") = produto
Range("E14") = cliente
Range("F14") = projeto
Range("I14") = entrega
Range("J14") = PBS
Range("K14") = VIG
Range("L14") = VIG2
Range("M14") = PDD
Range("N14") = PDE
Range("O14") = PTD
Range("P14") = PTE
Range("Q14") = PFD
Range("R14") = PFE
Range("S14") = FTD
Range("T14") = FTE
Range("U14") = FMD
Range("V14") = FME
Range("W14") = FDD
Range("X14") = FDE
Range("Y14") = FXD
Range("Z14") = FXE
Range("AA14") = TTP
Range("AB14") = TDM
Range("AC14") = TDF
Range("AD14") = TTF

dat = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 2, 0)
produto = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 7, 0)
cliente = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 8, 0)
projeto = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 9, 0)
entrega = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 5, 0)
PBS = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 10, 0)
VIG = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 11, 0)
VIG2 = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 12, 0)
PDD = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 13, 0)
PDE = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 14, 0)
PTD = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 15, 0)
PTE = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 16, 0)
PFD = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 17, 0)
PFE = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 18, 0)
FTD = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 19, 0)
FTE = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 20, 0)
FMD = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 21, 0)
FME = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 22, 0)
FDD = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 23, 0)
FDE = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 24, 0)
FXD = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 25, 0)
FXE = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 26, 0)
TTP = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 27, 0)
TDM = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 28, 0)
TDF = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 29, 0)
TTF = Application.WorksheetFunction.VLookup(Range("B15"), Planilha1.Range("D3:AG250"), 30, 0)

Range("C15") = dat
Range("D15") = produto
Range("E15") = cliente
Range("F15") = projeto
Range("I15") = entrega
Range("J15") = PBS
Range("K15") = VIG
Range("L15") = VIG2
Range("M15") = PDD
Range("N15") = PDE
Range("O15") = PTD
Range("P15") = PTE
Range("Q15") = PFD
Range("R15") = PFE
Range("S15") = FTD
Range("T15") = FTE
Range("U15") = FMD
Range("V15") = FME
Range("W15") = FDD
Range("X15") = FDE
Range("Y15") = FXD
Range("Z15") = FXE
Range("AA15") = TTP
Range("AB15") = TDM
Range("AC15") = TDF
Range("AD15") = TTF

dat = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 2, 0)
produto = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 7, 0)
cliente = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 8, 0)
projeto = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 9, 0)
entrega = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 5, 0)
PBS = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 10, 0)
VIG = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 11, 0)
VIG2 = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 12, 0)
PDD = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 13, 0)
PDE = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 14, 0)
PTD = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 15, 0)
PTE = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 16, 0)
PFD = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 17, 0)
PFE = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 18, 0)
FTD = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 19, 0)
FTE = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 20, 0)
FMD = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 21, 0)
FME = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 22, 0)
FDD = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 23, 0)
FDE = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 24, 0)
FXD = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 25, 0)
FXE = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 26, 0)
TTP = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 27, 0)
TDM = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 28, 0)
TDF = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 29, 0)
TTF = Application.WorksheetFunction.VLookup(Range("B16"), Planilha1.Range("D3:AG250"), 30, 0)

Range("C16") = dat
Range("D16") = produto
Range("E16") = cliente
Range("F16") = projeto
Range("I16") = entrega
Range("J16") = PBS
Range("K16") = VIG
Range("L16") = VIG2
Range("M16") = PDD
Range("N16") = PDE
Range("O16") = PTD
Range("P16") = PTE
Range("Q16") = PFD
Range("R16") = PFE
Range("S16") = FTD
Range("T16") = FTE
Range("U16") = FMD
Range("V16") = FME
Range("W16") = FDD
Range("X16") = FDE
Range("Y16") = FXD
Range("Z16") = FXE
Range("AA16") = TTP
Range("AB16") = TDM
Range("AC16") = TDF
Range("AD16") = TTF

dat = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 2, 0)
produto = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 7, 0)
cliente = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 8, 0)
projeto = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 9, 0)
entrega = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 5, 0)
PBS = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 10, 0)
VIG = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 11, 0)
VIG2 = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 12, 0)
PDD = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 13, 0)
PDE = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 14, 0)
PTD = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 15, 0)
PTE = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 16, 0)
PFD = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 17, 0)
PFE = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 18, 0)
FTD = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 19, 0)
FTE = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 20, 0)
FMD = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 21, 0)
FME = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 22, 0)
FDD = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 23, 0)
FDE = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 24, 0)
FXD = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 25, 0)
FXE = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 26, 0)
TTP = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 27, 0)
TDM = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 28, 0)
TDF = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 29, 0)
TTF = Application.WorksheetFunction.VLookup(Range("B17"), Planilha1.Range("D3:AG250"), 30, 0)

Range("C17") = dat
Range("D17") = produto
Range("E17") = cliente
Range("F17") = projeto
Range("I17") = entrega
Range("J17") = PBS
Range("K17") = VIG
Range("L17") = VIG2
Range("M17") = PDD
Range("N17") = PDE
Range("O17") = PTD
Range("P17") = PTE
Range("Q17") = PFD
Range("R17") = PFE
Range("S17") = FTD
Range("T17") = FTE
Range("U17") = FMD
Range("V17") = FME
Range("W17") = FDD
Range("X17") = FDE
Range("Y17") = FXD
Range("Z17") = FXE
Range("AA17") = TTP
Range("AB17") = TDM
Range("AC17") = TDF
Range("AD17") = TTF

'Next

End Sub

Re: Duvida para diminuir codigo e deixar automatico VLOOKUP#47460

Por eduardogrigull - 28 Ago 2019 às 18:28

- 28 Ago 2019 às 18:28 #47460

Olá, bolei um script, só não tenho como testá-lo pq não tenho a planilha. Mas deve funcionar.
Não esqueça de mudar o nome da planilha.
Pra deixar mais rapido podes tentar usar o Application.ScreenUpdating = False

Código: Selecionar todos

Sub ProcurarComp()

Dim numero As Integer, linha As Integer, coluna As Integer, UltimaLinha As Integer

'DEFINA O NOME DA SUA PLANILHA
Worksheets("SuaPlanilha").Activate

Planilha9.Range("C4:W39").ClearContents

UltimaLinha = Cells(Rows.Count, "a").End(xlUp).Row

For linha = 4 To UltimaLinha
    For coluna = 3 To 28
        
        If coluna = 3 Then
            numero = 2
        ElseIf coluna = 4 Then
            numero = 7
        ElseIf coluna = 5 Then
            numero = 8
        ElseIf coluna = 6 Then
            numero = 9
        ElseIf coluna = 7 Then
            numero = 5
        Else
            numero = coluna + 2
        End If
            
        Cells(linha, coluna).Value = Application.WorksheetFunction.VLookup(Range("B" & linha), Planilha1.Range("D3:AG250"), numero, 0)

    Next
Next

End Sub

Re: Duvida para diminuir codigo e deixar automatico VLOOKUP#47461

Por Jimmy - 28 Ago 2019 às 18:43

- 28 Ago 2019 às 18:43 #47461

Olá,

Substitua a macro que postou, pela macro abaixo.

Código: Selecionar todos

Sub procuraracomp()
Planilha9.Range("C4:W39").ClearContents
UltimaLinha = Planilha9.Cells(Rows.Count, "a").End(xlUp).Row

Dim dat As Date
Dim entrega As Date
For lin = 4 To UltimaLinha '17
    For Idx = 1 To 26
        Aux1 = 1
        If Idx > 1 Then Aux1 = 5
        If Idx = 5 Then Aux1 = 0
        If Idx > 5 Then Aux1 = 4
        Col1 = Idx + Aux1
        
        Aux1 = 2
        If Idx >= 5 Then Aux1 = 4

        Cells(Lin, Col2).value = Application.WorksheetFunction. _
            VLookup(Range("B" & lin), Planilha1.Range("D3:AG250"), Col1, 0)
    Next
Next

End Sub

Se postar a planilha, poderemos testar.

Jimmy San Juan

Nas mensagens que te ajudaram de alguma forma, dê seu LIKE: clique no "positivo" (ícone OBRIGADO).
Se o problema está encerrado, por favor, clique em MARCAR RESOLVIDO.

Este fórum está sendo desativado

Duvida para diminuir codigo e deixar automatico VLOOKUP

Duvida para diminuir codigo e deixar automatico VLOOKUP#47459

Re: Duvida para diminuir codigo e deixar automatico VLOOKUP#47460

Re: Duvida para diminuir codigo e deixar automatico VLOOKUP#47461

long long title how many chars? lets see 123 ok more? yes 60

Another post test yes yes yes or no, maybe ni? :-/

Do you need a super MOD? Well here it is. chew on this

Lasagna on me this time ok? I got plenty of cash