none
VBScript: If value found in column A, show value in B RRS feed

  • Question

  • Hi,

    I have tried for quite some time to make a vbscript which will allow me to search a excel sheet within a spesific column (A) for a spesific keyword. If there is a match in column A for the keyword, I want to be given the value belonging next to it (B). Like a VLOOKUP in Excel.

    I have found out how to search for a given value in A, but it won't give me the value from B.

    A snippet of my code is as following:

    WHAT_TO_FIND = "something" 
    File_Path = "C:\test\users.xls"
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oExcel = CreateObject("Excel.Application")
    Set oData = oExcel.Workbooks.Open(File_Path)
    Set FoundCell = oData.Worksheets("Ark1").Range("A2:B500").Find(WHAT_TO_FIND)


    Should anyone feel the urge to give me some assistance, I would be grateful.


    • Edited by MrMinde Tuesday, July 15, 2014 10:03 AM
    Tuesday, July 15, 2014 10:01 AM

Answers

  • Add 1:

    WHAT_TO_FIND = "something" 
    File_Path = "C:\temp3\users.xlsx"
    Set oExcel = CreateObject("Excel.Application")
    Set oData = oExcel.Workbooks.Open(File_Path)
    Set FoundCell = oData.Worksheets("Ark1").Range("A2:B500").Find(WHAT_TO_FIND)
    row=FoundCell.Row
    col=FoundCell.Column
    MsgBox oData.Worksheets("Ark1").Cells(row,col+1)


    ¯\_(ツ)_/¯

    • Marked as answer by MrMinde Tuesday, July 15, 2014 1:38 PM
    Tuesday, July 15, 2014 1:10 PM

All replies

  • Just add 1 to the column number to get the cell immediately to the right.


    ¯\_(ツ)_/¯

    Tuesday, July 15, 2014 12:56 PM
  • Add 1:

    WHAT_TO_FIND = "something" 
    File_Path = "C:\temp3\users.xlsx"
    Set oExcel = CreateObject("Excel.Application")
    Set oData = oExcel.Workbooks.Open(File_Path)
    Set FoundCell = oData.Worksheets("Ark1").Range("A2:B500").Find(WHAT_TO_FIND)
    row=FoundCell.Row
    col=FoundCell.Column
    MsgBox oData.Worksheets("Ark1").Cells(row,col+1)


    ¯\_(ツ)_/¯

    • Marked as answer by MrMinde Tuesday, July 15, 2014 1:38 PM
    Tuesday, July 15, 2014 1:10 PM