Posts by Deathlace

  • Group Customers in One Cell - with there orders(multiple) in another cell same row.

    Re: Group Customers in One Cell - with there orders(multiple) in another cell same ro


    I ended up taking care of it this morning. I was missing the .xls to my workbook name, stupid little mistakes, grrrr.
    Here is the final code for that part with lastrow identification so it pastes on the next available line.


    Code
    Dim TransposeDataHold As Range
    Dim LastRow
    LastRow = Workbooks("TransposeDataHold.xls").Worksheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Row  'Find last row.
    Set TransposeDataHold = Workbooks("TransposeDataHold.xls").Worksheets("Sheet1").Range("A1:A" & LastRow)
    With TransposeDataHold.Resize(TransposeDataHold.Rows.Count, 3).Cells(TransposeDataHold.Resize(TransposeDataHold.Rows.Count, 3).Cells.Count).Offset(1, 1)
        .NumberFormat = "General"
        .Value = CStr(strCustomIDs)
        .Offset(, 1).Value = strMonth
        .Offset(, 2).Resize(1, 1 + UBound(Split(strOrderIDs, "|"))).Value = Split(strOrderIDs, "|")
    End With

  • Group Customers in One Cell - with there orders(multiple) in another cell same row.

    Re: Group Customers in One Cell - with there orders(multiple) in another cell same ro


    It works great, thank you.
    I went ahead and changed the range to ActiveWorkbook.ActiveSheet allowing me to place this in my personal.xls create a marco button and just roll through the worksheets.


    One other handy change I am trying to make is instead of pasting one row after the range, I am trying to get it to paste into an open workbook that is not active named TransposeDataHold, however I am getting script out of range. on the Set TransposeDataHold line.


    Code
    Dim TransposeDataHold As Range
    Set TransposeDataHold = Workbooks("TransposeDataHold").Worksheets("Sheet1").Range("A1")
    With TransposeDataHold.Resize(TransposeDataHold.Rows.Count, 3).Cells(TransposeDataHold.Resize(TransposeDataHold.Rows.Count, 3).Cells.Count).Offset(1, -2)
        .NumberFormat = "General"
        .Value = CStr(strCustomIDs)
        .Offset(, 1).Value = strMonth
        .Offset(, 2).Resize(1, 1 + UBound(Split(strOrderIDs, "|"))).Value = Split(strOrderIDs, "|")
    End With

  • Group Customers in One Cell - with there orders(multiple) in another cell same row.

    I have an excel sheet that Lists CustID, orderID, and month. Like so:


    CustID | OrderID | Month
    -------------------------
    1234 | 98273| June
    1234 | 34847| June
    1234 | 23421| June
    4523 | 23423| June
    6526 | 39282| June
    6526 | 29327| June


    Here is now I need it reorganized for another application.


    CustIDs...........|Month|orderIDrange1.......|orderIDrange2|orderIDrange3|
    -----------------------------------------------------------------------
    1234,4523,6526|June..|98273,34847,23421|23423..........|39282,29327..|


    (Periods used to line up | as cell breaks.)


    So a couple of thanks, the month will always be the same.
    As you will see the customer ids are combined into one numerical order cell comma separating them. The order ids are then are placed in one cell where the range number is the "place number" in the CustIDs cell. I need to be able to support up to 100 customer ids so 100 orderIDrangeX's, but will not necessarily use all 100.


    I am using Excel 2003, very comfortable with VB and MS Access if you find it easier to accomplish there. This is going into MS Access once its in this structure anyway.

  • Vlookup - two variables to populate one result.

    Re: Vlookup - two variables to populate one result.


    That works great, thank you. One other thing I forgot to mention is this sheet basically a template sheet that users will import a text file into. This text files generates the rows that populate sheet1 columns A thru H. The problem is I don't know how many rows there will be on a given import. So I dont know how far to "drag" down the formula in column I and leaving that up to the user is just out of the question. What sort of solution do I have for something like this?

  • Vlookup - two variables to populate one result.

    I am horrible at VLookup, I don't use it often enough or understand half of what I read about it but I have a feeling its needed in this scenario.


    Attached you will find my excel workbook example.
    Sheet1 has Column "i" that needs to be populated sheet"Feilds"column"C" based on the enteries in column "D" and "F".
    The key to what should populate is in worksheet "Feilds".


    Please add your help to my workbook, as I said I don't understand half of what I read on Vlookup when trying to figure it out myself so just posting a few code snippets to point me in the right direction won't work in this case. If you can explain your VBA code with comments a little that is great and will hopefully help me learn a little about Vlookup.



    I know I am basically asking you all to do this for me but I don't have time too muddle my way thru this part.


    This is being done in excel 2003, thanks you.
    (Yep I spelled it "Feilds" wrong in the workbook, I will fix that later)

  • vba Excel - Highlight row on new number.

    I have a numbers listed in column "B" starting on row "3". And the multiple listings of a number are always grouped together. That is static. What is not is the number and how many rows its on before it changes to a different number.


    I am trying to write some loop code to compare current cell value and off set of one cell above. If the values do not equal, it would highlight the row else/then move to the next until current cell value is empty. However I am getting no where on the code since I am horrible at navigating excel sheets in vba, sql tables are more my cup of tea.


    Attached is an excel workbook with an example end result.
    Works is being done in Excel 2003, thank you.

  • Automated - Add Comment If Criteria Met

    Re: Automated - Add Comment If Critera Met


    Thank you for the code - I have having trouble understanding it. Therefore I am unable to debug this error


    I am receiving a - "Script out of Range" on this code


    Code
    Set Master = ws1.Range("A1", ws1.Range("A65536").End(xlUp)). _
            Find(c, LookIn:=xlValue, MatchCase:=True, lookat:=xlWhole)

  • Automated - Add Comment If Criteria Met

    I have attached a work book that has 3 sheets.
    Basicly I have 2 columns of records on sheet 3 that may or may not match
    Sheet1 has one set of those records


    If on sheet3 the records in each row to not match I want a Comment added to that row on sheet one with the opposite value.


    I have make Comments to walk you thru want I would like on the workbook and have made and example comment of row3

  • Read text in column A and count entries in E and F

    In this workbook random names will be placed in "Colomn A" sometimes they will show up every month or in just one month. When place in "Column A" an "X" will be placed in either "Column E or F".


    I need under the "YTD" SHeet a summary for the year.
    I want it to list the name from Colomn A of month2month and count each X for that name thru-out the year.


    Keep in mind we can not use a static list of names.


    thankyou

  • Search workbook sheets, then return name associated with search entry

    Re: Search workbook sheets, then return name associated with search entry


    Thank you for noticing this.


    However because I am unable to change the layout of the pages I am unable to use your solutions providered.


    So we need to take into consideration the State and County while still having option to search with zip code also.


    Side Note: There will be no more then KS or MO for states.