Posts by BorneoHornbill

Re: Move Or Copy Sheets Dialog Box

Thanks so much. It will be enough for me to continue with customized Copy.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Thanks so much. It will be enough for me to continue with customized Copy/Move.

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..

Re: Append Multiple Worksheets To One Worksheet

It works just what I wanted. Greatly appreciate that Bryce

Re: Append Multiple Worksheets To One Worksheet

Thanks bryce but it is not what I want. The output should be as what is printed in RED. Can you help?

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)

Re: Serializing Start From numeric 1

it's amazing. Thanks very much.

Hi Excel expert,

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

=(INT((ROW()-1)/1)+1)*1

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.

Re: Text String Manipulation

Thanks so much for help

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).

345678-S

=IF(ISNUMBER(--(MID(A1,FIND("-",A1)-1,1))--(MID(A1,FIND("-",A1)-2,1))--(MID(A1,FIND("-",A1)-3,1))--(MID(A1,FIND("-",A1)-4,1))--(MID(A1,FIND("-",A1)-5,1))--(MID(A1,FIND("-",A1)-6,1))),LEFT(A1,FIND("-",A1)-1))

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.

=IF(ISNUMBER(--(LEFT(A1,1+1+1+1+1+1))),LEFT(A1,6))

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.

Option Explicit
Sub textIF()
If Range("C1:G1") = "" Then MsgBox "missing headers in row 1'"
If Range("C1").Value <> "SS" Then MsgBox "cell C1 is not SS"
If Range("D1").Value <> "Current Stock" Then MsgBox "cell D1 is not current stock"
If Range("E1").Value <> "PO" Then MsgBox "cell E1 is not PO"
If Range("F1").Value <> "Sales Order" Then MsgBox "cell F1 is not Sales Order"
If Range("G1").Value <> "In-Coming" Then MsgBox "cell G1 is not In-Coming"
If Range("J1").Value <> "unit cost" Then MsgBox "cell J1 is not Unit Cost"
If Range("C2:J2") = "" Then MsgBox "missing data under headers SS,Current Stock,PO,Sales Order,In-coming, unit cost"
Else
Exit Sub
End If
End If
End If
End If
End If
End If
End If
End Sub

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!

Re: Count Frequency In A Column

I totally overlooked the use of *. Thanks a lot!

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.

=MATCH(IF(LEFT(A1:A9,3)="ASC",LEFT(A1:A9,3)),A1:A9,0)

Column A
ASC_8731
UNI_ST9821
ASC_6735
ASC_431
UNI_ST1421
ASC_1731
UNI_ST821
ASC_731
UNI_ST2330

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.

Re: Sumproduct Of 2 Criteria In A Single Array

Thank you so much!! it is so simple yet I cant figure it out in a day... I gotta work harder on sumproduct and sumif

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:

=SUMPRODUCT((C2:C10="PXT")*(C2:C10="PCT"))*(A2:A10)

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!

Sub Remove_Char()
Dim wsSheet As Worksheet
Dim rngData As Range
Dim varData As Variant
Dim i As Long


Set wsSheet = ActiveSheet


With wsSheet
Set rngData = .Range(.Range("A2"), .Range("A65536").End(xlUp))
End With


vaData = rnData.Value


For i = LBound(varData) To UBound(varData)
varData(i, 1) = Right(varData(i, 1), varData(i, 1) - 1)
Next i


rngData = varData
End Sub