Posts by jan_g

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi

    I have a list of data in table format which is copied to 2 different tabs.

    Labels Tab - replicates the data N times based on the Value in each column H:M, then based on the size details in column F, fills in the size on each row. - This Macro works perfectly well.


    Export Tab - counts the number of cells containing data in each row, and replicates N times based on the Count. (if there are 3 sizes with orders, copy the row 3 times and show which sizes are ordered) This replication is working but I cannot figure out how to get the size to show only once and not for the count. The code is using .value which is causing the issue but I cannot figure out. after several hours of trying, what to change it to :|

    I hope this makes sense.


    Many thanks in advance.


    J:)


    Hi

    I have data that sums across columns based on column header. I need to take these totals and transpose to a single column without the 0 results.

    Currently the code (thanks to another user) results in a blank cell where the formula result is zero.

    How can I transpose the data so that there are no blank (zero) cells in the resulting list? Pls refer screenshot for desired result

    I thought an If > 0 statement might work but I got the same result. Skip blanks is also not applicable as there are results for the formula.

    Many thanks for your help once again.

    J:)

    Hi Jolivanes,

    Fantastic. Thank you so much for your patience with this. I have run both sets of code but prefer you last option in Comment 12 because of the flexibility with columns.

    :/:?:I have spent some time understanding the code but don't quite follow the syntax in line 7 - Find "RRP" in Row 3. Commas rep Optional arguments - how do you know how many to put in? and what is the "1"? If you have a moment, could you please explain.

    I have read these 2 article https://docs.microsoft.com/en-…/vba/api/Excel.Range.Find and this https://www.ozgrid.com/VBA/find-method.htm but still not quite getting the syntax.


    Cheers and have a great day. 8)

    After spending much time searching with no progress, I have made some changes to the above code and have used WorksheetFunction.Countif.

    (The if/else statement has been removed as I couldn't get it to give the correct result....still some work needed.)


    The issue with the code to this point is that without the "on Error Resume Next" I get an "Application Defined or Object Defined" error on Line 22. But I cannot work out what the problem is with the code. Any pointers in the right direction would be much appreciated as I am completely stumped. Thanks.

    Hi Roy,


    I have up uploaded a new formatted spreadsheet. In doing so I noticed a code error which I have updated. This code was adapted from a larger project where the rows were resized based on the numbers in each size columns but for this project I only require the rows to be resized by the count. My apologies if this caused confusion. As mentioned above, is it possible to replace the reference to Column 14 with a variable? I just can't get the formula to work.

    Thanks.


    Jan


    Hello,


    I thought this would be easy but everything I try gives me a blank sheet or a qualifier error. The code on the attached example gives the correct result when using a column reference to lookup Countif result (Sheet Orders Col14) in the For Loop. I want to change the code to use a variable for the "Countif" so that I can remove the column from the worksheet. I have tried using Application.WoksheetFuntion and R1C1 reference for the variable but am getting completely stuck. Hope that makes sense.


    Really appreciate the help this site provides to all of us that are learning.


    Cheers :)


    Jan



    Hi,

    Thanks for the file and for your time to assist. It is very much appreciated. That worked great. I added one further row to copy down the size Label into the last column as well.

    Cheers and have a good day.


    Hi Jolivanes,

    Sorry for the confusion.

    In my company worksheet there are many other tabs which are interlinked with formulas and lookups. I could probably delete those and then replace all formula's with static values and change the data but I thought it simpler to give an example which mirrored what I was looking to achieve. If you prefer I do this then I can.
    The products tab (10cols) contains all the info for each product

    The Labels tab extracts only the information that is required for each label, hence only 9 columns.

    Sorry..I'm a bit lost re Post #4?

    To merge with MS Word, each product has to be on a different row..which the code does..I just don't quite know how to get the Size (XS,S etc) in a column.

    Hope this makes a little more sense.

    Hi, Understood, but the actual file contains lots of company sensitive information which I am unable to upload. The data and I have created in this file represents what the macro is trying to achieve. The labels only contain Product Code, Description, Colour and Size and a barcode which will be added later. Cheers.

    Good Afternoon,

    I am trying to set up a list of data to do a mail merge into Word Labels.

    The Current macro copies the required columns (Products) to a new Sheet(Labels). Once copied, the rows are duplicated based on the total quantity so that the correct no of labels are printed.

    From here...and I am not sure where to even start, I would like to take the Size of each garment and copy n times based on the count in the column. Sheet 3 shows what the end result should look like. Workbook attached.

    Thanks for your help.

    Hi,

    If Rows are deleted from the end Table A, then delete from end Table B.

    If Table A & B are not on the same rows and structured references used, the delete code deletes the first rows not the bottom rows.

    Cheers and thank you so much.