troubleshooting Question

VBA - Add Formula, Copy Down To Last Row & Add Formatting

Avatar of Geekamo
GeekamoFlag for United States of America asked on
Microsoft Excel
3 Comments1 Solution959 ViewsLast Modified:
Hello Experts,

I am currently using this code,...

Sub CreateLookUpTable()

'Create New WorkSheet

    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "LookUpTable"

'Create Lookup Values - Transation Codes

    Sheets("LookUpTable").Range("A1") = "10"
    Sheets("LookUpTable").Range("A2") = "20"
    Sheets("LookUpTable").Range("A3") = "25"
    Sheets("LookUpTable").Range("A4") = "30"
    Sheets("LookUpTable").Range("A5") = "40"
    Sheets("LookUpTable").Range("A6") = "41"
    Sheets("LookUpTable").Range("A7") = "43"
    Sheets("LookUpTable").Range("A8") = "44"
    Sheets("LookUpTable").Range("A9") = "45"
    Sheets("LookUpTable").Range("A10") = "46"
    Sheets("LookUpTable").Range("A11") = "50"
    Sheets("LookUpTable").Range("A12") = "60"
    Sheets("LookUpTable").Range("A13") = "70"
    Sheets("LookUpTable").Range("A14") = "75"
    Sheets("LookUpTable").Range("A15") = "80"
    Sheets("LookUpTable").Range("A16") = "90"
    Sheets("LookUpTable").Range("A17") = "100"
    Sheets("LookUpTable").Range("A18") = "110"
    Sheets("LookUpTable").Range("A19") = "120"
    Sheets("LookUpTable").Range("A20") = "125"
    Sheets("LookUpTable").Range("A21") = "130"
    Sheets("LookUpTable").Range("A22") = "140"
    Sheets("LookUpTable").Range("A23") = "150"
    Sheets("LookUpTable").Range("A24") = "160"
    Sheets("LookUpTable").Range("A25") = "170"
    Sheets("LookUpTable").Range("A26") = "180"
    Sheets("LookUpTable").Range("A27") = "190"
    Sheets("LookUpTable").Range("A28") = "200"
    Sheets("LookUpTable").Range("A29") = "210"
    Sheets("LookUpTable").Range("A30") = "220"
    Sheets("LookUpTable").Range("A31") = "230"
    Sheets("LookUpTable").Range("A32") = "240"

'Create Lookup Values - Transation Names

    Sheets("LookUpTable").Range("B1") = "New Distributor"
    Sheets("LookUpTable").Range("B2") = "Customer Maintenance"
    Sheets("LookUpTable").Range("B3") = "Credit"
    Sheets("LookUpTable").Range("B4") = "Sales Order Entry"
    Sheets("LookUpTable").Range("B5") = "Sales Order Change"
    Sheets("LookUpTable").Range("B6") = "Expedite"
    Sheets("LookUpTable").Range("B7") = "Check Order Status"
    Sheets("LookUpTable").Range("B8") = "Tie in Attachment"
    Sheets("LookUpTable").Range("B9") = "Back Orders"
    Sheets("LookUpTable").Range("B10") = "Review EDI Order"
    Sheets("LookUpTable").Range("B11") = "POD"
    Sheets("LookUpTable").Range("B12") = "POD Receipt"
    Sheets("LookUpTable").Range("B13") = "Stock Check"
    Sheets("LookUpTable").Range("B14") = "MRP Display"
    Sheets("LookUpTable").Range("B15") = "Price Inquiry"
    Sheets("LookUpTable").Range("B16") = "Print Acknowledgement"
    Sheets("LookUpTable").Range("B17") = "Print Invoice"
    Sheets("LookUpTable").Range("B18") = "Custom Quote Entry"
    Sheets("LookUpTable").Range("B19") = "Custom Quote Change"
    Sheets("LookUpTable").Range("B20") = "Quote Display"
    Sheets("LookUpTable").Range("B21") = "Convert Quote"
    Sheets("LookUpTable").Range("B22") = "Print Quote"
    Sheets("LookUpTable").Range("B23") = "Inventory Transaction"
    Sheets("LookUpTable").Range("B24") = "Inventory Location"
    Sheets("LookUpTable").Range("B25") = "CR/DB Memo Entry"
    Sheets("LookUpTable").Range("B26") = "CR/DB Memo Change"
    Sheets("LookUpTable").Range("B27") = "RA Entry"
    Sheets("LookUpTable").Range("B28") = "RA Change"
    Sheets("LookUpTable").Range("B29") = "RA Inquiry"
    Sheets("LookUpTable").Range("B30") = "RA Acknowledgement"
    Sheets("LookUpTable").Range("B31") = "General"
    Sheets("LookUpTable").Range("B32") = "New Product"
    
'Auto Fit Columns

    Sheets("LookUpTable").Columns("A:B").EntireColumn.AutoFit
    
'Name Ranges

    Sheets("LookUpTable").Columns("A:A").Name = "TransactionCodes"
    Sheets("LookUpTable").Columns("B:B").Name = "TransactionNames"

End Sub

Ideally, I would like this new code to be incorporated into the one I am already using.

In my workbook, I have a worksheet named: "TINAO-REQUESTS". I would like a code to do the following steps.

1.)  Cell I1 = "Duration"
2.)  Cell I2 = "=G2-F2"
3.)  Copy this formula ALL THE WAY DOWN TO LAST RECORD (which will always be different) - The last value in column H, would be considered the last row.
4.)  Apply custom format: "[h]:mm" to column I

Technically, based on the work I've already done (see above code) I know how to accomplish step 1, to set the cell value to "Duration".  But honestly, other then that - I have no idea how to finish the rest.

Any ideas?

Thanks,
Geekamo
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros