I have written the following code as part of a larger sub-routine to set the value of a cell, relative to the active cell, when a particular selection has been made within the active cell.
ActiveCell.Offset(0, 5).Value = "CLV 7"
While this works, I may have need in the future to add columns into my worksheet and this presents a problem, due to the change of location of the cell that requires its value to be set, and by association the need to rewrite the code each time a new column is added.
In considering this variable, I researched and as a result, defined a range name for each column that requires values to be set within it. I thought that I would then be able to determine the variable & relocatable intersect point between the active row and the named range column and define it as the cell that requires the value to be set.
After this I researched ways to define this variable intersection and attempted to set the following alternate code:
ActiveCell.Offset(0, 0).Range("BusinessStudies").Value = CLV 7
in the hope that it would do the trick, but unfortunately it does not. I have looked at other posts and cannot see how to adjust it with any success as I can't see any similar requests.
try the Intersect() function in VBA
Debug.Print Intersect(Rows(ActiveCell.Row), Range("MyRange")).Value
Edit: apply to your situation, assuming that you want the string "CLV 7" to go into the cell:
Intersect(Rows(ActiveCell.Row), Range("BusinessStudies")).Value = "CLV 7"