So you have two columns, C and D, and you need to get a list of all values in D that match the entries in C. Original answer, shows how to get a list of all serial numbers for a specific stock number using only excel formulas, but it became clear that this wouldn't be sufficient, since the lists were going to be used to set validation. In this case, we use FILTER to return ONLY the serial numbers that match a stock number, UNIQUE to make sure there are no duplicates, and then TEXTJOIN to create a list from that, and then we can just pass that result straight to the validation. Unless specified, evaluate occurs in the context of the active sheet, so that's what I use that.evaluate in this code: Sub setValidation()Įxpr = "Textjoin("","", true, Unique(Filter(F:F, E:E=""" & stockNumber & """)))" In this case we will just write out a formula just like we would in a cell, and then evaluate it from VBA. That.Range(that.Columns(5), that.Columns(6)), _Īssuming you do have UNIQUE and FILTER in your Excel version, there is another way to do it, using the EVALUATE function to access the Excel function engine. Use Validation.Delete before setting the validation to avoid stacking rules.Īssuming that your version of Excel doesn't have UNIQUE, you can use INTERSECT to control the size of the serialNumbers array, like this: Sub setValidation() Then I run through the range that needs the validations (demo column 4), getting the stock number from column 3 and then using that stock number to find all serial numbers that match, concatenating them into a string and then using that string to set the validation. We assign some worksheet variables to make it easier to reference them, and then put the stock number/serial number combos into an array (with UNIQUE so I don't have to check for duplicates). SerialNumbers = Application.Unique(that.Range(that.Columns(5), that.Columns(6))) Then I run this code: Sub setValidation()ĭim this As Worksheet: Set this = Sheets("demo")ĭim that As Worksheet: Set that = Sheets("lookups") I have the complete list of items in Column 1 & 2 of the sheet (Item, Stock Number) and the complete list of serial numbers in columns 5 & 6 (Stock Number, Serial Number). See this google sheet, which just contains the layout for reference. However, before you set all that up it's probably just easier to set the validation entirely in code. You can set it up to do a dynamic range, but that would have to point to a range of cells that contain the needed values one per cell. The validation is looking for an array of values, and unfortunately it is tricky to pass a dynamic array using formulas only. So if you want to set the validation, it is possible to set dynamic ranges BUT the validation won't accept a text list, for instance "one, two, three". Type:=xlValidateList, _ '/Application-defined or object-defined error in thisĪnd sometimes code just hangs my excel application for atleast 3 mins, i think it's because there is no limit for cells to look up to and eventualy it tries to give all the cells in D:D a validation check This.Cells(r, 4).Validation.Add _'After doing everything it strucks with Run time error 1004 If serialNumbers(x, 1) = stockNumber Thenīuffer = buffer & comma & serialNumbers(x, 2) 'it in MS documentation) for Application object so i changed it to just Range' SerialNumbers = that.Range(that.Columns(3), that.Columns(4))'Could not find method Unique(and there is no mentions about' So i ran this code you proposed in your updated version and struck some problems Private Sub CommandButton2_Click()ĭim this As Worksheet: Set this = Sheets("ALFA")'renamed this for my book'ĭim that As Worksheet: Set that = Sheets("STORAGE")'renamed that for my book' Range(r.Offset(0, 1), r.Offset(0, 1)).Name = r.Valueīut thats not realy working, and assigns only one serial number per one named range of stock numbers LastRow = Cells(Rows.Count, "C").End(xlUp).Row I've tried this code Private Sub CommandButton2_Click() Stock numbers are in C column and serial numbers are in D column if i have two or more serial numbers i need to put an array of serial numbers in named range of one stock number. I have 2 columns first with stock number and second with serial number, i need to put all the same serial number in the named range of one of stock numbers. My idea is to have a drop down list for each item type in book for separate divisions so i need macro to assign/reassign named range for each item. I have a book lets pretend its a warehouse book for inventory, and we have different divisions in our enterprise, I have master sheet with all the goods and some sheets covering those divisions for distribution of goods between them. I'm kinda new here, but here is what I'm trying to do.
0 Comments
Leave a Reply. |