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]

Hi Peter everything is working well I tried creating a auto delete command button and that whats messing things around when I use the auto delete code it it stops the use of your move left code. Am I missing something?

 

[keranali]

Test

 

[keranali]

Code

Sub TRASH()
'
' TRASH Macro
'
'
'
Range("A2:M20").Select
Range("M20").Activate
Selection.ClearContents
ActiveWindow.SmallScroll ToRight:=-8
Range("A2").Select
End Sub

 

[Peter_SSs]

I don't know how much data you have in your sheet or where it is located. The only thing I have to go on is your previous sample screen shots which have generally shown data in the first few rows of columns A:M

The last code you posted deletes all cell values from the range A2:M20. So if you ran this code before my code, what would be left for my code to work on? If you ran it after my code presumably it would delete what my code did anyway.

The upshot is that I don't know enough about what is in your sheet and what you are really trying to achieve.

 

[keranali]

Hello Peter thanks for the reply, I am trying to put together a business card database and preview spread sheet whereby allowing the to make basic data entry. This a sample of the sheet below:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	W	X
1	LOCATION	QUANTITY	NAME	POSITION	DEPARTMENT	BUSINESS UNIT	ADDRESS LINE 1	ADDRESS LINE 2	ADDRESS LINE 3	EMAIL.WWW	TEL.:	EXT.:	FAX	CEL.:
2	ARIMA						12 JHONSTON STREET	MARC AVENUE	ARIMA, ROAD ARIMA		800-123-4567		800-321-9876
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sheet1

Now under location COL A2 there is a data validation box which is reading information from sheet 2 COL A1 as follows:

Excel Workbook
	A	B	C	D	E	F
1	LOCATION	ADDRESS LINE 1	ADDRESS LINE 2	ADDRESS LINE 3	TEL.:	FAX
2	ARIMA	12 JHONSTON STREET	MARC AVENUE	ARIMA, ROAD ARIMA	800-123-4567	800-321-9876
Sheet2

I added this code to the first data mapping code in order to automatically plot information on sheet 1 G,H,I,K and M using the data from sheet 2.

Range("G2:G25").Formula = "=IFERROR(IF(VLOOKUP(A2,DATALIST,2,FALSE)="""","""",VLOOKUP(A2,DATALIST,2,FALSE)),"""")"
Range("H2:H25").Formula = "=IFERROR(IF(VLOOKUP(A2,DATALIST,3,FALSE)="""","""",VLOOKUP(A2,DATALIST,3,FALSE)),"""")"
Range("I2:I25").Formula = "=IFERROR(IF(VLOOKUP(A2,DATALIST,4,FALSE)="""","""",VLOOKUP(A2,DATALIST,4,FALSE)),"""")"
Range("K2:K25").Formula = "=IFERROR(IF(VLOOKUP(A2,DATALIST,5,FALSE)="""","""",VLOOKUP(A2,DATALIST,5,FALSE)),"""")"
Range("M2:M25").Formula = "=IFERROR(IF(VLOOKUP(A2,DATALIST,6,FALSE)="""","""",VLOOKUP(A2,DATALIST,6,FALSE)),"""")"

As a result of combining the two codes :

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

Range("G2:G25").Formula = "=IFERROR(IF(VLOOKUP(A2,DATALIST,2,FALSE)="""","""",VLOOKUP(A2,DATALIST,2,FALSE)),"""")"
Range("H2:H25").Formula = "=IFERROR(IF(VLOOKUP(A2,DATALIST,3,FALSE)="""","""",VLOOKUP(A2,DATALIST,3,FALSE)),"""")"
Range("I2:I25").Formula = "=IFERROR(IF(VLOOKUP(A2,DATALIST,4,FALSE)="""","""",VLOOKUP(A2,DATALIST,4,FALSE)),"""")"
Range("K2:K25").Formula = "=IFERROR(IF(VLOOKUP(A2,DATALIST,5,FALSE)="""","""",VLOOKUP(A2,DATALIST,5,FALSE)),"""")"
Range("M2:M25").Formula = "=IFERROR(IF(VLOOKUP(A2,DATALIST,6,FALSE)="""","""",VLOOKUP(A2,DATALIST,6,FALSE)),"""")"

LastRw = Range("A" & Rows.Count).End(xlUp).Row
If Intersect(ActiveCell, Range("A2:N" & LastRw)) Is Nothing Then
Range("P6:N8,P10:P15,U10:U15").ClearContents
Else
ThisRw = ActiveCell.Row
Range("P6:P8").Value = Application.Transpose(Cells(ThisRw, "C").Resize(, 3).Value)
Range("P10:P13").Value = Application.Transpose(Cells(ThisRw, "F").Resize(, 4).Value)
Range("U10:U13").Value = Application.Transpose(Cells(ThisRw, "K").Resize(, 4).Value)
Range("P15").Value = Application.Transpose(Cells(ThisRw, "J").Resize(, 1).Value)
End If

End Sub

This entire code is embedded into sheet one without any modules. These two codes allows the user to pick a location fill in the blanks and see a preview to the right of the screen in col P,T and U.


