You are here:

# Excel/vba

Question
Hi Richard

I am hoping you can help me out....here is what I need....

on a worksheet I have a range of A7:A700 and it calls for a plant number and then once that plant number is inputted then in cells B7,C7,D7,E7,F7 - which is the name, address, city state and zip....

I have this code below which works for one cell to once cell
but like I said I need cells B7 thru F7 to call up the address once its entered into A7 and down ward.....without me having to put that formula in each cell and the clients erasing them....

hope this makes sense and you can help

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

If Not Intersect(Target, Range("A1")) Is Nothing Then

If Range("A1") > 3 Then Range("C1") = "=H1"

End If

End Sub

there has gotta be a better ....

Tony

I am not a VBA expert, so I look for ways to solve problems without using VBA.  I would solve your problem using a VLOOKUP formula.
The formulas might look like this.

B7=VLOOKUP(\$A7,\$I\$7:\$N\$21,2,FALSE)
C7=VLOOKUP(\$A7,\$I\$7:\$N\$21,3,FALSE)
D7=VLOOKUP(\$A7,\$I\$7:\$N\$21,4,FALSE)
E7=VLOOKUP(\$A7,\$I\$7:\$N\$21,5,FALSE)
F7=VLOOKUP(\$A7,\$I\$7:\$N\$21,6,FALSE)

The formula looks in the array I7 thru N21 (this is the list of plants with the name, address, etc) and searches for the value in cell A7 (which is the plant number),  if the plant number is found, then the formula returns the value from the same row that is in the 2nd column of the array (the name) or the 3rd column of the array (the address) or the 4th column of the array (the city), etc, etc.

The column to return the data from is indicated by the 2,3,4,5,6 in the above formulas.  The "FALSE" in the formula tells excel that you want an exact match otherwise it will pick the closest match.

Because of the \$ creating the absolute references, the formula ss written can be copied down for the 700 rows that you need.  You can protect the cells if you want to eliminate the user from changing the formulas.

Of course you can put your list of plants anywhere in the worksheet or even in other workhseets, you just need to change the array references in the formula.

Hope this works for you.

Richard
Florida
USA
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Richard Roberts

##### Expertise

Can assist you in most areas of Excel, have been working with it for about 15 years in many types of applications, but primarily in financial and accounting applications. I am a CPA and many client or client problems have necessitated the use of excel. I am not an expert in charting, macros, or pivot tables.

##### Experience

Have been working with Excel for about 20 years primarily in accounting and financial areas.

Education/Credentials
BA, Certified Public Accountant