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 Comments2 Solutions959 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

Open in new window


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
ASKER CERTIFIED SOLUTION
Faustulus

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
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 2 Answers 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