Reiniervdijk
08-11-2020, 07:25 AM
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
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