Find Combinations, Delimit, and Paste into new Tab

    Re: Find Combinations, Delimit, and Paste into new Tab


    Oh I'm sorry, I'm an idiot and included the Month headers in that count. It is 125. 3 months 5 variables (5*5*5) = 125. duh


    It works perfectly using exactly what you gave me for 3 months! I changed it to 2 months using the -2,-1,0,1,2 already there and it tells me to debug this line: Range("A4").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes


    Also when testing 4 months it only gives me 125 lines? When it should be 625 rows?

    Re: Find Combinations, Delimit, and Paste into new Tab


    I do not download files from external websites.
    After some time those files are being removed over there.
    This thread will become incomprehensible for anyone who is interested later. That's contrary to the raison d'être of this forum.
    If you post a sample file in this thread I (and probably others) will have a look at it.

    Re: Find Combinations, Delimit, and Paste into new Tab


    Quote

    This thread will become incomprehensible for anyone who is interested later. That's contrary to the raison d'être of this forum.


    Yeah.. good point.. I never thought of that and have previously encouraged others to post larger files to somewhere like Dropbox or Mediafire..


    I guess a better thing to do would be to cut down superfluous data in order to reduce file size.. and upload it within the forum like you say..

    Re: Find Combinations, Delimit, and Paste into new Tab


    Yes!! You don't realize how long I've been trying to figure this out. Pike it works perfectly! Thank you so much!


    I do have another question. The output range is stored as text. How/where can I incorporate code into the macro to select this entire range (A4 to last nonblank row and over to last nonblank column), copy, and paste into the next tab over as numbers (because the current output is stored as text)?

    Re: Find Combinations, Delimit, and Paste into new Tab


    Snb and apo: What's the problem? Did I do something wrong on the forum? My apologies!

    Re: Find Combinations, Delimit, and Paste into new Tab


    snb is right .. better to attach your examples via the forum attachment tool than by a third party site


    add the line below ..

    Code
    Loop
    End With
       Range("A4").CurrentRegion.Copy Destination:=Sheet2.Range("A1")   'add this line 
    Application.ScreenUpdating = True


End Sub


    current values are general
    why change?

    Re: Find Combinations, Delimit, and Paste into new Tab


    Apo: ohh ok so for future reference just link spreadsheets thru this site and not going thru box.com or any other file sharing sites? That way the thread can stay in tact in case that file is removed in the future. I got it, thanks for letting me know! I'll do so from now on.


    Pike: Since the values are "general", when I try to sort them in order of smallest to largest I can't because the sort table doesn't recognize them as #'s and instead tries to sort A-Z, which doesn't do anything.

    Re: Find Combinations, Delimit, and Paste into new Tab


    ok.. add another line

    Code
    Loop
    End With
       Sheet2.Range("A1").CurrentRegion.ClearContents
       Range("A4").CurrentRegion.Copy Destination:=Sheet2.Range("A1")
       Sheet2.Range("A1").CurrentRegion.NumberFormat = "0" ' add this line
    Application.ScreenUpdating = True


End Sub

    Re: Find Combinations, Delimit, and Paste into new Tab


    Huh, it does say it's formatted as a number in the ribbon. Yet the values themselves still say they are stored as text? Why might that be?

    Re: Find Combinations, Delimit, and Paste into new Tab


    Clicking on a cell in sheet2 where the values are pasted, it says number in the format in the above ribbon. But the actual cells have that green flag in the upper left of each cell with "!" and they all say, "Number Stored as Text"

    Re: Find Combinations, Delimit, and Paste into new Tab


    really odd, when I open it DOES give me green triangles and say "Number Stored as Text" even with that extra code. I saved it as a new file and everything to make sure, deleted that whole sheet and saved and closed and reopened and it still comes up as such. Hahaha this is just weird. You're not seeing it on your end?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login