Posts by abba92

  • Opening, Temporarily Naming and Switching Between Workbooks With Varying Names

    Hi there. I'm trying to write a series of macros that refer to the macro document itself (Order Macro.xlsb) and then two other workbooks. These two other workbooks are browsed for and opened by the end user (paths and names will vary). The question is, how do I switch between these three workbooks when I only know the name of one? I've looked around and found what I think is a partial answer but I can't seem to switch between the workbooks. See attached.


    ANY information would be helpful. I'm stumped as to what I'm missing. I'm sure it's something basic and stupid but I'm learning VBA as I go so it's......challenging.


    Thank you!

  • V and H Lookups On Dynamic Data

    Hi. Going to do my best to state what I'm attempting to do. I have a spreadsheet with two tabs. On the 'Template' tab I have a series of "ranges" (they are not named) for VPNs and colors. Within each VPN/color range is a range of sizes - from one to fifty-two possibilities. On the second tab ("IIF"), I have a list of all the sizes/colors/VPNs. In column L on the second tab ("Notes to Allocator") I want to look up the VPN, color and size and return the recommended order quantity which is six rows below the size. For example, on the first row of the second tab, the result should be 21 as that VPN/Color/Size combination is recommending 21 pieces. Is there even a way to do this without a macro? The number of VPN/Color combinations can vary from one to infinity. Any help would be great.

  • Macro for Copying, Transposing Dynamic Non-Continuous Rows

    Hello -


    Attached is a workbook that has two tabs: Tab "Template" allows users to enter order information for shirts/blouses. They can enter as many as they need by copying/pasting a section.


    Highlighted in bright yellow are the styles, descriptions, colors and sizes.


    On the "Output" tab I need to list each style, its description, its color and each size. The catch: each size needs to be on a different row.


    Since the number of style and the number of sizes can change within each section, I'm unsure how to do this. I think VBA is the way to go but have no idea how to begin. Any help would be so appreciated!


    Thank you!

  • Looking Up Closest Match Between Two Columns and Within That Row

    I'm usually fairly decent when it comes to Excel formulae but this one has me stumped. I've been given a table (Columns K-P) and a "current value" and "percent off" (Columns R and S). Using this information, I have to find where the percent off (column S) falls in the table, check that row for what's closest to the current value (Column R) and return the header information (Column T).


    Is this even possible? Any help would be very appreciated.


    [ATTACH=CONFIG]70416[/ATTACH]

  • Lookup Multiple Criteria in a Table and Return Header

    I'm usually fairly decent when it comes to Excel formulae but this one has me stumped. I've been given a table (Columns K-P) and a "current value" and "percent off" (Columns R and S). Using this information, I have to find where the percent off (column S) falls in the table, check that row for what's closest to the current value (Column R) and return the header information (Column T).


    Is this even possible? Any help would be very appreciated.



    [ATTACH=CONFIG]70415[/ATTACH]

  • Entering a Formula via VBA for a Variable Range

    I have a formula [=SUMIFS(Worksheet!R2C6:R50000C6,Worksheet!R2C9:R50000C9,RC1,Worksheet!R2C4:R50000C4,R13C,Worksheet!R2C1:R50000C1,R2C4)] that I need to add to every cell within a variable range based on which columns are populated in row 13. The range always starts at G14 but it can go on to multiple columns and rows.


    I know I can find the last column using this:


    Dim LastCol As Long
    LastCol = Cells(13, Columns.Count).End(xlToLeft).Column


    And I can get the last row using this:


    Dim LastRow As Long
    LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row


    But what I can't figure out is how to get the formula into LastRow:LastCol range. Does that make sense?


    Thanks for any help!

  • Entering a Formula into Empty Cells in a Column Based On Another Column as Criteria

    Hi. I have a simple spreadsheet with data in columns A-G (see attached). Users will enter information in those columns regularly. Columns H-J are blank and I fill them in with a formula. Each column (H:J) gets a unique formula. What I want to do is fill in from the first blank row in column H with its formula (down to the end of Column A) and the same for columns I:J.


    I apologize if this doesn't make sense. On the attached file, the yellow highlighted columns are blank by default. After users enter A:G information, I want to run a macro that will auto-fill H-J with the appropriate formulas - but only to the point where Column A is blank.


    If this has been answered elsewhere, I apologize. I've search for a few hours and couldn't find it. Thank you for any help!

  • Annual leave planner

    Re: Annual leave planner


    Hi kennethbrandon -


    For the conditional formatting, highlight cell H8 and make your conditional format formula read:


    =$D8<>""


    and then drag down the formatting.


    For the Total Annual Leave (column AM), this formula works for me:


    =IF(SUM(AK8-AL8)=0,"",SUM(AK8-AL8))


    Hope that helps!

  • Longer Custom Lists in Excel 2007

    Re: Longer Custom Lists in Excel 2007


    Thank you, NBVC. Yes, initially I'd tried to create it using a single cell and that was impossible as it reached the 255 limit about 13 rows in. Importing from the cells allowed me to go down to 102 rows but still stopped there. Curious.

  • Longer Custom Lists in Excel 2007

    Hello - I am trying to create a custom list in Excel 2007 by importing cells A1:A312. Unfortunately the custom list stops at row 102 and rows 103 and beyond are not included.


    Is there a way to create a longer custom list than the 102?


    Thank you for any help!

  • VBA to Copy to Last Row for a Single Row

    Hello - I have a macro that determines the last populated row in a region and fills down the formula to the last row. It works awesomely unless there is only ONE row populated, in which case it copies down the row above. Here is the code:[INDENT]
    Sub CopyDown()


    Dim LastRow As Long
    LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
    Range("O14").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Key!C[-4]:C[-3],2,FALSE)"
    Range("O14").Select
    Range("O14:O" & LastRow).FillDown


    End Sub[/INDENT]


    I have this code for multiple columns, etc, but they're all using the same "fill down" information.


    I'm one of those "create a macro by recording it, then edit it" people and I'm slowly teaching myself how to write it, but not there yet. Any help would be greatly appreciated!