Saturday, January 9, 2010

Get a Row and retrieve Value Based on a your Desired Cell Value

Hi,

Herewith I am writing a script to get a Values of a Row by using a specific Cell Value in that Row. No, need to filter that value in a Sheet...

Check it once.

Dim xl

Set xl=createobject("excel.application")

xl.visible=true

xl.workbooks.open("D:\Baba_QTP\QTP\Test Data\Excel_Filter.xlsx")

xl.sheets("Sheet1").select

'Set xls=xl.range("A1","G1")

'xls.range("B1:G1").AutoFilter

'xls.range("B1:G1").AutoFilter 1,25

RC=xl.sheets("Sheet1").usedrange.rows.count
Print "Row Count of a Excel Sheet: "&RC
res=0

For i=2 to RC

' If (xl.sheets("Sheet1").cells(i+1,2).Value="25") Then

'reporter.ReportEvent micPass,"Step Passed","Step Passed"
'res=res+1
set ss=xl.sheets("Sheet1").Range("B"&i&":B"&i).Find("25")
ss_val=xl.sheets("Sheet1").Range("B"&i&":B"&i).Value
print "Value of Found Value Row"& ss_val

If (ss_val=25)Then
Val_Row=ss.row
Print "25 Contained in this row: "&Val_Row
Call Act()

Else
print "Row "&i&" doesn't Contain Value 25"

End If

' Print "25 Contained in These Rows: "&ss

' End If

Next

Function Act()

Res=xl.sheets("Sheet1").Cells(i,4).value
Print "Row " &i& " is "&Res

End Function


Thanks & Regards
Baba Fakruddin.D
baba.fakru@gmail.com

No comments:

There was an error in this gadget