Private Sub CommandButton1_Click()
Dim Fila As Long
Dim Fila2 As Long
Dim Col As Integer
Col = 3 ' columna C
Fila = 4
'coloca montos totales en hoja2
Do While Sheet2.Cells(Fila, "B") <> ""
Call vlookup_data(Sheet2.Cells(Fila, "B").Value, Col, Fila)
Fila = Fila + 1
Loop
Fila = 3
'Limpiar hoja de QAD
Sheet1.Range("D3:E11").Clear
'consolidado de venta
Do While Sheet1.Cells(Fila, "A") <> ""
Fila2 = 4
Do While Sheet2.Cells(Fila2, "B") <> ""
If Sheet2.Cells(Fila2, "B") = Sheet1.Cells(Fila, "A") Then
'sumamos las cantidades vendidas por item
Sheet1.Cells(Fila, "D") = Sheet1.Cells(Fila, "D") + Sheet2.Cells(Fila2, "C")
'sumamos lo montos
Sheet1.Cells(Fila, "E") = Sheet1.Cells(Fila, "E") + Sheet2.Cells(Fila2, "E")
End If
Fila2 = Fila2 + 1
Loop
Fila = Fila + 1
Loop
End Sub
Sub vlookup_data(val_look As String, val_col As Integer, val_fila As Long)
Dim resultado As Long
Dim temp_col As Integer
resultado = 0
On Error Resume Next
resultado = Application.WorksheetFunction.VLookup(val_look, Sheet1.Range("A3:C10"), val_col, False)
If Err.Number <> 0 Then
End If
On Error GoTo 0 ''
If Err.Number = 0 Then
Sheet2.Cells(val_fila, "E").Value = Sheet2.Cells(val_fila, "C").Value * resultado
End If
End Sub
No hay comentarios
https://www.programacionparatodos.com/2019/10/macro-de-excel-para-utilizar-vlookup-o.html