Excel Issue - Need to find cell value

[john_shiels]

I have an excel file pulled into a collection. I now need to identify a cell location that contains specific text. Should I use Business Object Utility Calculation Manipulation or MS VBO Excel to find the exact cell?

[MurthujaShaik]

Hi, Easiest way is to use MS Excel VBO .

[jpunthambaker]

Is there any resolution on this topic? Using the Excel VBO is best when working with small worksheets. When the line count >1000, it would be nice to use a macro to find a cell with a specific value & then use the 'get active cell' VBO action. Any insight or assistance on creating such a code stage would be very helpful. Thanks.

[ArchiveUser]

You may use the following solution and add a code stage into MS Excel VBO Inputs: [handle] as number, [Workbook], [Worksheet], [Range], [Value] - all as text Outputs: [CellRef], [Message] - both as text Code: Dim excel, sheet As Object Try sheet = GetWorksheet(Handle, Workbook, Worksheet) excel = sheet.Application If sRange="""" Then Message = ""No Range provided"" Else sheet.Activate sheet.Range(sRange).Find(What:=FindValue, LookIn:= _ -4163, LookAt:=1, SearchOrder:=1, SearchDirection:= _ 1, MatchCase:=False, SearchFormat:=False).Activate CellRef = excel.ActiveCell.Address End If Catch e As Exception Message = e.Message Finally excel = Nothing sheet = Nothing End Try

[jpunthambaker]

Hi Radoslav, The code works wonderfully although there were two very slight syntax errors. Fixed version is below. Namely, I changed all instances of sRange to Range & changed FindValue to Value. Cheers. Inputs: [handle] as number, [Workbook], [Worksheet], [Range], [Value] - all as text Outputs: [CellRef], [Message] - both as text Code: Dim excel, sheet As Object Try sheet = GetWorksheet(Handle, Workbook, Worksheet) excel = sheet.Application If Range="""" Then Message = ""No Range provided"" Else sheet.Activate sheet.Range(Range).Find(What:=Value, LookIn:= _ -4163, LookAt:=1, SearchOrder:=1, SearchDirection:= _ 1, MatchCase:=False, SearchFormat:=False).Activate CellRef = excel.ActiveCell.Address End If Catch e As Exception Message = e.Message Finally excel = Nothing sheet = Nothing End Try

[ArchiveUser]

Thank you for fixing. The problem was that the data item name differed from the variable name in the code which I did not notice when posting.

[jpunthambaker]

No problem! Thanks for your help. Just wanted to note something to future viewers of this thread. The code above attempts to find items with 'Match entire cell contents' enabled. The use of the wildcard * negates this. -Jay

[MahmudBarrak]

Thank you for the code, I'm trying to use this code but I have the following message in the data item [Message]: ""Object variable or With block variable not set."" Do you have any idea on how to resolve this ? Thank you. MBarrak