Posts by steveorg

I'm hoping to create this chart from the data below it:

If that is not possible, how about either of the following?

Any guidance would be appreciated.

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?

Function ConcatenateRange(Parts As Range, Separator As String, Blank As Integer, Zero As Integer)
    ' Build a single string from a passed range with a passed separator
    ' placed between each value. The separtaor can be more than 1 character
    ' If Blank = 0, cells = "" are ignored, else they are concatenated
    ' If Zero = 0, cells = 0 and <> "" are ignored, else they are concatenated
    Dim strAll As String, cel As Range, celCount As Integer
    strAll = ""
    celCount = 0
    For Each cel In Parts.Cells
        If (Blank = 0 And cel.Value = "") Or (Zero = 0 And cel.Value = 0 And Len(cel) > 0) Then
        Else
            celCount = celCount + 1
            strAll = strAll & Separator & cel.Value
        ' Removes separator placed before first concatenated cell
            If celCount = 1 Then
                strAll = Right(strAll, Len(strAll) - Len(Separator))
            End If
        End If
        Next cel
    ConcatenateRange = strAll
End Function

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?

Function ConcatenateRange(Parts As Range, Separator As String, Blank As Integer, Zero As Integer)
    ' Build a single string from a passed range with a passed separator
    ' placed between each value. The separtaor can be more than 1 character
    ' If Blank = 0, cells = "" are ignored, else they concatenated
    ' If Zero = 0, cells = 0 and <> "" are ignored, else they concatenated
    Dim strAll As String, cel As Range, celCount As Integer
    strAll = ""
    celCount = 0
    For Each cel In Parts.Cells
        If (Blank = 0 And cel.Value = "") Or (Zero = 0 And cel.Value = 0 And Len(cel) > 0) Then
            If celCount = 1 Then
                celCount = celCount + 1
            End If
        Else
            celCount = celCount + 1
            strAll = strAll & Separator & cel.Value
        ' Removes separator placed before first concatenated cell
            If celCount = 1 Then
                strAll = Right(strAll, Len(strAll) - Len(Separator))
            End If
        End If
        Next cel
    ConcatenateRange = strAll
End Function

Re: Convert Text To Number

Here's an alternative that can be done by formula only. If the value is in cell A1, use this formula: =A1*1.

Re: Automatically Set Print Range For Last Row Before Printing

Dave - Once again you've come to the rescue. Thanks much.

Steve

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 MaxRow As Long, i As Integer
     
    MaxRow = 1
    For i = 1 To 6
         Cells(65536, i).End(xlUp).Select
        MaxRow = Application.WorksheetFunction.Max(MaxRow, ActiveCell.Row)
    Next i
     
    ActiveSheet.PageSetup.PrintArea = "$A$2:$F$" & MaxRow
End Sub

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

Thanks,
Steve

I'm using Worksheet_BeforePrint to change a formula in a specific cell before printing. I also want to change it back after printing. Is there any way to do this?

Steve

Re: Concatenate Range With Separator

Works like a charm! Brian - thanks for for the help.

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:

Function ConcatenateRange(Parts As Range, Separator As String)
' Build a single string from a passed range with a
' passed separator between each value
    Dim strTemp, sepTemp As String
    Dim cel As Range
    Dim cnt As Integer
    strTemp = ""
    For Each cel In Parts.Cells
        If cel.Value = "" Or cel.Value = 0 Then
            sepTemp = ""
        Else
            sepTemp = Separator
        End If
        strTemp = strTemp & sepTemp & cel.Value
    Next cel
    ConcatenateRange = strTemp
End Function

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.

Function ConcatenateRange(Parts As Range, Separator As String)
' Build a single string from a passed range with a
' passed separator between each value
    Dim strTemp, sepTemp As String
    Dim cel As Range
    Dim cnt, [B]firstSep[/B] As Integer
    [B]firstSep = 0[/B]
    strTemp = ""
    For Each cel In Parts.Cells
        If cel.Value = "" Or cel.Value = 0 [B]Or firstSep = 0 [/B]Then
            sepTemp = ""
        Else
            sepTemp = Separator
            [B]firstSep = 1[/B]
        End If
        strTemp = strTemp & sepTemp & cel.Value
    Next cel
    ConcatenateRange = strTemp
End Function

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

Steve

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.

Steve

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.

Steve

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.

Steve

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.

Steve

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,
Steve

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.

Steve

Re: Import Csv As Text Not General

How about changing the file extension to .txt before import? That should give you the wizard.

Steve

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:
=SUMIF($B3:$B9,$B11,D3:D9)

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

I hope this helps.

Steve

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.

Sub check_month_end()
     
    Dim currentmonthend
    Dim previousmonthend
    Dim newbook
     
    currentmonthend = Application.GetOpenFilename("Excel Files (*.xls),*.xls", , "Select CURRENT Month End Report?")
     
    previousmonthend = Application.GetOpenFilename("Excel Files (*.xls),*.xls", , "Select PREVIOUS Month End Report?")
     
    Set newbook = Workbooks.Add
    With newbook
        .Title = "MoEnd Compare"
        .Subject = "Compare"
        .SaveAs Filename:="MoEnd Compare.xls"
    End With
     
    Workbooks.Open currentmonthend
     
     'copy and paste first worksheet
    ActiveWorkbook.Activate
    Sheets("Master").Copy Before:=Workbooks("MoEnd Compare.xls").Sheets(1)
    Workbooks("MoEnd Compare.xls").Sheets("master").Name = "Current Master"
     
    Workbooks.Open previousmonthend
     
     'copy and paste second worksheet
    ActiveWorkbook.Activate
    Sheets("Master").Copy Before:=Workbooks("MoEnd Compare.xls").Sheets(1)
    Workbooks("MoEnd Compare.xls").Sheets("master").Name = "Previous Master"
     
End Sub

Steve

Re: Mapping Data: Combining Two Worksheets Into A New One

Robert B is close, but copying is not required. The trick is to create a lookup key in the first column of the source data sheets that concatenates the common data. Then vlookup can be used in the third sheet to create the desired results. The solution is attached.

Steve