0

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.

Paul Roub
  • 36,322
  • 27
  • 84
  • 93
ManbyRiver
  • 11
  • 5

1 Answers1

0

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"
teylyn
  • 34,374
  • 4
  • 53
  • 73
  • Thanks @teylyn. I assume that this requires a definition of "MyRange"? I think I have seen that this requires some code to do so earlier in the sub routine but I am not confident about that. Could you please point me in the right direction so that I can complete that part as well? – ManbyRiver Nov 16 '16 at 03:29
  • In thinking it through further, is it that 'MyRange' is replaced with the range name already defined? Also, do I simply use this code to replace the current code and add ' = "CLV 7" at the end? So the new code would look like this: Debug.Print Intersect(Rows(ActiveCell.Row), Range("BusinessStudies")).Value = "CLV7" Edit: I tried this just now and it doesn't work. Could you suggest any other adjustments or alternatives? – ManbyRiver Nov 16 '16 at 03:42
  • Yes. You can easily apply this to your situation. `Intersect(Rows(ActiveCell.Row), Range("BusinessStudies")).Value = "whatever"`. You would need to replace "whatever" with whatever you want in the cell. I edited my post. – teylyn Nov 16 '16 at 03:52
  • You little ripper (Not sure where you're from but if not from Australia that's Aussie Slang for That works beautifully - Thanks heaps!). The perfect and most simple solution! – ManbyRiver Nov 16 '16 at 03:56
  • Kiwi. Thanks for the feedback. – teylyn Nov 16 '16 at 08:54