Get cell values from the selected cells by columns or by ranges:

[carmelai]

I have this Excel sheet structure with a VBA in the sheet module. It looks like this:

0000X VBA CODE.xlsm
	A	B	C	D	E	F	G
1	ARTICLE	ADJECTIVE	NOUN	VERB	PREPOSITION	ARTICLE	NOUN
2	ARTICLE	ARTICLE	ARTICLE	ARTICLE	ARTICLE	ARTICLE	ARTICLE
3
4	THE	RED	TREE	FALLS	ON	THE	CAR
5	A	BIG	TOY	BURNS	WITH	A	ACTOR
6	AN	EXOTIC	HOUSE	WALKS	LIKE	AN	FLAME
Foaie1

The VBA looks like this:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("A2").Value = ActiveCell.Value

Range("B2").Value = ActiveCell.Value

Range("C2").Value = ActiveCell.Value

Range("D2").Value = ActiveCell.Value

Range("E2").Value = ActiveCell.Value

Range("F2").Value = ActiveCell.Value

Range("G2").Value = ActiveCell.Value

End Sub

In cell A2 I want to get the value of any of the active selected cells from range A4:A6 or from column A. Then when I select a cell from range B4:B6 or column B I want to get a new value in cell B2 but to keep the previous value unchanged in cell A2. And so on with all the columns to the right till I get a complete sentence. I need this as my sheet will have thousands of rows for each main part of speech so I will scroll and as I scroll I will select new words to create new sentences.

 

[Fluff]

How about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Intersect(Target.EntireColumn, Rows(2)).Value = Target.Value
End Sub

 

[carmelai]

How about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Intersect(Target.EntireColumn, Rows(2)).Value = Target.Value
End Sub

Thank you very much! It works like a charm.

 

[Fluff]

You're welcome & thanks for the feedback.