Need VBA Code for Specific data

[chiranjiv poojari]

Hi Team,

I had already working on this file but got stuck with below data so need your help.
Sugeestion : Use the below code and after that only start working on it .

Needed VBA code for below data

1) Current Status (AF) : All Paid invoices which is paid in Dec Month as per paid date (AD) : Needed comment in New current status in AG column as Paid in Dec and Actionable to in (AH column )should be Closed
2) Current Status (AF) : All Paid invoices which is paid prior to Dec Month as per paid date (AD) : Needed comment in New current status in AG column as Paid Prior to Dec and Actionable to (AH column)should be Closed.
3) Current Status (AF) : All unpaid invoices + Pending Approval with finance + Pending with Finance :Needed comment in New current status in AG column as With Finance and Actionable to (AH column )should be STC.
4) Current Status (AF) : Pending with Regional SPOC + Rejected by SPOC : Needed comment in New current status in AG column as With SPOC and Actionable to (AH column )should be STC
5) Current Status (AF) : Pending with L2 + Rejected with L2 : Needed comment in New current status in AG column as With L2 and Actionable to (AH column )should be STC
6) Current Status (AF) : Workflow not initiated : Needed comment in New current status in AG column as Workflow not initiated and Actionable to (AH column )should be Recently received .
7) Current Status (AF) : Rejected by Finance : Needed comment in New current status in AG column as Actionable to STC and Actionable to (AH column ) should be STC
8) Current Status (AF) :1ST FILTER : Rejected by CIPC there will be two filter as per reason for pending in column X
Below reason will be Actionable to STC in New current status column AG and Actionable to (AH column ) should be STC

( PO NA - Need Amendment - Vendor GST Mismatch / Circuit id not found - PO/GPS not found in LM / PO not available / PO NA - Need Amendment / PO Quantity Errors - Need WON /Total -ve & Need to adjust with Next Qtr /PO Quantity Errors - Budget issue / LM - Need Declaration for SEZ / To be Trashed - Service Tax debit notes / Need to check resolution / To be Trashed - Vendor Confirmation attached / LM- Decomissioned-Need ETC confirmation / PO Quantity Errors - NLDT / To be Trashed - Belongs to TEIL / PO Quantity Errors - PO end date / ISU Head approval needed / PO NA - Requires Reapproval / Circuit id not found - GPS/PO not given/wrong / Circuit id not found / Other Reasons - Internal clarification / LM- Circuit details mismatch / PO NA - 2 PO's have different OU - finance not accepting / Circuit id not found - COF shared by Vendor / To be Trashed - Paid from VMS / LM- Under Imp-New / CCA - Under Discussion / PO Quantity to be received / LM- Decomissioned-Cease date missing / Other-Invoice/CN pending in discrepancy /TCS Foundation - Invoice Discrepancy / LM- Under Imp-Revised/PO date mismatch)


2ND FILTER : Below reason will be Actionable to Vendor in New current status column AG and Actionable to (AH column ) should be Vendor ( Address Mismatch / Total payable is zero - need cancellation /GST - Multiple issues / Credit after termination / ARC Mismatch / Double billing - need cancellation / SPLIT needed for each Separate circuit / Need credit reason / Multiple issues but no GST issues / Billing prior to commissioning / Period incorrect/missing / SPLIT needed for End A & B / Wrong billing / Other-Invoice/CN pending in discrepancy
/ GST - Tax exemption / GST - TCS GSTN incorrect/missing / Billing after termination - need cancellation / SPLIT needed for OTC charges / Credit for overlapping / GST - Vendor GSTN incorrect/missing / GST - HSN/SAC incorrect/missing / GST - Tax should be charge / Invoice Discrepancy / Invoice to be cancelled / Tax exemption / Double billing / GST - Incorrect IGST/CGST-SGST / Cost clarification required / SPLIT invoice is needed for Separate circuit / GST - Wrong Tax% / No need for CN - need cancellation / Other Reasons / Billing after termination / SPLIT invoice is needed for TAX charges / GST + Other discrepancy / Need revised submission with current Invoice date / Circuit id not found - GPS/PO not given/wrong
/ Authorized Signature required / Incorrect Tax / OTC Mismatch / Need current PO reference in Invoice copy / To be Trashed - Service Tax debit notes / Credit for ETC charges / GST - Need Vendor GST as per PO / Need Missing invoice )

Sub Macro1()
'
' Macro1 Macro
'

'

    Cells.Select
    Selection.RowHeight = 18.75

    Range("AT3:BE3").EntireColumn.Delete
    Range("AN3:AR3").EntireColumn.Delete
    Range("AG3:AK3").EntireColumn.Delete
    Range("AC3:AE3").EntireColumn.Delete
    Range("K3").EntireColumn.Insert
    Range("K3").Value = "ShortName"
    Range("P3").EntireColumn.Insert
    Range("P3").Value = "Aging"
    Range("Q3").EntireColumn.Insert
    Range("Q3").Value = "Due/NonDue"
    Range("AG3").EntireColumn.Insert
    Range("AG3").Value = "New Current Status"
    Range("AK3").EntireColumn.Insert
    Range("AK3").Value = "Old Status"

       Worksheets("sheet1").Range("T:V").Select
    Selection.Replace What:="/", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=",", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=".", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="/", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

     Worksheets("sheet1").Range("X4 : X1048576").Select
     Selection.Replace What:="!", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="@", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="#", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False


      End Sub

 

[Marc L]

Hi, as a wild cross posting, see a specific explanation like this forum rules below :​

A message to forum cross posters

Do you know why you're here? If you have been pointed to this page, it is not because someone is mad at you, but rather because we want to curb your posting habits before people start ignoring your posts. Online … Continue reading →

www.excelguru.ca

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org