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