Hi there,
I'm not good in programming and I hoped that people could help me on this platform.
I have made a macro that makes a productlist.
However some options of this productlist sometime need to be changed.
So I want a macro that scans my productlist and makes dropdownlists based on the list.
What this macro needs to do is:
- It has to search for a word from the (productlist) subjectlist in another sheet.
- To do this correctly a cell before the subjectcell contains always: frm .
- Something I can"t find is the lookup function that is as follows: frm >cell< - (cell to the right)- Subject >Cell<
- When cell is found then select till end > without the last row! named: Next
- Then it needs to make a defined name for the selection.
- Code
- Application.CutCopyMode = False
- Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False
- When the name is made there has to come a dropdownlist made specially for the subject cell
- And then is has to loop till the end of the productlist.
The code is missing the loop and automatically search option and make dropdownlist.
Sub test() ' ' test Macro Range("B4").Select Selection.Copy Range("C4").Select Cells.Find(What:="subject 1", After:=ActiveCell, LookIn:=xlFormulas2, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Sheets("Sheet1").Select Cells.FindNext(After:=ActiveCell).Activate Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _ False Sheets("Sheet2").Select Range("C4").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=subject_1" .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub