Compare a link name to a column in Excel

[mv8167]

I have a set of link names that I have copied into column D of one Excel spreadsheet.

If the column D of #1 Excel spreadsheet matches any of the values in column E of #2 Excel spreadsheet, the link name is Ok to be used.

How is the search of an entire column possible? Must I know how many rows there are?

[Ankesh]

Yes u must first get the row count. You can use usedrange. once you get the number of rows, you can proceed with the comparision.

[mv8167]

Ankesh,

Ok, greast but...

How do I do the compaireson of a link name to an entire coulmn of data?

thank you ... ;-)

[Ankesh]

Lorena,

Yes comaparing a link with entire coulmn seems to be tricky and time consuming as well. One idea would be to use the excel Find property to check for the value which we are looking for.

I have developed the below code which worked fine for me. Can you try and check on your side if this solves your purpose?

'***********************************************************

strSearchedItem ="91773978"'please specify the link that you want to search

Code:

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("C:\TestData_SAP_FICO_FI_VF11_CANCEL BILL DOC_037.xls") 'Enter the file path

Set actualValues  = objWorkbook.Worksheets("FICO_CANCEL_BILL_DOC") 'Enter the sheet name

objExcel.Visible=True '

intFoundRow = actualValues.Columns(4).Find(strSearchedItem).Row ' Find the searched item in excel on the column specified

intRowCount = actualValues.UsedRange.Rows.Count 'Get the total row count

If intFoundRow > intRowCount Then
    Reporter.ReportEvent micFail, "Item " &strSearchedItem & " not found in Report", ""
else
   Reporter.ReportEvent micPass, "Item " &strSearchedItem & " found in Report", "Item " & strSearchedItem & " found in Report in line " & intFoundRow
End If

'Release the objects
objExcel.Visible=False
Set actualValues=Nothing
Set objWorkbook=Nothing
objExcel.Quit
Set objExcel=Nothing

'*********************************************************

Regards,
Ankesh

[mv8167]

Wow! Ankesh, thank you.

I will try this and see what happens. I do see that I might need to match up 3 column variables (in one row) to find a value that is unique. but this will help.