change cell nos. by name

Microsoft Excel 2003 (full product)
January 11, 2010 at 04:40:50
Specs: Windows XP
Is it possible to replace multiple values by searching it by picking from another excel file.
suppose i have a excel file named dir.xls in which "A" column contains all cell no. & "B" column containc all name.
now when my cell phone bill comes on e-mail i want to run a search which pick value from file dir.xls fron its a1 cell & replcae it in bill file by b1 cell.

See More: change cell nos. by name

Report •

January 11, 2010 at 05:10:32
This post should be in the Office Software forum.

Report •

January 11, 2010 at 13:05:12

Attached is a macro that should do what you want. For this example, the new phone bill is in "PhoneBill.xls" with the phone numbers to be replaced by names in column B, starting on Row 2 (assumes a heading in row 1).
The macro calculates the number of rows in the Bill using a function called current region. For this to work properly there must always be one empty row below the last phone number entry.

The file containing your names to numbers is called "MyPhoneNos.xls" with a header row and pairs of names/numbers starting in row 2 - numbers in column A and names in column B.
The lookup table can be extended, but must have an empty row below the last entry.
Note that the table with headers must start at cell A1 - if not change the code to point to top left cell of lookup table.

You can change the Excel file names in the macro to match your actual requirements.
Note that for both workbooks the data is in Sheet1. If you rename the worksheets, the macro will have to be changed to match.

Place the code in the "MyPhoneNos.xls" workbook as this will be constant - the PhoneBill.xls workbook may be created new for each Bill.

On the MyPhoneNos.xls Sheet1 Worksheet, create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to 'Get Names'
Right-click the button again and select View Code
In the code window that opens enter this:

Option Explicit

Private Sub CommandButton1_Click()
Dim rngSource As Range
Dim rngLookUp As Range
Dim rngCell As Range
Dim varRetVal As Variant

On Error GoTo ErrHnd

'setup range of numbers in phone Bill
Set rngSource = Workbooks("PhoneBill.xls").Worksheets("Sheet1").Range("A1").CurrentRegion
'offset & resize starting at row 2 below header & just column A
Set rngSource = rngSource.Offset(1, 0).Resize(rngSource.Rows.Count - 1, 1)
'setup range of lookup table 
'(headings don't matter so no need to offset & resize the table)
Set rngLookUp = Workbooks("MyPhoneNos.xls").Worksheets("Sheet1").Range("A1").CurrentRegion

'loop through each cell in column B [Offset(0, 1)] 
'- assuming column B contains the phone numbers
For Each rngCell In rngSource
    'test for no match (if no match just leave the number)
    varRetVal = Application.VLookup(rngCell.Offset(0, 1).Value, rngLookUp, 2, False)
    If Not IsError(varRetVal) Then
        'replace number with name
        rngCell.Offset(0, 1).Value = Application.WorksheetFunction.VLookup _
            (rngCell.Offset(0, 1).Value, rngLookUp, 2, False)
    End If
Next rngCell
Exit Sub

'error handler
End Sub

Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them. Option Explicit goes before Private Sub CommandButton1_Click().
one line of code was split onto two lines for ease of viewing, using the line continuation character "_". This should work 'as is' just copy and paste, or you could remove the "_" and bring the code back to one line.

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).

The PhoneBill.xls workbook must be open (you could add code to the Macro to open it automatically if you wanted).
Click the 'Get Names' button.
Known numbers will be replaced by names.
Save the changed PhoneBill file using Save As, so that you can use the name PhoneBill.xls for each new Bill.


Report •

Related Solutions

Ask Question