Posts by yjoshi

  • Finding consecutive letters in a column

    Hi,


    I think the use of If is not really required,


    What you can do is.... multiple the two series of true & false and you will get the numbers...


    "=SUMPRODUCT(ISNUMBER(FIND(B2,LOWER(A2:A5),1))*ISNUMBER(FIND(C2,A2:A5,1)))"


    Instead of


    "=SUMPRODUCT(IF(ISNUMBER(FIND(B2,LOWER(A2:A5),1)),1,0),IF(ISNUMBER(FIND(C2,A2:A5,1)),1,0))"


    This will certainly reduce the code, and also use internal features of excel (i.e. TRUE = 1 & False = 0) and their multipliaction...


    I tried it in your sheet and it worked well.


    What do you think???

  • Change page field in multiple pivot tables on one sheet

    Interesting question ...


    I will do it this way, instead of changing it in page, I change the value in a cell, say E1


    and then change all the page values.... by this code....

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Target, Range("E1")).Address = Range("E1").Address Then
For Each pvt In ActiveSheet.PivotTables
        pvt.PivotFields("City").CurrentPage = Range("E1").Value
Next
End If
End Sub


    It is working for me... .let me know if it does not.... I will attach a file.


    HTH

  • VB stopwatch with form

    Your query was not irrelevant :)


    Put this code so that when you start the form for the first time, even if the caption is
    something different, it will be set right.

    Code
    Private Sub UserForm_Initialize()
    CommandButton1.Caption = "Start"
End Sub

  • Worksheet Event

    Following code works with MS Outlook.


    Hope this helps ... Interesting requirement BTW ...


    Also if A1 and A2 are calculated fields, please put proper range you want to compare, or if you want to alert / send mail every time there is a change, you may choose to remove the criteria.


  • Finding consecutive letters in a column

    Hi,


    You can do it in two ways. Simple formula and array formula.


    If you want a simple formula, : enter following in any cell other than A1 to A9
    =SUMPRODUCT((LEFT(UPPER(A1:A9),3)="APP")*1)


    What it does is....
    1. Takes values in A1 to A9 and makes it CAPITALIZED
    2. Takes first 3 chars and compates with APPS
    3. if this is true, it generates an array of TRUE & FALSE
    4. TRUE = 1 and FALSE = 0
    5. when you multiply it by one, it creates series of 1s and 0s
    6. when you do a sumproduct, it totals all 1s and gives you the result.


    IF you want to use Array Formula for this : use following
    {=SUM((LEFT(UPPER(A1:A9),3)="APP")*1)}


    Note that, you have to enter =SUM((LEFT(UPPER(A1:A9),3)="APP")*1) and press
    "Ctrl + Shift +Enter" to get the { } s and not enter manually.


    You can see following links for getting more info on WHAT CAN BE ACHIEVED WITH ARRAY FORMULAE :: enjoy
    http://www.emailoffice.com/excel/arrays-bobumlas.html


    Also see array formulas link on OZGRID.
    http://www.ozgrid.com/Excel/arrays.htm

  • Control Toolbox Buttons Move when Printing

    I have done this ample time, but never faced an issue.


    Can you try this?


    Create a new file, put many controls on that page then try to print prieview....


    If the problem persists, then this can be a BUG with Excel 2002 ;)
    ... otherwise looks like a file corruption.


    Please provide an update..

  • Data Validation

    Hi Admin, (Quite sure this is not Dave's Update)


    The issue is, Daniel wants to have the list and also avoid duplicates.
    So just avoiding duplicates does not solve the issue.


    Just a clarification :)

  • Data Validation

    Here is how you can start....


    This is not EXACTLY what you expect, but can help you.
    1. Create a List of numbers a person can use
    2. Besides the list create another list, which will exclude those items which
    are already selected.
    You can do this by formula :
    =IF(ISNA(MATCH(A1,Sheet1!A:A,FALSE)),A1,"")
    Where, A1 is from the original list, Sheet1!A:A is the range where you want
    to have the validation, i.e. entry row....
    what it means is, if this number is already used, it shows "" otherwise the
    number.
    3. Create a named range for this new range, say unused_nums
    4. Now create the validation with this named range.


    You will see blanks in place of numbers that are already used.


    I know this is not the best solution, if you have many numbers that can be blank but it does work.


    HTH

  • VB stopwatch with form

    I will suggest a simpler solution


    Just use one button with Caption "Start" and in the run time change it
    to "Stop" once it is clicked.


    If the caption is Start when clicked, it will enter date and time in Cols A & B respectively
    If the caption is Stop when clicked, it will enter date and time in Cols C & D respectively, in the same row it started, thats why i have used (1,1) in first part and (0,3) in second part


    Let me know if this does not solve your problem



  • Capture Conditional Format Item

    Hi Will,


    You can access properties of a particular condition using

    Code
    ? ActiveCell.FormatConditions.Item(1).Interior.ColorIndex


    But the question that WHICH item is currently activated is still not known...


    Looks to be an interesting problem, but I think this can be a good starting point

  • VB stopwatch with form

    I will suggest following code

    Code
    Private Sub CommandButton1_Click()
Range("A65536").End(xlUp).Offset(1, 0).Value = Now
End Sub


Private Sub CommandButton2_Click()
Range("A65536").End(xlUp).Offset(0, 1).Value = Now
End Sub


    The Reason for replacing now with Date & time is that its combination
    of both and you can take out all the information you need with this.

  • Worksheet switching

    Following code can help you to get a complete list of all the links on a particular sheets shown on a new sheet
    You can then go through the list and determine if there is any unwanted link.


  • parsing worksheet names

    Here goes the code

    Code
    Sub page_of()
Dim sht As Worksheet
For Each sht In Sheets
    sht.Range("A20").Value = "Page " & sht.Index & " of " & Sheets.Count
Next
End Sub


    If you want this for printing purpose... i will suggest another way to do this.
    On each sheet setup appropriate header or footer (Page # of #). then select all the sheets you want to print and see print priview. You will see that appropriate page numbers are set and works fine even if one sheet has more than one pages.


    Hope this Helps

  • Copy / Paste problem

    Do you want Paste Transpose?


    Code
    Sub CopyTest1()
   Range("A1:H18").Copy
   Range("A65536").End(xlUp).Offset(7, 0).PasteSpecial Transpose:=True
   Range("A65536").End(xlUp).Select
End Sub


    My sincere apologies if i am still unable to understand.... If you can describe your problem better, it can help