the original code is severly overcomplicated and also suffers from the all so common "VBA select disease".. no less then 7 variables have been setup to find 1 simple input value in a fixed range (C4) in another fixed range of 8 cells (A4:B7) on sheet2
Just cut all that cr*p and limit the code to do what it needs to.. variables are great if you need them several times in the code or to store an intermediate result to test (like foundCell here)
Explanation of the code.
Target is the name used in event macro's for the Activecell or the cell that triggers the event and often also the cell that is input or cell to be tested.
If Target.Address = "$C$4" Then
This line makes sure the code is only excecuted if the changed cell is C4 on the worksheet with this eventcode
if that is the case then
Set foundCell = Sheets("Sheet2").Range("A4:B7").Find(What:=Target.Value)
This will find the selected value of the dropdown and store it in variable foundCell.
You need to Set the variable to use it as (sort of a) named range so you can navigate to the wanted cell (here 1 column to the right later on)
If Not foundCell Is Nothing Then
This will test if a result was found, if no result is found the code skips next lines end goes to end sub
The main purpose is to avoid the code ending in error if for some reason the search value is not found or if value in C4 is removed
Application.EnableEvents = False
Will disable triggering the event code due to a cell change. This line is required here because we change the value of C4 next and that cell triggers this macro. So it would then excecute again. (this is why in the first code example of rorya the value showed up and disappeared again, the eventmacro was triggered again by the change made by the code)
Range("C4").Value = foundCell.Offset(0, 1).Value
the most important line, this actually changes the cell C4 to the wanted result.. by moving (offsetting) 1 column to the right from the foundCell-range
Application.EnableEvents = True
this turns event macros back on to make sure next time C4 is changed the eventcode is excecuted properly
hope that this explains the code and shows why you should not always use variables in simple code that does 1 simple thing.
Keep it simple
Bookmarks