I have included a administrator button which uses your code to move data to the left for processing upon receiving the file. I have also added a password feature to prevent the user from this function.

Sub MoveLeft()
    
    Dim pw     As String
    pw = Application.InputBox("Enter password")
    Select Case pw
        Case "t"
        
    Dim Blnks As Range, A As Range
    Dim LR As Long, r As Long, cols As Long
    
    Const ChkCols As String = "C:E,F:I,K:N"
    
    Application.ScreenUpdating = False
    LR = Range("C" & Rows.Count).End(xlUp).Row
    For r = 2 To LR
        On Error Resume Next
        Set Blnks = Intersect(Rows(r), Range(ChkCols)).SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0
        If Not Blnks Is Nothing Then
            For Each A In Blnks.Areas
                cols = A.Columns.Count
                A.Offset(, cols + 1).Insert Shift:=xlToRight
                A.Delete Shift:=xlToLeft
            Next A
            Set Blnks = Nothing
        End If
    Next r
    Application.ScreenUpdating = True

       Case Else
            Exit Sub
    End Select


End Sub

Now to make the entire sheet new once more for processing fresh data I added "trash" code to remove all data in sheet 1 A2 : N25 as follows

Sub TRASH()
'
' TRASH Macro
'

'
    Range("A2:N25").Select
    Range("M20").Activate
    Selection.ClearContents
    ActiveWindow.SmallScroll ToRight:=-8
    Range("A2").Select
End Sub

The problems I am experiencing are:

1. How I inserted this code:

Range("G2:G25").Formula = "=IFERROR(IF(VLOOKUP(A2,DATALIST,2,FALSE)="""","""",VLOOKUP(A2,DATALIST,2,FALSE)),"""")"
Range("H2:H25").Formula = "=IFERROR(IF(VLOOKUP(A2,DATALIST,3,FALSE)="""","""",VLOOKUP(A2,DATALIST,3,FALSE)),"""")"
Range("I2:I25").Formula = "=IFERROR(IF(VLOOKUP(A2,DATALIST,4,FALSE)="""","""",VLOOKUP(A2,DATALIST,4,FALSE)),"""")"
Range("K2:K25").Formula = "=IFERROR(IF(VLOOKUP(A2,DATALIST,5,FALSE)="""","""",VLOOKUP(A2,DATALIST,5,FALSE)),"""")"
Range("M2:M25").Formula = "=IFERROR(IF(VLOOKUP(A2,DATALIST,6,FALSE)="""","""",VLOOKUP(A2,DATALIST,6,FALSE)),"""")"

Whenever the "moveleft" vba shifts the data on the vlookup column "G,H,I,K,M " it deletes the data.

2. The trash feature stops the use of the "move left" macro whenever new data is entered and saved, example the form is being filled out and a mistake occurs the trash feature is used to wipe clean all data, new data is entered and saved, the administrator receives the file and uses the "move left" vba to shift the data left respectively for processing the feature has now been disabled?.


I hope you can help me in solving these problems, that I have created.

Thanks
K

 

[keranali]

Whenever the "moveleft" vba shifts the data on the vlookup column "G,H,I,K,M " it deletes the data, And automatically duplicates the data that moved left, is there a way to tell the vlookup function to end when the move left feature is about to begin.

 

[Peter_SSs]

OK, I haven't studied all that code (yet at least) but a few things first ..

1. When using Excel jeanie, please use the ‘Analyse range (Forum)’ field near the top left to restrict the number of formulas generated. There is generally no need to display multiple formulas that are basically the same, it just fills up the board.

2. I'm not sure if you already stated this or not but what version of Excel are you using?

3. Please explain just how the named range "DATALIST" is defined.

4. Please explain just what the Data Validation in A2 of Sheet1 is.

5. Note for the future you can get Excel jeanie to show how names are defined (check 'Resolve names in formulas' box) and what Data Validation in a cell is (check 'Data Validation' box) but please use this in conjunction with point 1 above (we don't need to know the Data Validation in 20 cells that have the same DV )

6. When asking about the MoveLeft macro, your data samples didn't indicate that the cells contained formulas. That will make a difference. Maybe will return to this later.

 

[keranali]

Hello Peter I will use the Analyse range thanks for the tip. I am also using Microsoft Excel 2007, Datalist is defined as A1:F on Sheet 2.
Data validation in sheet 1 A2 consists of a list which is =BRANCH as source and A1:A as the Range.

hope this helps.

K

 

[Peter_SSs]

I think for me this project is getting beyond the bounds of what can be expected from a free public forum like this and I'm having trouble getting my head around your overall layout, data and aims.

What I would suggest is to consider getting your macro to populate the data into Sheet1 directly instead of populating formulas. That way you should be able to put the data in the correct place straight off rather than find that the formulas leave gaps and then try to move the results.

If you need help with specific parts of any of your code then certainly post those questions (in a new thread possibly).

 

[keranali]

Hello Peter thanks for your help I too have a couple ideas of getting it right i'll give it a shot thanks again.

k