auto fomatting 1

[tav1035]

I use the auto correct to fomat names in excel.
ie.
REPLACE: tommy
WITH: 4875125-TOMMY

The problem is I want everyone to be able to use this feature without setting up their auto correct.
I need VB code to put into the spreadsheet that will format this as they type or when they click out of the cell.
It will need to capitalize and add the persons ID. number in front of their name seperated by a dash..

Bonus: Each person works in pairs. Where cell 'A' = tommy, add his partner to cell 'B'. (his partner is 4875126-JOHN)

Any ideas?

 

[dwilson01]

This may not be exactly what you are looking for, but it may be useful to you. I attempted to solve your problem using the VLOOKUP worksheet function. The only problem I ran into is that I could not get the cell that you type the name into to update with the ID-Name.

I used sheet 1 to set a range called sh1Rng. sh1Rng columns A, B and C contain in order Name, ID-Name and Partner ID-Name.

When you type a name (tommy) in column A of sheet 2, the code inserts

=VLOOKUP(A1,sh1Rng,2,Flase)

into the current row, columns B & C, which evaluates to 4875125-TOMMY and 4875126-JOHN.

Post your email address if you would like me to send you the sample sheet I made.

 

[dwilson01]

I found a way to do what it is I think you want to do. With a similar setup as my last post, i.e. sheet 1 contains the NAME, ID-NAME and ID-PARTNER. Then typing the NAME in column 1 of sheet 2, the code will search sheet 1 for a match and if found will change the name typed in sheet 2 to the ID-NAME in column 1 and the ID-PARTNER will be added to column 2 of sheet 2. Copy this code to Microsoft Excel Objects - Sheet 2

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then
  Set sh1Rng = Worksheets(1).Columns(1).Find(Target, LookIn:=xlValues, LookAt:=xlWhole)
  If Not sh1Rng Is Nothing Then
    Target = sh1Rng.Offset(0, 1)
    Target.Offset(0, 1) = sh1Rng.Offset(0, 2)
  End If
End If

End Sub

Again, if you would like to see my sample sheet, post your email address.

 

[tav1035]

I'm new to this. I need steps to doing this.
How do I add this code to an object?
How do I set up SHEET 1?
I can do it, just need a little boost.
Thanks

 

[dwilson01]

Sheet 1 may look like this
Col 1 Col 2 Col 3
NAME ID-NAME ID-PARTNER
Tommy 4875125-TOMMY 4875126-JOHN
John 4875126-JOHN 4875125-TOMMY
Kate 4875127-KATE 4875128-SHARRON
Sharron 4875128-SHARRON 4875127-KATE
tim 4875129-TIM 4875130-RYAN
RYAN 4875130-RYAN 4875129-TIM
terry 4875131-TERRY 4875132-BRIAN
BRIAN 4875132-BRIAN 4875131-TERRY

As far as adding the code to an object, let me explain. If you point to Tools - Macros - Visual Basic Editor (Alt F11), a window will open for editing VBA code. If the Project window is not showing, click View - Project Explorer. In the Project Explorer the should be VBAProject(Your File.xls), click this to show Microsoft Excel Objects, click the + sign show Sheet(1), Sheet(2)... Double click Sheet(2), move your mouse to the pull down box that says General, click and select Worksheet. In the other pull down box select Change and copy the following code:

If Target.Column = 1 Then
  Set sh1Rng = Worksheets(1).Columns(1).Find(Target, LookIn:=xlValues, LookAt:=xlWhole)
  If Not sh1Rng Is Nothing Then
    Target = sh1Rng.Offset(0, 1)
    Target.Offset(0, 1) = sh1Rng.Offset(0, 2)
  End If
End If

If you have other problems, post your email address and I will send you my sample file.

 

[tav1035]

Never mind on the steps, I did it thanks.
The only question I have is,
if I want to reverse the rolls of WORKSHEET 1 and 2.
I export from a report like Crystal into Excel, the export goes into Worksheet 1, then I fill out the names in Column F and I would like to auto fill Column G. I would still like to use Worksheet 2 as a reference.
What would you change if any.

 

[tav1035]

One thing I can't get to work is:
After I have Column 1 and 2 populate, I would like to use the FILL HANDLE from the active cell to duplicate the names below. When I use the FILL HANDLE it doesn't duplicate properly. Instead it takes the record from Column 1 Row 1 from Worksheet 1 and fills it in.

How can I fix this?
Tvondra@hotmail.com

 

[dwilson01]

So, did you figure out the reversal of sheets? Here is a fix for the fill handles problem, basically what it does is check to see if the target address is more than a single cell, if it is, the code is skipped. This works in my test sheet, if this doesn't work for you, I don't understand the problem and am therefore wasting your time and mine. I hope this works for you or it points you in the right direction.

i = 0
For Each singleArea In Target.Cells
  i = i + 1
  If i > 1 Then Exit For
Next
If Target.Column = 1 And i = 1 Then

  Set sh1Rng = Worksheets(

2

).Columns(1).Find(Target, LookIn:=xlValues, LookAt:=xlWhole)
  If Not sh1Rng Is Nothing Then
    Target = sh1Rng.Offset(0, 1)
    Target.Offset(0, 1) = sh1Rng.Offset(0, 2)
  End If
End If

The RED items indicate the necessary changes.

 

[tav1035]

Works slick.
Thanks

 

[tav1035]

Now I have another question...

If Sheet 1, column 1, already has the names populated, the ID-NAME/ID-PARTNER won't populate because each cell has to be activated then deativated before it populates from Sheet 2 (or type the name in). The only way to get column 2 to populate (ID-NAME), is to click in and out of each cell.
Is this due to the 'singleArea' code?
Here is the code i'm using...

Private Sub Worksheet_Change(ByVal Target As Range)
i = 0
For Each singleArea In Target.Cells
i = i + 1
If i > 1Then Exit For
Next
If Target.Column = 1 And i = 1 Then
Set sh1Rng = Worksheets(2).Columns(1).Find(Target, LookIn:=xlValues, LookAt:=xlWhole)
If Not sh1Rng Is Nothing Then
Target = sh1Rng.Offset(0, 1)
Target.Offset(0, 1) = sh1Rng.Offset(0, 2)
End If

 

[RobBroekhuis]

To do after-the-fact (not event-based) filling-in of your IDs, you could create a separate macro, stealing code from your current one, that loops through your table and executes the find code for each cell, then populates the ID fields. You'd have to manually run the macro when necessary.
Rob