Tenho três planilhas: "bom", "MPS" e "DData". O que estou tentando fazer é primeiro ler o valor da célula A2 de "MPS" e buscar todas as linhas de "bom" que na coluna A tenham esse valor e listá-las em "DData".
Ao mesmo tempo, eu precisaria que os valores nas colunas C e D de "MPS" fossem buscados nas linhas correspondentes. Portanto, se o valor no valor da célula A2 "MPS" corresponder a 4 linhas em "bom", os valores das células C2 e D2 deverão ser colocados após essas 4 linhas. Isso não funciona corretamente no momento.
Uma vez concluído este loop, ele deve passar para o valor da célula A3 em "MPS" e assim por diante... O código abaixo funciona de alguma forma. Tentei adicionar um segundo loop for e tudo o mais que me veio à mente, mas sem sorte. O maior problema é que se MPS!A2tiver valor 1, A3= 2 e A4for 1 novamente, ele não lista os valores de "bom" uma segunda vez.
O código é baseado originalmente nisso:https://stackoverflow.com/a/26912176
Public Sub CommandButton1_Click()
Dim countRows1 As Long, countRows2 As Long
countRows1 = 2 'the first row of your dataset in sheet1
endRows1 = 50 'the last row of your dataset in sheet1
countRows2 = 2 'the first row where you want to start writing the found rows
For j = countRows1 To endRows1
Dim keyword As String: keyword = Sheets("MPS").Cells("A2, A100").Value
If Sheets("bom").Range("A2, A100").Value = keyword Then
Sheets("DData").Rows(countRows2).Value = Sheets("bom").Rows(j).Value
Sheets("DData").Rows(countRows2).Cells(6).Value = Sheets("MPS").Rows(countRows2).Cells(3).Value
Sheets("DData").Rows(countRows2).Cells(7).Value = Sheets("MPS").Rows(countRows2).Cells(4).Value
countRows2 = countRows2 + 1
End If
Next j
End Sub
Minha mente diz que isso definitivamente precisa de dois loops, mas simplesmente não consegui fazê-lo funcionar.
Não posso postar imagens mas tentarei ilustrar melhor o que é necessário e está acontecendo abaixo.
Estrutura e dados da planilha "bom" (faixa A1:E7):
id desc id_part desc_part qty
30010 build1 10200 part1 1
30010 build1 23002 part2 3
30010 build1 21003 part3 500
30010 build1 21503 part4 400
20010 build2 10210 part5 100
20010 build2 10001 part6 5
Estrutura e dados da planilha "MPS" (intervalo A1:D4):
id desc week batches
30010 build1 1 2
20010 build2 2 4
30010 build1 2 0
Estrutura "DData" da planilha (intervalo A1:H3) e o que é retornado com seu código panhandel:
id desc id_part desc_part qty week batches total(=qty*batches)
30010 1 2
30010 2 0
E meu objetivo é este:
id desc id_part desc_part qty week batches total (=qty*batches)
30010 build1 10200 part1 1 1 2
30010 build1 23002 part2 3 1 2
30010 build1 21003 part3 500 1 2
30010 build1 21503 part4 400 1 2
20010 build2 10210 part5 100 2 4
20010 build2 10001 part6 5 2 4
30010 build1 10200 part1 1 2 0
30010 build1 23002 part2 3 2 0
30010 build1 21003 part3 500 2 0
30010 build1 21503 part4 400 2 0
... também onde H2 por exemplo teria o valor de E2*G2.
*** Eu tentei mudar
Sheets("DData").Range("A" & countRows2).Value = Sheets("bom").Range("A" & lCount).Value
para
Sheets("DData").Rows(countRows2).Value = Sheets("bom").Rows(lCount).Value
por exemplo, mas o Excel começou a travar fortemente. É mais sensato usar Range em vez de Rows?
Responder1
EDITADO: Um loop passa linha por linha pela coluna A do MPS, o segundo loop compara cada valor da coluna A do MPS com todos os valores da coluna A "bom". Depois que uma correspondência é encontrada, cada célula individual é copiada (com certeza há uma maneira mais rápida de fazer isso, mas ilustra bem o que está acontecendo) para a planilha DData, e a coluna H obtém uma fórmula para calcular seu total.
As guias são definidas da mesma forma que você tem agora e resultam no que você espera/precisa.
Sub Button1_Click()
Dim countRows2 As Long
countRows2 = 2 'the first row where you want to start writing the found rows
Dim szMPSValues As Variant
Dim szbomValues As Variant
Dim lCount As Long
Dim lCountbom As Long
Dim MPSRng As Range
Dim bomRng As Range
Dim szConcatString As Variant
Dim strKeyword As String
'gets range of used cells
Set MPSRng = Intersect(Columns("A").Cells, Worksheets("MPS").UsedRange)
If MPSRng Is Nothing Then MsgBox "Nothing to do"
'have to switch sheets to set the second loop's range of "bom" values
Worksheets("bom").Activate
Set bomRng = Intersect(Columns("A").Cells, Worksheets("bom").UsedRange)
Worksheets("MPS").Activate
'saves range values into arrays
szMPSValues = MPSRng.Value
szbomValues = bomRng.Value
'double check a to be sure its an array and of proper size
If Not IsArray(szMPSValues) Then ReDim a(1, 1): szMPSValues = MPSRng.Value
'loop through array concatenating cell values with a space after cell value
'NOTE: Changed this to start at 2 in case you have a header row**
For lCount = 2 To UBound(szMPSValues)
strKeyword = Sheets("MPS").Range("A" & lCount).Value 'gets MPS.A2, MPS.A3, etc
For lCountbom = 2 To UBound(szbomValues)
If Sheets("bom").Range("A" & lCountbom).Value = strKeyword Then 'compares to bom.A2, bom.A3, etc
Sheets("DData").Range("A" & countRows2).Value = Sheets("bom").Range("A" & lCountbom).Value
Sheets("DData").Range("B" & countRows2).Value = Sheets("bom").Range("B" & lCountbom).Value
Sheets("DData").Range("C" & countRows2).Value = Sheets("bom").Range("C" & lCountbom).Value
Sheets("DData").Range("D" & countRows2).Value = Sheets("bom").Range("D" & lCountbom).Value
Sheets("DData").Range("E" & countRows2).Value = Sheets("bom").Range("E" & lCountbom).Value
Sheets("DData").Range("F" & countRows2).Value = Sheets("MPS").Range("C" & lCount).Value
Sheets("DData").Range("G" & countRows2).Value = Sheets("MPS").Range("D" & lCount).Value
Sheets("DData").Range("H" & countRows2).Formula = "=$F" & countRows2 & "*$G" & countRows2
countRows2 = countRows2 + 1
End If
Next lCountbom
Next lCount
End Sub