Posts by steveorg

    Re: UDF CONCATENATE with Separator - Empty Cells return "0" Result

    I've removed more unnecessary code. Now there is no point to the part of the IF statement that is before ELSE, however I can't figure out how to negate the logic in the IF. Everything that I've tried has yielded bad results. Anybody have thoughts of how to fix it and get rid of the unnecessary ELSE?

    Re: UDF CONCATENATE with Separator - Empty Cells return "0" Result

    After I first developed the UDF, I added the option to include empty cells or cells with a value of 0 in the concatenation. Accordingly, there are two new arguments in the function, which are explained in the code comments.

    EgoProwler: "For Each cell' defines the length of the loop. If I didn't understand your question properly, I'm not sure it still applies since the logic is a little different.

    mikerickson: Is there still a purpose to CStr?

    Re: Set Print Range For Last Row Beforeprint

    Thanks PCI.

    After further testing, it seems that the before print event is not executing. All three of the above macros work perfectly in a module. I then tested an extremely simple macro that also works in a module but not as a worksheet before print macro.

    Does anyone have any ideas on why the before print event would not execute?

    I'd like to set the print range based on the last row with text in specific columns. I found a couple of macros in this forum to adapt, but neither are working. Extra rows, which only contain conditional formatting, and other excluded rows and columns still print. Any suggestions on fixing either of these macros or another approach would be highly appreciated.

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
        Dim LastRow As Long
        LastRow = ActiveSheet.Columns("A:F").Find(What:="*" _
        , After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        ActiveSheet.PageSetup.PrintArea = "$A$2:$F$" & LastRow
    End Sub


    I've been trying to modify a UDF that I built a while back with the help of this board. The function concatenates cells within a range and places a separator between each of the values. The modification prevents the separator from being inserted between cells without a value. The function is almost perfect with this code:

    The only issue is that it usually inserts a separator at the front of the string. I added a test for using the separator the first time (additional code is bold). The code doesn't work. Instead of removing the first occurence of the separator, it removes all occurences.

    Could someone point out the error of my ways? Thanks.


    Re: Lookups & Lists

    I'm afraid that the table with the pricing is confusing. Your first 2 columns have widgets and colors implying a single price for each combination. However, there are multiple pricing columns including widget1pricing, widget2pricing and widget3pricing. What would the basis be for a widget 4 blue price?

    I suspect that the the table should be structured with column headings for the colors. If that's the case, the revised spreadsheet should work for you. The formula in C3 is =INDEX(PriceChart,MATCH(A3,widget,0),MATCH(B3,color,0)). Notice that the ranges of widget and color are redifined to extend to the first row and column of the table (PriceChart).

    If this isn't what you want, please explain how a price would be determined from the price table.


    Re: Hide Pivot Table Output

    I can't reproduce your problem, but it is possible that the complexity of your spreadsheet creates issues that my simple test doesn't have. I can suggest two workarounds. Set a print area for the report or place the pivot table in a different workbook.


    Re: Lookups & Lists

    By asking for an example I think cbanks was asking you to upload a sample workbook. Your explanation isn't clear because it has multiple values in single cells. A sample would be easier to understand.


    Re: Matching Nicknames To Real Names

    I figured out shg's approach and made a few corrections that gets it working. Creating an extra record for given names as a nickname was a great idea.

    Here's the changes that I made:
    -Changed the range Nickname to Nicknames!$B:$B
    -Created a range Givenname at Nicknames!$A:$A
    -Changed the formula in col C on List 1 to:
    = IF( ISNA( MATCH( A3, Nickname, 0 )), A3,INDEX( Givenname, MATCH(A3,Nickname,0) ) )
    -Used a similar formula on List 2

    This way all nicknames and names are mapped to a Givenname for matching. This now leads to the next issue. Often a nickname can map to more than one given name. For instance Bobbie is a common nickname for both Robert and Barbara. Any ideas on how to match these?

    Also, I don't see how to get rid of col a in list 2. All suggestions would be appreciated.


    Re: Matching Nicknames To Real Names

    This is great! Thanks for your help.

    On the sample, it misses matches where the list with the nicknames is reversed. Lines 14 and 32 are examples. Based on your example, I can probably figure that out when my head is a little clearer.

    The more comprehensive and cleaner solution that you mention would be highly appreciated. The full nickname database has over 1,000 records and includes four variations for Patrick.

    Thanks again,

    I have two lists of names and I need to identify the people that appear on both lists. Often it is difficult to match the common records because a nickname may be used for either of the first names and not the other. I've collated an extensive list of nicknames to help match the records, but I'm stumped on how to use it. I have a feeling that there is a sumproduct formula in my future. I attached a samples workbook. As always, all help is appreciated.


    Re: Counting Blank Cells Based On Two Criteria

    This is a job for SUMIF. Put the following in D10 and then copy down 1 and across:

    It may be worth noting that your formulas will always produce an empty cell if the equipment type is B.

    I hope this helps.


    Re: Copy Worksheets To New Workbook

    This should work. I added code to rename the masters so that they are more easily distinguishable in the new workbook.