cooleomter
New Member
- Joined
- Sep 3, 2024
- Messages
- 23
- Office Version
- 365
- 2021
- Platform
- Windows
Hi can you help me. how can i convert my excel formula to vba code. ( =@IFERROR(IFS($D$2="HDROP-15",INDEX(Serial!$E$2:$E$25426,MATCH('MASTER COPY'!E8,Serial!$A$2:$A$25426,0)),$D$2="HDROP-14",INDEX(Serial!$E$2:$E$25426,MATCH('MASTER COPY'!E8,Serial!$A$2:$A$25426,0)),$D$2="IND-B",INDEX(Serial!$D$2:$D$25426,MATCH('MASTER COPY'!E8,Serial!$A$2:$A$25426,0)),OR($D$2="HDROP-FK1",$D$2="JD2100",$D$2="JD2000WH",$D$2="JD2000BK"),INDEX(Serial!$B$2:$B$25426,MATCH('MASTER COPY'!E8,Serial!$A$2:$A$25426,0)),$D$2="ZR-02",INDEX(Serial!$F$2:$F$25426,MATCH('MASTER COPY'!E8,Serial!$A$2:$A$25426,0))),"")..this one is for column A...
and
this ones is for column b (=@IFERROR(IFS(OR($D$2="HDROP-14",$D$2="HDROP-15"),INDEX(Serial!$C$2:$C$25426,MATCH('MASTER COPY'!E8,Serial!$A$2:$A$25426,0))),"")
its serial matching index is taken from another sheet which is named Serial
my code is triggered in colunm c:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, r As Range
Set rng = Intersect(Target, [C8:C3000])
If rng Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In rng
If Trim$(r) <> "" Then
r(, 2) = [f1]
With r(, 4).Resize(, 2)
.Value = Array(Time, Date)
.NumberFormat = Array("h:mm:ss AM/PM", "mm/dd/yyyy")
End With
r(, 6).Resize(, 2) = Array([D2], [F2])
Else
r.Range("b1,d1:g1").ClearContents
End If
Next
Application.EnableEvents = True
ThisWorkbook.Save
End Sub
this is my main excel file
and my serial sheet is;
hope you can help me.
and
this ones is for column b (=@IFERROR(IFS(OR($D$2="HDROP-14",$D$2="HDROP-15"),INDEX(Serial!$C$2:$C$25426,MATCH('MASTER COPY'!E8,Serial!$A$2:$A$25426,0))),"")
its serial matching index is taken from another sheet which is named Serial
my code is triggered in colunm c:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, r As Range
Set rng = Intersect(Target, [C8:C3000])
If rng Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In rng
If Trim$(r) <> "" Then
r(, 2) = [f1]
With r(, 4).Resize(, 2)
.Value = Array(Time, Date)
.NumberFormat = Array("h:mm:ss AM/PM", "mm/dd/yyyy")
End With
r(, 6).Resize(, 2) = Array([D2], [F2])
Else
r.Range("b1,d1:g1").ClearContents
End If
Next
Application.EnableEvents = True
ThisWorkbook.Save
End Sub
this is my main excel file
test4.xlsm | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | Category: | Packing Operator | Shipment Plan Qty: | |||||||||
2 | Model | Weighing Operator | Finished Good Qty.: | |||||||||
3 | Shipment Date: | Shipment Freight | Good Weight | |||||||||
4 | Start Serial | 1 | End Serial | NG Weight | ||||||||
5 | Minimum | Maximum | Lacking | |||||||||
6 | Excess | |||||||||||
7 | Box No. BIG | Box No. SMALL | Weight | Packing Operator | Serial Number | Time | Date | Model | Weighing Operator | Remarks | ||
8 | ||||||||||||
9 | ||||||||||||
10 | ||||||||||||
11 | ||||||||||||
12 | ||||||||||||
13 | ||||||||||||
14 | ||||||||||||
15 | ||||||||||||
MASTER COPY |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D5 | D5 | =IF((D2=""),"",INDEX(Validation!I2:I8,MATCH('MASTER COPY'!D2,Validation!F2:F7,0))) |
F5 | F5 | =IF((D2=""),"",INDEX(Validation!J2:J8,MATCH('MASTER COPY'!D2,Validation!F2:F7,0))) |
H2 | H2 | =COUNT(C8:C100384) |
H3 | H3 | =H2-H4 |
H4 | H4 | =((COUNTIFS(C8:C100000,"<"&D5)+(COUNTIFS(C8:C100000,">"&F5)))) |
H5 | H5 | =((COUNTIFS(C8:C10001,"<"&D5))) |
H6 | H6 | =((COUNTIFS(L8:L10003,">"&O5))) |
A8:A15 | A8 | =IFERROR(IFS($D$2="HDROP-15",INDEX(Serial!$E$2:$E$25426,MATCH('MASTER COPY'!E8,Serial!$A$2:$A$25426,0)),$D$2="HDROP-14",INDEX(Serial!$E$2:$E$25426,MATCH('MASTER COPY'!E8,Serial!$A$2:$A$25426,0)),$D$2="IND-B",INDEX(Serial!$D$2:$D$25426,MATCH('MASTER COPY'!E8,Serial!$A$2:$A$25426,0)),OR($D$2="HDROP-FK1",$D$2="JD2100",$D$2="JD2000WH",$D$2="JD2000BK"),INDEX(Serial!$B$2:$B$25426,MATCH('MASTER COPY'!E8,Serial!$A$2:$A$25426,0)),$D$2="ZR-02",INDEX(Serial!$F$2:$F$25426,MATCH('MASTER COPY'!E8,Serial!$A$2:$A$25426,0))),"") |
B8:B15 | B8 | =IFERROR(IFS(OR($D$2="HDROP-14",$D$2="HDROP-15"),INDEX(Serial!$C$2:$C$25426,MATCH('MASTER COPY'!E8,Serial!$A$2:$A$25426,0))),"") |
E7 | E7 | =IF(OR(D1="Option",D1="Harness"),"Item Count","Serial Number") |
E8 | E8 | =IF(C8="","",D4) |
E9,E12:E15 | E9 | =IF(C9="","",E8+1) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
ACCList | =Validation!$F$5:$F$7 | D5, F5 |
OPList | =Validation!$F$3:$F$4 | D5, F5 |
SIBList | =Validation!$F$2 | D5, F5 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
C8:C1048576 | Cell Value | <$D$5 | text | NO |
C8:C1048576 | Cell Value | >$F$5 | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
D1 | List | =ManuList |
D2 | List | =INDIRECT(VLOOKUP($D$1,ManuLookUp,2,0) &"List") |
D3 | Any value | |
F1:F2 | List | =OFFSET(Validation!$A$2:$A$999267,0,0,COUNTA(Validation!$A$2:$A$198)-0,1) |
F3 | List | =Validation!$C$10:$C$14 |
and my serial sheet is;
test4.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Serial | 10pcs | 25pcs | 50pcs | 100 pcs | 250pcs | ||
2 | 1 | 1 | 1 | 1 | 1 | 1 | ||
3 | 2 | 1 | 1 | 1 | 1 | 1 | ||
4 | 3 | 1 | 1 | 1 | 1 | 1 | ||
5 | 4 | 1 | 1 | 1 | 1 | 1 | ||
6 | 5 | 1 | 1 | 1 | 1 | 1 | ||
7 | 6 | 1 | 1 | 1 | 1 | 1 | ||
8 | 7 | 1 | 1 | 1 | 1 | 1 | ||
9 | 8 | 1 | 1 | 1 | 1 | 1 | ||
10 | 9 | 1 | 1 | 1 | 1 | 1 | ||
11 | 10 | 1 | 1 | 1 | 1 | 1 | ||
12 | 11 | 2 | 1 | 1 | 1 | 1 | ||
13 | 12 | 2 | 1 | 1 | 1 | 1 | ||
14 | 13 | 2 | 1 | 1 | 1 | 1 | ||
15 | 14 | 2 | 1 | 1 | 1 | 1 | ||
16 | 15 | 2 | 1 | 1 | 1 | 1 | ||
17 | 16 | 2 | 1 | 1 | 1 | 1 | ||
18 | 17 | 2 | 1 | 1 | 1 | 1 | ||
19 | 18 | 2 | 1 | 1 | 1 | 1 | ||
20 | 19 | 2 | 1 | 1 | 1 | 1 | ||
21 | 20 | 2 | 1 | 1 | 1 | 1 | ||
22 | 21 | 3 | 1 | 1 | 1 | 1 | ||
23 | 22 | 3 | 1 | 1 | 1 | 1 | ||
24 | 23 | 3 | 1 | 1 | 1 | 1 | ||
25 | 24 | 3 | 1 | 1 | 1 | 1 | ||
26 | 25 | 3 | 1 | 1 | 1 | 1 | ||
27 | 26 | 3 | 2 | 1 | 1 | 1 | ||
28 | 27 | 3 | 2 | 1 | 1 | 1 | ||
Serial |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D3:F27,B28:F28,B27,B23:C26,C3:C22,B13:B21,B3:B11 | D3 | =D2 |
A2 | A2 | ='MASTER COPY'!D4 |
A3:A28 | A3 | =A2+1 |
hope you can help me.