Search for a word in excel string

[QAVA]

Hi,

I am trying to find the word "qtp" if exist delete it from the cell. i dont want to delete all the data from the cell . only qtp.

that means if cell has "i like qtp". i want to delete "qtp"

This code works just fine to find qtp. i dont know how to delete qtp after if finds it.

Please advise.

Code:

Dim xl
t = "qtp"
Set xl = createobject("excel.application")
xl.Visible = true
Set sd = xl.Workbooks.Open ("F:\usp\FP Tickets to Automate2.xls")
Set st = sd.Worksheets("Sheet1")
rows = st.usedrange.rows.count
coloumn = st.usedrange.columns.count
msgbox t
For i = 1 to rows
For j = 1 to 2
Dim a
a = st.cells(i,j)
msgbox "a  "   &a
If Instr(1,a,t) >0 Then
'st.cells(i,6) = "pass"
msgbox "dd"
End If
Next
next

[Ankesh]

Code used is not optimized..its time consuming. you should better be using excel find and replace option for this.

use replace function instead of delete.

First find the text "i like qtp" and then replace it with "i like".

No delete is required.

Regards,
Ankesh

[QAVA]

I am not sure how to do that in qtp. How do i replace "i like qtp" with "i like" in excel using qtp?

[ssvali]

Sample Code :-

Code:

a = "i like QTP"

b= Replace(a,"QTP","")

Msgbox b

[QAVA]

First i need to find out whether QTP exist. if exist then replace with blank space. How to do that in excel?

I am simply working with excel. I just need to delete any cell has qtp and delete that. That cell has lot of data along with qtp.

How?

First i want to find whether any cell has qtp with other data.
second if qtp exist, replace with blank or delete it.

Sample Code :-

Code:

a = "i like QTP"

b= Replace(a,"QTP","")

Msgbox b

---

In excel, i also have in different cells 2) 3) 4).
I want to delete all of them (2) 3) 4).
) if it is any cell.

[ssvali]

Can you please attach ur excel...

[vinod123]

Code:

Dim appExcel, objWorkBook, objSheet, columncount, rowcount,

'Create the application object for excel
Set appExcel = CreateObject("Excel.Application")

'Set the workbook object by opening
Set objWorkBook = appExcel.Workbooks.open("c:\Smoke_Test\SmokeTest.xls")

'Set the Worksheet object
Set objSheet = appExcel.Sheets("Global")

'Get the count for total used columns
columncount = objSheet.usedrange.columns.count

'Get the count for total used rows
rowcount = objSheet.usedrange.rows.count

'Assign the data to search for
Find_Details="Report"

'Iterate the loop through each cell to find out required data
For a= 1 to rowcount
For b = 1 to columncount
fieldvalue =objSheet.cells(a,b)
If  cstr(fieldvalue)= Cstr(Find_Details) Then
msgbox cstr(fieldvalue)
Exit For
End If
Next
Next