Tengo tres hojas: "bom", "MPS" y "DData". Lo que intento hacer es leer primero el valor de la celda A2 de "MPS" y recuperar todas las filas de "bom" que en la columna A tienen ese valor y enumerarlas en "DData".
Al mismo tiempo, necesitaría que los valores de las columnas C y D de "MPS" se recuperaran en las líneas correspondientes. Entonces, si el valor en la celda A2 de "MPS" coincide con 4 líneas en "bom", los valores de las celdas C2 y D2 deben colocarse después de esas 4 líneas. Esto no funciona correctamente en este momento.
Una vez realizado este ciclo, debería pasar al valor de celda A3 en "MPS" y así sucesivamente... El siguiente código funciona de alguna manera. Intenté agregar un segundo bucle for y todo lo demás que se me ocurrió, pero no tuve buena suerte. El mayor problema es que si MPS!A2tiene el valor 1, A3= 2 y A4vuelve a ser 1, no enumera los valores de "bom" por segunda vez.
El código se basa originalmente en esto: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
Mi mente dice que esto definitivamente necesita dos bucles pero no pude hacerlo funcionar.
No puedo publicar imágenes, pero intentaré ilustrar mejor lo que se necesita y lo que está sucediendo a continuación.
Estructura y datos de la hoja "bom" (rango 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
Estructura y datos de la hoja "MPS" (rango A1:D4):
id desc week batches
30010 build1 1 2
20010 build2 2 4
30010 build1 2 0
Estructura de la hoja "DData" (rango A1:H3) y lo que se devuelve con su código panhandel:
id desc id_part desc_part qty week batches total(=qty*batches)
30010 1 2
30010 2 0
Y mi objetivo es 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
... también donde H2 por ejemplo tendría el valor de E2*G2.
*** Intenté cambiar
Sheets("DData").Range("A" & countRows2).Value = Sheets("bom").Range("A" & lCount).Value
a
Sheets("DData").Rows(countRows2).Value = Sheets("bom").Rows(lCount).Value
por ejemplo, pero Excel empezó a fallar con fuerza. ¿Es más prudente utilizar Rango en lugar de Filas?
Respuesta1
EDITADO: Un bucle va línea por línea a través de la columna A de MPS, el segundo bucle compara cada valor de la columna A de MPS con todos los valores de la columna A "bom". Una vez que se encuentra una coincidencia, se copia cada celda individual (estoy seguro de que hay una manera más rápida de hacerlo, pero esto ilustra bien lo que está sucediendo) en la hoja DData, y la columna H obtiene una fórmula para calcular el total.
Las pestañas están configuradas de la misma manera que las que tiene ahora y dan como resultado lo que espera/necesita.
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