[xpost]
[/xpost]
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<
- To do this correctly a cell before the subjectcell contains always: frm .
- 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
- Code
- 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.
Code
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