Macros code

[Closed]
Report
Posts
3
Registration date
Wednesday January 7, 2015
Status
Member
Last seen
January 8, 2015
-
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
-
My excel depends on date column....

I have used macros to calculate all my required columns. So when I generate data for today or tomorrow I get on same row, for tomorrows it should generate on next row...but I dont get :(

Please help me, highly appreciated.

Thanks in advance

3 replies

You should post your macro so we can see what you have already. It will help to debug it.
As it is, ANYTHING could be causing it to fail.
Posts
3
Registration date
Wednesday January 7, 2015
Status
Member
Last seen
January 8, 2015

Hello RayH,

First of all, I really thank you for responding my quiries.

How to attach my excel sheet here?
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482 >
Posts
3
Registration date
Wednesday January 7, 2015
Status
Member
Last seen
January 8, 2015

Hi Gokulcgs,

Files can be shared using free file sharing sites like www.speedyshare.com or ge.tt. Once uploaded you will get a download link, which you then paste in your next post here. Always be careful with sensitive information.

Best regards,
Trowa
Posts
3
Registration date
Wednesday January 7, 2015
Status
Member
Last seen
January 8, 2015
>
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021

http://speedy.sh/zWF9q/sheet.xls

I have attached the excel sheet..

I have no problem,if you can write a new macros code and I will tell you what is the actuall requirement.

1) In daily trend analysiz sheet, we require datas with respect to date column.
2) In incoming there are two type - Simplex and Complex
2)i)In simplex column, data should come from feasiblity tracker matching date and simplex - only count should be in daily trend analysiz
2)ii) same for complex column, data should come from feasiblity tracker matching date and complex - only count should be in daily trend analysiz
3)In closed per day column, two type - Simplex and Complex
3)i)for this we have go to daily tracker - filter date, FNC status - only should be withdrawn and completed and count number of simplex and complex and pasting the count in daily trend analysiz respective coulms with date.
4)In Pending per day column,
4)i)for this we have go to daily tracker - filter date, FNC status - only should be In-progress and pending and count number of simplex and complex and pasting the count in daily trend analysiz respective coulms with date.
5)Pending till date - total count in pending tracker
6)out of hours - simplex and complex
6)i)for this we have go to daily tracker - filter date, FNC status column - only should be completed, Out of Scope/ Out of Hour column - only OOH and count number of simplex and complex and pasting the count in daily trend analysiz respective coulms with date.
7)Out of Scope - simplex and complex
7)i)for this we have go to daily tracker - filter date, FNC status - only should be Re-allocted and count number of simplex and complex and pasting the count in daily trend analysiz respective coulms with date.
8)Simplex ( 4 Hrs ) - Met SPT - value should come from closed per day - simplex Missed SPT - should always be zero
9Complex ( 4 Hrs ) - Met SPT - value should come from closed per day - complex Missed SPT - should always be zero

Above all requires only counts(number of orders per day) in Daily trend analysiz.

The same should come day by day with Macros..

I know its long,but really I would appreciate if this can solved...Hoping the best result..

Thanks in advance.
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482
Hi Gokulcgs,

No need for macro's so far (I'm at point 3).

Sheet "Daily Trend Analysiz".
C5: =SUM(('Feasibility Tracker'!$B:$B=$B5)*('Feasibility Tracker'!J:J=C$4))
This is an array formula and needs to be confirmed by hitting Ctrl+Shift+Enter, when done correctly the formula will be enclosed with curly brackets.
D5: drag the formula from C5 here.
E5: =C5+D5

F5: =SUM(('Daily Tracker'!$B:$B=$B5)*(('Daily Tracker'!$V:$V="Withdrawn")+('Daily Tracker'!$V:$V="Completed"))*('Daily Tracker'!I:I=F$4))
This is also an array formula.
G5: Drag the formula from F5 here.
H5: =F5+G5

Now select all the above formula's and drag them down as far as needed.

Taking a quick look at the rest of your points, it seems the same kind of formula's would do the job.

So give it a try and let me know if you get stuck.

Best regards,
Trowa
Hi TrowaD,

Wow Worked!!!

Really appreciate your efforts on this work. Love your work!!! Thanks a heap!!!

A last request to you with regard to same spreadsheet,

In pending Tracker, we have to update the day by day pending orders alone.
so the source should come from Daily tracker.

Each days pending order should be updated in pending tracker and duplication should be removed. Also same day's completed order should be checked in pending tracker, if the order is exist then it should removed from pending tracker.

For example:

Daily tracker:

Date 25/01/2015
order - 123456
status - Pending
order - 987456
status - completed

In pending tracker:

order - 123456 is to be REPLACED if already existing - if not then the order should be ADDED NEWLY.

order - 987456 is to be DELETED if already existing - if not then no action required.

Thanks in advance.
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482
Hi Gokulcgs,

Good to see it worked out for you.

In regards to your new request, it is unclear to me which data from Daily tracker should go to Pending tracker, since the heading don't match.

Something like:
Daily tracker - Column B (Date) --> Pending tracker - Column D (Order created)

Could you do this for all necessary columns?

Since this one needs to be done with macro, let me ask you in advance if you know how to implement and use a macro code?

Best regards,
Trowa
>
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021

Hi TrowaD,

My apology for giving you unclear request.

Daily tracker - Column C (NNN) --> Pending tracker - Column B (NNN Orders)

Daily tracker - Column D (Product Type) --> Pending tracker - Column C (Product)

Daily tracker - Column F (NNN logged date and time) --> Pending tracker - Column D (Order created)

Daily tracker - Column P (Pending Time) --> Pending tracker - Column E (Order Recently Parked)

Daily tracker - Column U (Enginner) --> Pending tracker - Column G (Enginner)

Daily tracker - Column V (Current NNN Status) --> Pending tracker - Column H (Status of the NNN)


*********************************************************************************************************
In pending Tracker, we have to update the day by day pending orders alone.
so the source should come from Daily tracker.

Each days pending order should be updated in pending tracker and duplication should be removed. Also same day's completed order should be checked in pending tracker, if the order is exist then it should removed from pending tracker.

-------------------------------------------------------------------------------------------------------------------------------

Pending orders --- is to be REPLACED if already existing - if not then the order should be ADDED NEWLY.

Completed orders --- is to be DELETED if already existing - if not then no action required.

-------------------------------------------------------------------------------------------------------------------------------

For example:

Daily tracker:

Date 25/01/2015
order - 123456
status - Pending
order - 987456
status - completed

In pending tracker:

order - 123456(pending) is to be REPLACED if already existing - if not then the order should be ADDED NEWLY.

order - 987456(completed) is to be DELETED if already existing - if not then no action required.

Thanks in advance, appreciate your help as always:)
HI TrowaD,

Any luck on above request...

Thanks in advance
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482
Hi Gokulcgs,

Yes, I have got a code for you to try.

But I noticed some duplicates in the order numbers. Not sure if that is there on purpose or that you were testing something for yourself.

Your Column A row numbers may get messed up because of the deletion of completed orders. May I advise you to use a formula for that: =ROW(A2)-1 (to be placed in A2). Drag the formula down as far as desired. Or a more clean version: =IF(B2<>"",ROW(A2)-1,"")

Let me know how the code is to your liking:
Sub RunMe()
Dim lRow, lRow2, lRow3 As Integer
Dim fOrder As Range

Sheets("Daily Tracker").Select
lRow = Range("C" & Rows.Count).End(xlUp).Row
lRow2 = Sheets("Pending Tracker").Range("B" & Rows.Count).End(xlUp).Row

For Each cell In Range("C2:C" & lRow)
    If cell.Offset(0, 19) = "Pending" Then
    
        With Sheets("Pending Tracker").Range("B2:B" & lRow2)
            Set fOrder = .Find(cell.Value, LookIn:=xlValues)
            If fOrder Is Nothing Then
                lRow3 = Sheets("Pending Tracker").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row
                Sheets("Pending Tracker").Cells(lRow3, "B") = cell.Value
                Sheets("Pending Tracker").Cells(lRow3, "C") = cell.Offset(0, 1).Value
                Sheets("Pending Tracker").Cells(lRow3, "D") = cell.Offset(0, 3).Value
                Sheets("Pending Tracker").Cells(lRow3, "E") = cell.Offset(0, 13).Value
                Sheets("Pending Tracker").Cells(lRow3, "G") = cell.Offset(0, 18).Value
                Sheets("Pending Tracker").Cells(lRow3, "H") = cell.Offset(0, 19).Value
            Else
                Sheets("Pending Tracker").Cells(fOrder.Row, "C") = cell.Offset(0, 1).Value
                Sheets("Pending Tracker").Cells(fOrder.Row, "D") = cell.Offset(0, 3).Value
                Sheets("Pending Tracker").Cells(fOrder.Row, "E") = cell.Offset(0, 13).Value
                Sheets("Pending Tracker").Cells(fOrder.Row, "G") = cell.Offset(0, 18).Value
                Sheets("Pending Tracker").Cells(fOrder.Row, "H") = cell.Offset(0, 19).Value
            End If
        End With
        
    ElseIf cell.Offset(0, 19) = "Completed" Then
    
        With Sheets("Pending Tracker").Range("B2:B" & lRow2)
            Set fOrder = .Find(cell.Value, LookIn:=xlValues)
            If Not fOrder Is Nothing Then
                fOrder.EntireRow.Delete
            End If
        End With
        
    End If
Next cell

End Sub


Best regards,
Trowa