Hi RoyUK,
Because I need to calculate the capacity for each day. For example, Item1AA, line 21 can product 650pcs on 8-Oct, line 32 can product 550pcs on 8-Oct => the total capacity of that day is 650+550=1200pcs. And I dont know how to calculate without separate rows
Hi RoyUK,
Sorry for wrong calculation because of manually update. Your formula worked perfectly, however I also wish the data can break into 5 rows if the fty products in 5 days (same as the format from col I to col O). Thank you in advance.
Hi all Experts,
I would like to say thank you in advance and hope you can help me on this. I have a data and need to calculate the production capacity per day, but manual breaking takes lots of time and typo. So I hope you can help me for vba code (please the attached file for clearly). Thanks again.
Hi expert,
Pls help me with this matter. Thanks in advance.
Hi graha_karya,
I want to keep the same format from OUTS_COMBINED sheet to EXPORT sheet.
Hi, [FONT="Calibri"]This macro help to extract columns which is marked by “x” letter on row 1 but it’s only copy the data without format. How can I change the code to keep the format from OUT_COMBINED sheet after extracting data to EXPORT sheet? Pls help me with this matter, Thank you.
[/FONT]Sub getColumn_outstanding()
Dim sArr(), dArr(), i As Long, J As Long, K As Long, c As Long, N As Long
Application.DisplayAlerts = False
With Sheets("OUTS_COMBINED")
c = .Range("A2").End(xlToRight).Column
sArr = .Range("A1", .Range("A2").End(xlDown)).Resize(, c).Value '.Resize(, c).Value
End With
ReDim dArr(1 To UBound(sArr), 1 To c)
For J = 1 To c
If sArr(1, J) <> Empty Then
N = N + 1
For i = 2 To UBound(sArr)
dArr(i - 1, N) = sArr(i, J)
Next i
End If
Next J
With Sheets("EXPORT")
.Range("A2").Resize(1000, 100).ClearContents
If N Then
.Range("A2").Resize(i - 1, N) = dArr
MsgBox "Done", , "getColumn_outstanding"
Else
MsgBox "Please enter x lettle above title row", , "getColumn"
End If
End With
Sheets("EXPORT").Select
Application.DisplayAlerts = True
End Sub[FONT="Calibri"][/FONT]
Hi, I want to ask you one more thing about this code. If I have many Data sheets to fill in, how can I add all charts into this code. Thank you in advance.
Set srcWS = Workbooks("Data.xlsx").Sheets("Sheet1")
Ex: DTC – SUMMARY CHART => get data from DTC – UPDATE
PFE– SUMMARY CHART => get data from PFE – UPDATE
CTT – SUMMARY CHART => get data from CTT – UPDATE
…. More than 10 charts like this.
Sub CopyData()
Application.ScreenUpdating = False
Dim LastRow As Long, srcWS As Worksheet, invoice As Range, fnd As Range
Set srcWS = Workbooks("Data.xlsx").Sheets("Sheet1")
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If ActiveCell.Column <> 5 Then
MsgBox ("Please select a range in Column E.")
Exit Sub
Else
For Each invoice In Selection
Set fnd = srcWS.Range("F:F").Find(invoice, LookIn:=xlValues, lookat:=xlWhole)
If Not fnd Is Nothing Then
invoice.Offset(0, 6).Resize(1, 3).Value = fnd.Offset(0, 6).Resize(1, 3).Value
End If
Next invoice
End If
Application.ScreenUpdating = True
End SubHi KjBox,
Thanks so much for your generous help. It's only took around 2min to finish its task. Thank you again, KjBox.
Hi Kjbox,
It worked, it worked, a thousand thanks for your help. Thank you so so so much KjBox.
Hi KjBox,
I test on row 9. Firstly, I remove P and Rev in col I and Y and col QRST nofill. Secondly, I input P in col I then col QRST turn green => OK. Third, I input "rev" in col Y, col QRST nofill =>it's should be yellow. Fouth, I remove P in col I then col QRST nofill => OK. Finally, I remove "rev" in col Y then col QRST turn green => it's should be nofill. It's my step to test.
Hi KjBox,
It's still not 
HI KjBox,
I'm sorry if it made you headache, but it's still highlight green eventhough i didnt input anything and the problem above is same.:(
Hi KjBox,
It's also same, cant highlight yellow if I input col (Y) after.
Hi KjBox,
2) If col(I)=P and col(Y) start with “Rev*” or “REV*” then color col Q,R,S,T into yellow. For this condition, if I input col(I) then col(Y) => it run very well, however, if I input col(Y) first and then col(I) => it's no fill instead of highlight yellow. Would you have any solution for this issue ?
Hi KjBox,
Pls refer the attached file. Thanks for your help.
Hi experts,
Pls help me with this master, Thanks in advance!
Hi Mumps,
The macro can run exactly, however because of the huge data in my chart, the macro took more than 5min to finish its task (full information from col A to col T and nearly 15000 rows). Anyway, thanks for your help so much, Mumps
Hi everyone,
Pls help me fix this code below. I want to highlight cells with conditions as follow list
Thanks for you help.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 9 And Target.Column <> 25 Then Exit Sub 'only run if input date in col I and col Y
If Target.Row <= 5 Then Exit Sub "only run from row 5
If Not IsEmpty(Cells(Target.Row, 4)) Then Exit Sub 'only run if col D blank
If Target.Column = 9 And Target.Value = "P" Then
Target.Offset(0, 8).Interior.ColorIndex = 4
Target.Offset(0, 9).Interior.ColorIndex = 4
Target.Offset(0, 10).Interior.ColorIndex = 4
Target.Offset(0, 11).Interior.ColorIndex = 4
ElseIf Cells(Target.Row, 9) = "P" Or Left(Target.Row, 25),3) = "Rev" Then 'check if no
Target.Offset(0, -8).Interior.ColorIndex = 6
Target.Offset(0, -7).Interior.ColorIndex = 6
Target.Offset(0, -6).Interior.ColorIndex = 6
Target.Offset(0, -5).Interior.ColorIndex = 6
Else
Target.Offset(0, 8).Interior.ColorIndex = xlNone
Target.Offset(0, 9).Interior.ColorIndex = xlNone
Target.Offset(0, 10).Interior.ColorIndex = xlNone
Target.Offset(0, 11).Interior.ColorIndex = xlNone
End If
End SubHi Mumps,
I use macro to add fomular into col T,U,V,X,Y,AC and AD, but the data of sheet Fr orders is too much, so that my code didn’t run well. I vlookup data from Data Wb (Outstanding sheet) into FR orders, and compare have any duplicate with sheet Fabship and Trimship. Pls see the attached file. Thanks for your generous help.
Sub formula_FRorders()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim lRow As Long
Set ws = Sheets("FR orders")
With ws
lRow = .Range("A" & .Rows.count).End(xlUp).row
.Range("T2:T" & lRow).Formula = "=LEFT(RC[-18],10)"
'.Range("T2:T" & lRow).Value2 = .Range("T2:T" & lRow).Value2
.Range("U2:U" & lRow).Formula = "=IFERROR(VLOOKUP(RC[-19],[Data.xlsx]Outstanding!C1:C6,3,0),IFERROR(VLOOKUP(LEFT(RC[-19],10),[Data.xlsx]Outstanding!C1:C6,3,0),""-""))"
'.Range("U2:U" & lRow).Value2 = .Range("U2:U" & lRow).Value2
.Range("V2:V" & lRow).Formula = "=IFERROR(VLOOKUP(RC[-20],[Data.xlsx]Outstanding!C1:C6,4,0),IFERROR(VLOOKUP(LEFT(RC[-20],10),[Data.xlsx]Outstanding!C1:C6,4,0),""-""))"
'.Range("V2:V" & lRow).Value2 = .Range("V2:V" & lRow).Value2
.Range("X2:X" & lRow).Formula = "=IFERROR(VLOOKUP(RC[-22],[Data.xlsx]Outstanding!C1:C6,5,0),IFERROR(VLOOKUP(LEFT(RC[-22],10),[Data.xlsx]Outstanding!C1:C6,5,0),""-""))"
'.Range("X2:X" & lRow).Value2 = .Range("X2:X" & lRow).Value2
.Range("Y2:Y" & lRow).Formula = "=IFERROR(VLOOKUP(RC[-23],[Data.xlsx]Outstanding!C1:C6,6,0),IFERROR(VLOOKUP(LEFT(RC[-23],10),[Data.xlsx]Outstanding!C1:C6,6,0),""-""))"
'.Range("Y2:Y" & lRow).Value2 = .Range("Y2:Y" & lRow).Value2
.Range("AC2:AC" & lRow).Formula = "=IF(ISERROR(MATCH(RC[-18],FabShipping!C1,0)),"""",""DUPLICATE"")"
'.Range("AC2:AC" & lRow).Value2 = .Range("AC2:AC" & lRow).Value2
.Range("AD2:AD" & lRow).Formula = "=IF(ISERROR(MATCH(RC[-19],TrimShipping!C1,0)),"""",""DUPLICATE"")"
'.Range("AD2:AD" & lRow).Value2 = .Range("AD2:AD" & lRow).Value2
End With
Application.ScreenUpdating = True
End SubHi Mumps, Thanks for your generous help. The macro run very well, but could you please explain to me which lines of the code to get the data from (col L,M,N) of Data Wb and paste into (col K,L,M) of SHIPVIET? And how can it paste exactly to col K,L,M in SHIPVIET Wb? And if the col in SHIPVIET Wb and Data Wb arenot continously, could you give me some solutions ? I’m very new to VBA, hope that you can help me in this matter. Thanks again,Mumps.