CODEWITHSUNDEEP
vbaexcel
Geminiflipflop
Geminiflipflop

Reputation: 119

Excel VBA - Searching a column for a particular value and returning the value from the adjacent cell

I'm a total noob at VBA and need all the help I can get so I apologise in advance for the dumb question. I feel its the only way I will learn.

I have a spreadsheet with two columns, I simply want to search column A for a particular value (I know what this value is, so can hardcode the value into the code) and return the value in column B and assign it to a variable.

Column A             Column B   
Parameter            Value  
TabDocumentPath      Path 1  
FrameworkPath        Path 2  
FrameworkAllFile     Path 3  
FrameworkFullPath    Path 4  
AssembliesPath       Path 5

So for example if you look at the above, I would like to search for AssembliesPath in Column A and assign the value in the adjacent cell (Path 5) to a variable. I know, its quite simple but I cannot seem to find this when I search the archive here. Any help would be greatly appreciated.

Upvotes: 1

Views: 2594

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You can use the WorksheetFunction.VLookup Method for this

VariableName = Application.WorksheetFunction.VLookup("Parameter", Worksheets("Worksheet1").Range("A:B"), 2, False)
  1. Parameter = lookup value
    we want to lookup "Parameter"
  2. Parameter = lookup range
    our lookup and return data is in columns A:B of the worksheet named Worksheet1
  3. Parameter = column index (of lookup range) to return
    we want to return the value of column 2 of the lookup range (this is B)
  4. Parameter = exact match or an approximate match
    False means exact match

Upvotes: 2

Related Questions