You are here:



Hi Tom ! Thanks for a quick reply.

As an answer of my question you gave me following code :

Dim rw as Long
Dim s1 as String, s2 as String, s3 as String
rw = ActiveCell.row

s1 = "=IF(B" & rw & "="""","""",VLOOKUP(B" & rw & ",'C:Documents and SettingsamiApplication DataMicrosoftExcel[masterdata.xlsx]Sheet1'!$B:$L,2,FALSE))"
s2 = "=IF(B" & rw & "="""","""",VLOOKUP(B" & rw & ",'C:Documents and SettingsamiApplication DataMicrosoftExcel[masterdata.xlsx]Sheet1'!$B:$L,3,FALSE))"
s3 = "=IF(B" & rw & "="""","""",VLOOKUP(B" & rw & ",'C:Documents and SettingsamiApplication DataMicrosoftExcel[masterdata.xlsx]Sheet1'!$B:$L,4,FALSE))"

Cells(rw, "C").Formula = s1
Cells(rw, "D").Formula = s2
Cells(rw, "E").Formula = s3

with Cells(rw, "C")
 .Formula = .value
End With
With Cells(rw, "D")
 .Formula = .value
End With
With Cells(rw, "E")
 .Formula = .value
End With
End sub

But i am facing a problem ...

as i am pulling 3 values from master data when i run the macro it asks me to locate the file and as i give the path it copies the first value to column c then it again ask me to give the path for next value and do the same so it become more hectic for me.

Can you please tell me the vba code which work on whole workbook and autorun as the value entered in column "B". as i dont want to give a path every time i want to search the value.

If you will allow me i can send you both Excel files.

Thanks & Regards
Amit Rajput


the reason you are getting prompted is that you don't have a proper path to your workbook listed in your code.  I can't supply you that information.  You are the one that knows what it is.  I copied what you originally sent.  Apparently some of the symbols were removed.  I have set up the code to do what you want, but you have to put in the proper path and debug the code if it does not do what you want.  Of course I could set it up to work on my computer - but that would not work on your computer because the file would be in a different place.

Right click on the sheet tab where you want this functionality and choose view code.  Then paste in the below code and correct the spath if it is not correct.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Long
Dim s1 As String, s2 As String, s3 As String

If Target.Count > 1 Then Exit Sub
If Target.Column <> 2 Then Exit Sub

rw = Target.Row

' Make sure the next line accurately reflects your path and workbook data
sPath = _
"'C:\Documents and Settings\ami\Application Data\Microsoft\Excel[masterdata.xlsx]Sheet1'"

s1 = "=IF(B" & rw & "="""","""",VLOOKUP(B" & rw & "," & sPath & "!$B:$L,2,FALSE))"
s2 = "=IF(B" & rw & "="""","""",VLOOKUP(B" & rw & "," & sPath & "!$B:$L,3,FALSE))"
s3 = "=IF(B" & rw & "="""","""",VLOOKUP(B" & rw & "," & sPath & "!$B:$L,4,FALSE))"

Cells(rw, "C").Formula = s1
Cells(rw, "D").Formula = s2
Cells(rw, "E").Formula = s3

With Cells(rw, "C")
.Formula = .Value
End With
With Cells(rw, "D")
.Formula = .Value
End With
With Cells(rw, "E")
.Formula = .Value
End With
End Sub

Tom Ogilvy

About Excel
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


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 All rights reserved.