Mapping Data

[keranali]

Hi all how do you click on rows to populate columns lets say A2 has a row to text straight to G2 and A3 to G3 and so on, I want to populate column I with the row info, now when I click either A2 row or A3 row the same column changes to reflect the clicked row information.

Please help
thanks

 

[keranali]

Thank you very much for teaching me its like a puzzle I got It

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRw As Long, ThisRw As Long

LastRw = Range("A" & Rows.Count).End(xlUp).Row
If Intersect(ActiveCell, Range("A2:I" & LastRw)) Is Nothing Then
Range("w3:w5,w8:w11").ClearContents
Else
ThisRw = ActiveCell.Row
Range("w3:w5").Value = Application.Transpose(Cells(ThisRw, "G").Resize(, 3).Value)
Range("w8:w12").Value = Application.Transpose(Cells(ThisRw, "B").Resize(, 5).Value)
End If
End Sub

Yay You guys are really smart

 

[keranali]

Me again can those dropdown lists from the control form work with this formulation VBA?

K

 

[Peter_SSs]

Thank you very much for teaching me its like a puzzle I got It

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRw As Long, ThisRw As Long

LastRw = Range("A" & Rows.Count).End(xlUp).Row
If Intersect(ActiveCell, Range("A2:I" & LastRw)) Is Nothing Then
Range("w3:w5,w8:w11").ClearContents
Else
ThisRw = ActiveCell.Row
Range("w3:w5").Value = Application.Transpose(Cells(ThisRw, "G").Resize(, 3).Value)
Range("w8:w12").Value = Application.Transpose(Cells(ThisRw, "B").Resize(, 5).Value)
End If
End Sub

Yay You guys are really smart

Perhaps you don't have anything in column F (I don't know because it was hidden in your post) but you are actually copying it to column W as well. Did you mean to do that? If not, look at the highlighted change in the code below.

You seem to be ignoring my comments/requests about using Code Tags to post code. If you post unformatted code you will find that many of the best vba helpers in the forum will just ignore your question. Click the 'Quote' button at the bottom of this post of mine and you will see how easy it is to put the code tags at the start and end of the vba code. Presumably you can see how much easier mine is to see where each block of code (eg If ... Else ... End If) starts and ends.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim LastRw As Long, ThisRw As Long
    
    LastRw = Range("A" & Rows.Count).End(xlUp).Row
    If Intersect(ActiveCell, Range("A2:I" & LastRw)) Is Nothing Then
        Range("w3:w5,w8:w11").ClearContents
    Else
        ThisRw = ActiveCell.Row
        Range("w3:w5").Value = Application.Transpose(Cells(ThisRw, "G").Resize(, 3).Value)
        Range("w8:w11").Value = Application.Transpose(Cells(ThisRw, "B").Resize(, 4).Value)
    End If
End Sub

Me again can those dropdown lists from the control form work with this formulation VBA?

K

Almost certainly the answer is "Yes" but to get more detail you would have to explain just what you have and what you want to achieve.

If it is not directly related to what has already occurred in this thread then you would be best to start a new thread about that.

 

[keranali]

I am very sorry about the code placement I will do In the future Your VBA code worked splendid exactly what I wanted thank you, I n column B and C I want to be able to have a drop down list because the fields will be in use over and over the database should be in sheet 2 I know how to make a combo box but I dont know how to link it to the changing data?

Excel Workbook
	A	B	C	D	E	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	TRACKING NUMBER	BRANCH NAME	ADDRESS	TEL.FAX	EMAIL.WWW	NAME	POSITION
2	AAAA	BBBB	CCCC	DDDD	EEEE	FFFF	GGGG
3
4
5
6
7
8
9
10
11
12
Sheet1

Thanks
Keran

 

[keranali]

Are you the owner of Mr.excel website?

 

[Peter_SSs]

I n column B and C I want to be able to have a drop down list because the fields will be in use over and over the database should be in sheet 2 I know how to make a combo box

Could you just use Excel's built-in Data Validation in columns B & C rather than worrying about Combo Boxes?

Are you the owner of Mr.excel website?

No, the owner is Bill Jelen aka MrExcel

 

[keranali]

Hi Peter I will try that data validation feature, can you teach me some stuff in VBA do you offer classes??

K

 

[Peter_SSs]

.. can you teach me some stuff in VBA do you offer classes??

No, sorry, I don't offer classes. However, you can learn a lot by using this forum as a resource.
Follow the link below to see how I did most of my vba learning.
http://www.mrexcel.com/forum/showpost.php?p=1597608&postcount=13

And a couple of other MrExcel places to check out are ..
http://www.mrexcel.com/
http://www.mrexcel.com/store/index.php

 

[keranali]

Thanks for all your help thus far the data validation method worked well however if Column C row 2 has the data validation table how possible is it to populate in col D row 2 information from sheet 2. I dont know if i am making sense,

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	BRANCH NAME	ADDRESS	ADDRESS 2	ADDRESS 3	ADDRESS 4	ADDRESS 5	ADDDRESS 6	ADDRESS 7	ADDRESS 8	ADDRESS 9
2	Arima	Corner Pro Queen & Devenish Streets, Arima	JAMES LAND TOBAGO	CROSS CROSSING	*	*	*	*	*	*
3	Carlton Centre	St. James Street, Carlton Centre, San Fernando	*	*	*	*	*	*	*	*
4	Chaguanas	Chaguanas main road Spain	*	*	*	*	*	*	*	*
5		Royal Plaza, Main Road, Chaguanas	*	*	*	*	*	*	*	*
6	Chaguaramas	Western Main Road, Chaguaramas	*	*	*	*	*	*	*	*
7	Couva	Couva Shopping Complex, Southern Main Road, Couva	*	*	*	*	*	*	*	*
Sheet3

So if some one clicks on an item in the validation list the next list limits the amount of searches to address 1 2 3 4 5 6 7 8 9 10.

Thanks

 

[Peter_SSs]

Can't give an exact formula since I don't know what is on Sheet2 or how it is laid out but I suspect there is a table of data there in which case most likely a VLOOKUP or HLOOKUP function would be what you want. Have a look at those in the built-in Help.