Posts by BorneoHornbill

    Hello there the helpful people,

    I press Shift and click on the selected sheet tabs, goto Edit>Move or Copy Sheets... the dialog box allows move or copy to a new workbook or to an open workbook. Macro doesn't record syntax for the Dialog box. This build-in function also doesnt allow me to selectively choose the worksheets in the active workbook.

    I am looking for this code or a sample dialog box as I need to incorporate it as a part of my task automation macro. I would like a dialog box that allows eg. listbox to choose the individual sheets, then with option to Copy only (collectively Copy or Move are also fine) the selected sheets to a new Workbook or an already open Workbook. I am using Excel 2003. Any help is greatly appreciate. Thanks a bunch..

    The helpful VBA experts,

    I came across this sample workbook from Xlite. I tried to modify the Range to be used in my work so that it will copy and paste only to column A:F, starting from row 2. However, I'm not successful. Can someone please help me? Appreciate and many Thanks in advance.

    ActiveSheet.Range("A2", Range("A65536").End(xlUp)).EntireRow.Copy Sheets("MasterSh").Range("A65536").End(xlUp).Offset(1, 0)

    Hi Excel expert,

    if I use the following formulae in A1 and fill it down, I get the serialization 1,2,3....etc.


    But if I begin formulae at A11 and fill down, I get 11,12,13.....etc. Now I type 1 in A11, how do I modify the formula so that A12 will be 2, A13 will be 3, A14 will be 4 and so on ?

    Greatly appreciate any help. Thanks very much in advance.

    Hi there nice people,

    In my column of text strings, I have a multiple format of strings make-up. Below is just one of them I have to check that the first 6 are digits from 0 to 9, and it is followed by a hypen. If condition is true, the first 6 digits is the output (ie. 345678).



    However, if I were to continue doing this for other strings, I would soon run out of characters limit that is allowed in a cell. I wonder whether a formulae such as below is valid? Any suggestion or help for a shorter formula is very much appreciated.


    Re: Check Cells Value Before Macro Run

    Norie, sorry about my typo error that has caused so much confusion. I have changed it. Can you give me a hand please? Thanks again[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi VB experts,
    I gave it a shot but my code doesn't work. It bugs at the first End If. Can someone tell me what's wrong in it? or is there a shorter way to code it?

    Thanks a lot.

    Hello VBA experts,

    I have completed a formulation and included in a macro but I dont know about coding to check for specific cell string values before macro will take off. I want macro to check that if specific cell text are not matched, use MsgBox to display the wrong versus correct cell string text, then vbOK to exit macro. Also, if Range(B2:G2,J2) are completely blank, use MsgBox to display "missing data", then vbOK to exit macro.

    Apprciate very much if some VB experts dont mind help me with a code such that it will check for :

    cell B =REQ
    cell C =SS
    cell D =Current Stock
    cell E =PO
    cell F =Sales Order
    cell G =In-coming
    cell L =unit cost

    If these cell texts (U or L case will do) are in their specific cells, macro will proceed....

    If either one or some of these cell texts are not matched, display MsgBox of the wrong and correct cell texts. Then button OK to stop macro run.

    If Range(B2:G2,J2) are completely blank, use MsgBox to display "missing data", then vbOK to exit macro.

    a million hearfelt THANK-YOU if someone can kindly please assist me on this. Thankssss so much!

    I used Match array to count the frequency of occurences (ie. string begins ASC) with but to no avail. Can someone help me with a Match or Frequency function how to do this? Aprreciate very much any help offered.


    Column A

    Re: Multiply Corresponding Numbers & Return Sum of Those

    I can't tell how much I owe you guys ;-)) I summed it wrongly, yours are correct!! You have made my weeks to be much alive again. Thanks a milion times![hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi, just to share a summary formula I managed to put it in a cell. It wont happen without all your great helps!!!

    ="PR$ to convert: "&TEXT(SUMPRODUCT(A2:A48,G2:G48),"$,#,###")&CHAR(10)&"Total OH_excess: "&TEXT(SUM(IF(LEFT(G2:G48,9)="OH_excess",MID(G2:G48,FIND(" ",G2:G48),255)*A2:A48)),"$#,###")&CHAR(10)&"Total PO_excess: "&TEXT(SUM(IF(LEFT(G2:G48,9)="PO_excess",MID(G2:G48,FIND(" ",G2:G48),255)*A2:A48)),"$#,###")&CHAR(10)&"Total IT_excess: "&TEXT(SUM(IF(LEFT(G2:G48,9)="IT_excess",MID(G2:G48,FIND(" ",G2:G48),255)*A2:A48)),"$#,###")

    Hello Excel experts,
    I managed to put in a long formula in col G for multiple criteria in my worksheet. I am still puzzled as to how do I caculate the separate two Total Sums of these below:

    1) if values in col G is numeric, using value in col G X unit price (answer is 124955.54)
    2) if strings in col G start with text "OH_excess", retrieve its number, then X unit price (answer is 209690.95)

    Appreciate if someone could kindly help me with a sumproduct or sumif worksheet function if it is appropriate. Thanks so much in advance.

    hello VB experts,

    I have weekly meeting on inventory status with internal customers, with more than 20 columns & rows in Excel spreadsheet. Sometimes it was difficult to focus their attention as people having questions on different Part Number jump from one row to another. I am looking for a code or AddIn where the active row (the row where my cursor's position is at) will be highlighted each time I move the cursor. I have tried the popular "HiLite.xla" and the recent "RowLiner.xla" but not what I want. Greatly appreciate if anyone can help me on this.

    Thanks very much in advance.

    Hello Excel experts,

    I want to use Sumproduct function to sum up the values that belong only to Product "PXT" and "PCT". I enter it as array but my formulae doesnt work. can someone give me a hand please? Thanks very much. Here is my formulae:


    A B C D
    1 Value Qty Product
    2 299.94 4 PXT
    3 186.5 3 PXT
    4 711.07 1 PCA
    5 561.86 2 PCT
    6 608.96 1 PXT
    7 520.02 6 PCT
    8 427.68 2 PCA
    9 397.34 1 PCA
    10 387.66 4 PCT

    Re: For.. Next Looping

    Derk, can't tell you how much I appreciated it... now I understand it is "array inside a single variant" and also the purpose of LBound & UBound. It brightens my future in the interesting road of VB ahead. Thanks a million!!!

    By the way, my job handles tens of thousands of spares parts everyday on stocking and business decisions. I consider myself as an advanced user in Excel Level but as there are too many variables to be considered in the stocking analysis and most of the times the data spans up to 200+ columns. Clearly, Excel VB automation is the ultimate answer. Besides the VBA/VBA useforms Outline in the half-price Excel course, does Ozigrid publish any other better VBA books suitable for my work as in my case? or what's your recommendation?

    Hello VB experts,

    I'm using Excel 2003. In my worksheet, column A has all the Part-Numbers preceded with the character " ' ", eg. '55233, '55233B, '2221987, '220032450000, etc. Using RIGHT() formula to remove the front character don't quite help. I found a close answer as below from my old stack of copies of printed VB codes but still it doesnt work.

    Can someone VB expert please tell me which syntax is wrong?
    Also, can someone kindly tell what does the 2 statements "For i =LBound(varData) To UBound(varData)" and "varData(i, 1) = ....." mean as I cant seem to find anything like this in all my VB textbooks ?

    Thanks a lot in advance!