Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Return value not from a selected cell

0

Hello

can someone please help me?

Is it possible that when any cell is selected in a row it returns the value of a specific cell in that row?

Here a have a code that returns anything that is selected to B1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Range("A2:F20")) Is Nothing Then
       For Each c In Intersect(Target, Range("A2:F20"))
              If c.Address = ActiveCell.Address Then
                     Range("B1").Value = c.Value
                     Exit For
              End If
       Next c
End If
End Sub

Example if i select A4 the return value in B1=C4

Sorry i am new to vba

excel
posted by: Paulus de boskabouter (rep: 4) on Sat Nov 12, 2022 at 5:57 pm
Answer
Discuss

Answers

0
Selected Answer

Hi Paulus and welcome to the Forum.

The attached file (and code below) demonstrate how you can tell a user a given cell in a given row (in A2:G10) and put a value (the address of the cell clicked) in another column. I've added comments so you can see roughly what is happening.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'if NOT a single cell selected or NOT in desired range, do nothing
If Target.Count > 1 Or Intersect(Target, Range("A2:H10")) Is Nothing Then Exit Sub

' otherwise put address of selected cell in column 12 (L)
Cells(Target.Row, 12).Value = Target.Address

' tell user the value from column J, (using header title)
MsgBox Cells(1, 10).Value & " in column J= " & Cells(Target.Row, 10).Value

End Sub

Change cell J1 and you'll get a different message.

For your example ("... If I select A4 the return value in B1=C4"), add this before End Sub:

Range("B1").Value = Cells(Target.Row, 3). Value

and the value from column 3 (C) will be copied to B1. 

You can change the bits in bold to address other cells.

You can find out much more from Don's tutorials in the Macros and VBA section (see Tutorials link above).

Hope this helps- if so, please remember to mark this Answer as Selected.

posted by: John_Ru (rep: 6142) on Mon Nov 14, 2022 at 9:52 am
Discuss

Discussion

Thank you 
This is what i needed.
Paulus de boskabouter (rep: 4) Nov 14, '22 at 11:21 am
Great. Thanks for selecting my answer, Paulus. 
John_Ru (rep: 6142) Nov 14, '22 at 11:28 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login