Greetings friends of MREXCEL, good evening
The file is used to calculate costs of products and services, and the macros automate the process of calculations and procedures, apparently it works well, but when I delete all the information to start using the file, it does not work. Does not send the information from the NATIONAL PRODUCT COSTS sheet to the PRODUCTS AND SERVICES PRICES sheet
I explain how it works:
NATIONAL PRODUCT COST SHEET:
It records information on national products and services, and includes:
A= Product or Service
B= NATIONAL
C= Unit of Measurement
D= Product Quantity
E= Total Purchase Amount
F and G= (formula) Unit Cost per Unit of Measurement
The Macro sends the product information (columns A and B) to the PRODUCT AND SERVICES PRICES sheet when registering the Total Purchase Amount (E).
PRICE SHEET PRODUCTS AND SERVICES:
Here the information on national products and services is recorded to calculate the price.
The information on products and services comes from the NATIONAL PRODUCT COSTS sheet and is:
A = Name of the Product or Service
B= NATIONAL.
The rest of the sheet is handled by filling out information in some columns and the rest is formula.
As I explained before, when I clear all the information to start from scratch, the macro does not work.
SHEET COST NATIONAL PRODUCTS VBA
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Me.Name = "COSTOS PRODUCTOS NACIONALES"
End Sub
Sub EnviarDatosCostosProductosNacionalesAPreciosProductosYServiciosA()
Application.ScreenUpdating = False
Dim ult, ult1 As Long
Dim rng As Range
ult2 = Sheets("COSTOS PRODUCTOS NACIONALES").Range("A" & Rows.Count).End(xlUp).Row
ult3 = Sheets("COSTOS PRODUCTOS NACIONALES").Range("B" & Rows.Count).End(xlUp).Row
ult = Sheets("PRECIOS PRODUCTOS Y SERVICIOS").Range("A" & Rows.Count).End(xlUp).Row + 1
ult1 = Sheets("PRECIOS PRODUCTOS Y SERVICIOS").Range("B" & Rows.Count).End(xlUp).Row + 1
Application.ScreenUpdating = False
Sheets("PRECIOS PRODUCTOS Y SERVICIOS").Range("B" & ult1) = Sheets("COSTOS PRODUCTOS NACIONALES").Range("B" & ult3).Value
Sheets("PRECIOS PRODUCTOS Y SERVICIOS").Range("A" & ult1) = Sheets("COSTOS PRODUCTOS NACIONALES").Range("A" & ult2).Value
Sheets("PRECIOS PRODUCTOS Y SERVICIOS").Select
MsgBox ("SE HA ENVIADO AL MODULO PRECIOS PRODUCTOS Y SERVICIOS, LA INFORMACIÓN DE ESTE PRODUCTO O SERVICIO." & Chr(13) & _
"POR FAVOR COMPLETE LA INFORMACIÓN SOLICITADA" & Chr(13) & _
"!OPERACIÓN REALIZADA SATISFACTORIAMENTE¡"), vbInformation, "CALCULADORA DE PRECIOS Y COSTOS DATCHEL PLUS"
Sheets("PRECIOS PRODUCTOS Y SERVICIOS").Range("E" & ult1).Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 5 Then
If Target.Address Like "$E$*" Then
If Range("E" & Target.Row) > 0 Then
Application.ScreenUpdating = False
Call EnviarDatosCostosProductosNacionalesAPreciosProductosYServiciosA
End If
End If
End If
Dim prod$, ufo&, ufd&
Dim pro_d As Range
Dim rDependents As Range
uf = Range("C" & Rows.Count).End(xlUp).Row
'prod = TempCombo
prod = Range("A" & Target.Row).Value
If Application.Intersect(Target, Range("C5:C" & uf)) Is Nothing And _
Application.Intersect(Target, Range("D5:D" & uf)) Is Nothing And _
Application.Intersect(Target, Range("E5:E" & uf)) Is Nothing Then
Exit Sub
End If
If Worksheets("COSTOS PRODUCTOS NACIONALES").Range("F" & Target.Row).Value > 0 Then
With Sheets("PRECIOS PRODUCTOS Y SERVICIOS")
ufd = .Range("A" & Rows.Count).End(xlUp).Row
Set pro_d = .Range("A5:A" & ufd).Find(prod)
pro_d.Offset(, 1) = Worksheets("COSTOS PRODUCTOS NACIONALES").Range("B" & Target.Row).Value
End With
End If
End Sub
The file is used to calculate costs of products and services, and the macros automate the process of calculations and procedures, apparently it works well, but when I delete all the information to start using the file, it does not work. Does not send the information from the NATIONAL PRODUCT COSTS sheet to the PRODUCTS AND SERVICES PRICES sheet
I explain how it works:
NATIONAL PRODUCT COST SHEET:
It records information on national products and services, and includes:
A= Product or Service
B= NATIONAL
C= Unit of Measurement
D= Product Quantity
E= Total Purchase Amount
F and G= (formula) Unit Cost per Unit of Measurement
The Macro sends the product information (columns A and B) to the PRODUCT AND SERVICES PRICES sheet when registering the Total Purchase Amount (E).
PRICE SHEET PRODUCTS AND SERVICES:
Here the information on national products and services is recorded to calculate the price.
The information on products and services comes from the NATIONAL PRODUCT COSTS sheet and is:
A = Name of the Product or Service
B= NATIONAL.
The rest of the sheet is handled by filling out information in some columns and the rest is formula.
As I explained before, when I clear all the information to start from scratch, the macro does not work.
| EXAMPLE MACRO DONT WORK.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | ABC | ||||||||
| 2 | COST | ||||||||
| 3 | PRODUCT | NACIONAL | MEASURE | Q | AMOUNT | UNIT COST | |||
| 4 | ₡ | ₡ | |||||||
| 5 | PRODUCT 1 | NACIONAL | Kilo | 1.000,00 | 100.000,00 | 100,00 | por Kilo | ||
| 6 | PRODUCT 2 | NACIONAL | Litro | 1.000,00 | 400.000,00 | 400,00 | por Litro | ||
| 7 | PRODUCT 3 | NACIONAL | Kilo | 1.000,00 | 300.000,00 | 300,00 | por Kilo | ||
| 8 | PRODUCT 4 | NACIONAL | Galón | 1.000,00 | 400.000,00 | 400,00 | por Galón | ||
| 9 | PRODUCT 5 | NACIONAL | Kilo | 1.000,00 | 40.000,00 | 40,00 | por Kilo | ||
| 10 | NACIONAL | 0,00 | 0 | ||||||
| 11 | NACIONAL | 0,00 | 0 | ||||||
| 12 | NACIONAL | 0,00 | 0 | ||||||
| 13 | NACIONAL | 0,00 | 0 | ||||||
| 14 | NACIONAL | 0,00 | 0 | ||||||
| 15 | |||||||||
| 16 | |||||||||
COSTOS PRODUCTOS NACIONALES | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F5:F14 | F5 | =IFERROR(E5/D5,0) |
| G5:G14 | G5 | =C5 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| G5:G14 | Cell Value | =0 | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| E5:E8 | Custom | =CONTAR.SI($A$5:$A$14;E5)<=1 |
| E9:E14 | Any value | |
| A5:B14 | Custom | =CONTAR.SI($A$5:$A$14;A5)<=1 |
| C5:C14 | List | =UNIDADES |
| EXAMPLE MACRO DONT WORK.xlsm | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | ABC | ||||||
| 2 | PRICE | ||||||
| 3 | PRODUCT | NACIONAL o IMPORTADO | MEASURE | UNIT COST | SALES | ||
| 4 | |||||||
| 5 | ₡ | 0 | |||||
| 6 | PRODUCT 1 | NACIONAL | Kilo | 100,00 | |||
| 7 | PRODUCT 2 | NACIONAL | Litro | 400,00 | |||
| 8 | PRODUCT 3 | NACIONAL | Kilo | 300,00 | |||
| 9 | PRODUCT 4 | NACIONAL | Galón | 400,00 | |||
| 10 | PRODUCT 5 | NACIONAL | Kilo | 40,00 | |||
| 11 | |||||||
| 12 | |||||||
| 13 | |||||||
| 14 | |||||||
| 15 | |||||||
| 16 | |||||||
PRECIOS PRODUCTOS Y SERVICIOS | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E5 | E5 | =SUM(E6:E14) |
| C6:C14 | C6 | =IFERROR(IF(B6="NACIONAL",VLOOKUP(A6,'COSTOS PRODUCTOS NACIONALES'!$A$5:$G$14,3,0),VLOOKUP(A6,#REF!,3,0))," ") |
| D6:D14 | D6 | =IFERROR(IF(B6="NACIONAL",VLOOKUP(A6,'COSTOS PRODUCTOS NACIONALES'!$A$5:$G$14,6,0),VLOOKUP(A6,#REF!,23,0))," ") |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| A10:A13 | Custom | =CONTAR.SI($A$5:$A$14;A10)<=1 |
| A6:B9 | Custom | =CONTAR.SI($A$5:$A$14;A6)<=1 |
SHEET COST NATIONAL PRODUCTS VBA
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Me.Name = "COSTOS PRODUCTOS NACIONALES"
End Sub
Sub EnviarDatosCostosProductosNacionalesAPreciosProductosYServiciosA()
Application.ScreenUpdating = False
Dim ult, ult1 As Long
Dim rng As Range
ult2 = Sheets("COSTOS PRODUCTOS NACIONALES").Range("A" & Rows.Count).End(xlUp).Row
ult3 = Sheets("COSTOS PRODUCTOS NACIONALES").Range("B" & Rows.Count).End(xlUp).Row
ult = Sheets("PRECIOS PRODUCTOS Y SERVICIOS").Range("A" & Rows.Count).End(xlUp).Row + 1
ult1 = Sheets("PRECIOS PRODUCTOS Y SERVICIOS").Range("B" & Rows.Count).End(xlUp).Row + 1
Application.ScreenUpdating = False
Sheets("PRECIOS PRODUCTOS Y SERVICIOS").Range("B" & ult1) = Sheets("COSTOS PRODUCTOS NACIONALES").Range("B" & ult3).Value
Sheets("PRECIOS PRODUCTOS Y SERVICIOS").Range("A" & ult1) = Sheets("COSTOS PRODUCTOS NACIONALES").Range("A" & ult2).Value
Sheets("PRECIOS PRODUCTOS Y SERVICIOS").Select
MsgBox ("SE HA ENVIADO AL MODULO PRECIOS PRODUCTOS Y SERVICIOS, LA INFORMACIÓN DE ESTE PRODUCTO O SERVICIO." & Chr(13) & _
"POR FAVOR COMPLETE LA INFORMACIÓN SOLICITADA" & Chr(13) & _
"!OPERACIÓN REALIZADA SATISFACTORIAMENTE¡"), vbInformation, "CALCULADORA DE PRECIOS Y COSTOS DATCHEL PLUS"
Sheets("PRECIOS PRODUCTOS Y SERVICIOS").Range("E" & ult1).Select
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 5 Then
If Target.Address Like "$E$*" Then
If Range("E" & Target.Row) > 0 Then
Application.ScreenUpdating = False
Call EnviarDatosCostosProductosNacionalesAPreciosProductosYServiciosA
End If
End If
End If
Dim prod$, ufo&, ufd&
Dim pro_d As Range
Dim rDependents As Range
uf = Range("C" & Rows.Count).End(xlUp).Row
'prod = TempCombo
prod = Range("A" & Target.Row).Value
If Application.Intersect(Target, Range("C5:C" & uf)) Is Nothing And _
Application.Intersect(Target, Range("D5:D" & uf)) Is Nothing And _
Application.Intersect(Target, Range("E5:E" & uf)) Is Nothing Then
Exit Sub
End If
If Worksheets("COSTOS PRODUCTOS NACIONALES").Range("F" & Target.Row).Value > 0 Then
With Sheets("PRECIOS PRODUCTOS Y SERVICIOS")
ufd = .Range("A" & Rows.Count).End(xlUp).Row
Set pro_d = .Range("A5:A" & ufd).Find(prod)
pro_d.Offset(, 1) = Worksheets("COSTOS PRODUCTOS NACIONALES").Range("B" & Target.Row).Value
End With
End If
End Sub
