Posts by sucheng

    Hello, I have a few VBA code attached to a excel file. I would like to know is that any VBA code that I need to add so that each time when I opened the file, the Custom Menu Item will automatically appear on the toolbars ?

    The Custom Menu Item will disappear when the file is closed.

    I have uploaded the data as attached. Look like it is unlikely to use Text to Column to convert it into the excel format because it is based on column basis.
    Unless, there is a special command that would instruct Text to Column to go for Row by Row basis.

    Do you have any idea,yjoshi ?

    I have the problem when I downloaded the data from my company accounting system. For some of the data, I can easily use text to column to convert it into Excel format. However, some of the data it look like totally impossible as below.

    Sales 125300 5241.41 6352.00
    Type:B Quantity: 120 Location: Australia

    Therefore, I would like to know is that a VBA function which will look into each row and then perform the text to column function to convert the data into the readable format.

    Hello, is that anyone have any idea on how to use VBA code to represent a normal excel function.

    For example, I have a Excel file with three Worksheets. For each worksheet, there is a data (numeric) being key-in and SUM and Offset functions being used to sum up the data. However, I just wondering is that a way to convert the name of the function, e,g offset, to YTD.


    Thank for your prompt reply.

    What if the amount is assigned more than one expenses ? For example, USD100 is allocated to Meal (USD60) and Hotel (USD40)

    Hello, I have the following problem. Appreciate if someone can help me to figure out the short cut.

    For example, I have a data from Column A until AA. Column A is the input for date and Coumn B is the amount (USD), whereas from Column C to Column AA is the expenses items. Therefore, after I keyed in the Date and Amount, I need to spend a lot of time to allocate the amount to the right expenses category. Any short cut ?


    Is that anyone can help me to figure out what is the best way for Excel to prompt out a message when the total input is not equal to each other

    For example, I will key-in data under column A & B. At the end of the result, total under column A & B should be same, with assumption all the inputs are being key-in correctly.Actually, this is easy to monitor and check if I have only one worksheet. The problem is I have at least 30 over worksheets. Each worksheet will link to the summary worksheet.
    Therefore, I would like to have a VBA Code which in the summary worksheet which will automatically prompt out a message when the input for column A & B is the equal to each other.:flame:

    Hello, I have the problem in formula when I need to do a comparison for two figures. For example if A1 = 1000, B1 = 9000. Then, in C1 would show 11%(Formula is (A1-B1)/B1) Assume that, A1 is stand for Sales 2002, whereas B1 is stand for Sales 2001. Everything will go fine if there was figures in A1 & B1. The problem will arise when B1 is zero, the comparison figures will return #DIV/0.

    I copied this Code from Flind. Is that anyone can show me what are the amendment need to be made to the Code. So that, the cell location will show next to the worksheet name.

    Sub ListWorksheets()
    Dim ws As Worksheet
    Dim vValueToFind As Variant
    Dim lRow As Long

    vValueToFind = 45
    For Each ws In ThisWorkbook.Worksheets
    If Not ws.Cells.Find(vValueToFind) Is Nothing Then
    Worksheets("ListOfValues").Range("A1").Offset(lRow, 0) = ws.Name
    lRow = lRow + 1
    End If
    End Sub

    I think you can try this.

    For example, cell A1 = GL and cell A2 = 200. In order to combine the data in A1 and A2 in cell A3. I think you can type =A1&A2 in A3.

    Hopefully this will help you