Change Event to Auto Fill Address

[Alex O]

Is there a simple code that will automatically add the address and phone number when the information has already been entered? For example, my file has several hundred rows of customer information, but the addresses are missing (many duplicates are multiple invoice customers). If the clerk updates an address for a customer with multiple invoices, the information would automatically fill in the missing data (e.g. when the info for Kanawha County is entered in E36:I36, the same information would automatically be added to E37:I37).

Thanks

	C	D	E	F	G	H	I
1	Customer Name	Ship-To Location	Address	City	St	Zip	Phone
34	WEST VIRGINIA DEPT OF HIGHWAYS	WVDOH DIST 9 HEADQUARTERS	1334 SMITH ST	CHARLESTON	WV	25301-1434	3045583007
35	WEST VIRGINIA DEPT OF HIGHWAYS	SRC BUILDING	1334 SMITH ST	CHARLESTON	WV	25301-1434	3045583007
36	KANAWHA COUNTY PUBLIC LIBRARY	ST ALBANS PUBLIC LIBRARY	123 CAPITOL ST	CHARLESTON	WV	25301-2609	3043453492
37	KANAWHA COUNTY PUBLIC LIBRARY	KANAWHA COUNTY LIBRARY
38	REGULATORY TRAINING CENTER	REGULATORY TRAINING CENTER	120 3RD AVE	SOUTH CHARLESTON	WV	25303-1446	3042011112
39	CHARLESTON AREA ALLIANCE	CHARLESTON AREA ALLIANCE	1116 SMITH ST	CHARLESTON	WV	25301-1314	3043404253

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 338px;"><col style="width: 262px;"><col style="width: 210px;"><col style="width: 161px;"><col style="width: 67px;"><col style="width: 161px;"><col style="width: 161px;"></colgroup><tbody>
</tbody>

 

[My Aswer Is This]

I have written you a script which will do what you want but not exactly the same way you wanted.

On your sheet enter the address you want in column "E"

Then Double Click on the column "C" and all the correct addresses should be entered in all the correct rows

The reason I used Double click is because doing this when you just change the address may cause problems if you make a mistake.
A double click is a intentional action as just a cell value change could be accidental.
The script will run when you double click on any cell in column "C"

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cancel = True
Dim c As Range
    For Each c In Range("C1:C" & Lastrow)
        
        If c.Value = Target.Value Then
            Range(Cells(Target.Row, 5), Cells(Target.Row, 10)).Copy Destination:=Cells(c.Row, 5)
        End If
        Next
End If
End Sub

 

[L. Howard]

Hi Alex O,

This may work for you, if I understand correctly.

Copy to a standard module, and change the sheet name and range to match yours, I used Sheet2, and the range I used is C1:I500.

Run the code from "your sheet" and enter a customer name in the first input box, (you can copy a customer name first from column C and paste it in the input box and then run the code) click OK.

The code will filter all the customers of the chosen name and that name and any info for that name is what you will see on the sheet, and the first of five more inputboxes will pop up.

Now fill in the ensuing Inputboxes as they pop up, with the proper info and click OK for each. If you want to skip an inputbox (i.e. no new info needed for that input box), then just click OK with no entry and the next one will appear. So, if you got a new telephone number for a customer and that is all you want to change on that customer, then you would enter that customer in the first inputbox, the OK > OK > OK > across all the upcoming boxes until Telephone and enter the new telephone number/s and OK. Only the tele numbers will be upgraded and other data is undisturbed.

You can add more than one entry in any of the inputboxes, say for example you have two telephone numbers for a customer... you would make your entry in the inputbox like this 214-123-8855, 214-123-9922 (note the comma and the space). After the telephone number inputbox is completed, with an OK, the sheet will
un-filtered with all the new info adjacent the name that was chosen.

Howard

Option Explicit
Option Compare Text

Sub Inputbox_AutoFilter()

Dim Q_Info, i As Long
Dim myArr As Variant
Dim OneRng As Range
Dim rowEnd As Long, colEnd As Long
Dim lngLstRow&
Dim strAddress As String, strCity As String, _
    strState As String, _
    strZip As String, _
    strPhone As String

With Sheets("Sheet2")
     rowEnd = .Cells(.Rows.Count, 3).End(xlUp).Row
     'MsgBox rowEnd
            
     colEnd = .Cells(1, .Columns.Count).End(xlToLeft).Column
     'MsgBox colEnd
      
     Set OneRng = Range(.Cells(1, 3), .Cells(rowEnd, colEnd))

Q_Info = InputBox("Enter a customer name from column C")

   If Len(Q_Info) = 0 Then
       MsgBox "No Entry"
       Exit Sub
   End If

myArr = Split(Q_Info, ",")

  OneRng.AutoFilter
 
    For i = LBound(myArr) To UBound(myArr)
     
    
        If IsNumeric(myArr(i)) Then
          
           ActiveSheet.Range("$C$1:$I$500").AutoFilter Field:=(i + 1), Criteria1:=CLng(myArr(i))
            
        Else
         
          ActiveSheet.Range("$C$1:$I$500").AutoFilter Field:=(i + 1), Criteria1:=myArr(i)
               
        End If
        
        
    Next
    
 strAddress = InputBox("Adderss:", "The Address")

 strCity = InputBox("City", "The City")

 strState = InputBox("State, (two letters)", "The State")
 
 strZip = InputBox("Zip Code", "The Zip")
 
 strPhone = InputBox("Phone No.", "The Phone")
 
 
 lngLstRow = .UsedRange.Rows.Count + .UsedRange.Row
 
 .Range("E2:E" & lngLstRow - 1).Value = strAddress
 .Range("F2:F" & lngLstRow - 1).Value = strCity
 .Range("G2:G" & lngLstRow - 1).Value = strState
 .Range("H2:H" & lngLstRow - 1).Value = strZip
 .Range("I2:I" & lngLstRow - 1).Value = strPhone
 
End With
 
Selection.AutoFilter
 End Sub

 

[Alex O]

Thanks! Both suggestions worked beautifully...exactly what I needed.

Best,
Alex

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.
My script only required double clicking one cell as Howards required filling in a series of input boxes. But then there are always two ways to do most anything.

Thanks! Both suggestions worked beautifully...exactly what I needed.

Best,
Alex