• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need VBA Code for Specific data

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 )

Code:
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
 

Attachments

Last edited:

Marc L

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