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"
there has gotta be a better ....
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.
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.