vbscript to search value in a worksheet

Hi
I'm working on an outlook form and need some help on how to search a column in an excel worksheet, find the value and add a value 3 columns to the right.

I know I need to use the offset to get to the column but am having some trouble in how to find the value in the row.  

Any help would be great. thanks.
JadeCaridadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris BottomleySoftware Quality Lead EngineerCommented:
For example to find something declasre cel as range ... or object as appropriate then use

    Set cel = ActiveSheet.Cells.Find(What:="string to be found", After:=ActiveSheet.Cells(ActiveSheet.Rows.count, ActiveSheet.Columns.count), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
    If not cel Is Nothing Then
' do something with the found datum
        debug.print cel.Offset(0,3)
    end if

Chris
0
JadeCaridadAuthor Commented:
Chris

Unfortunately since this is an outlook form and coded in vbscript it will not accept this type of structure:

ActiveSheet.Cells.Find(What:="string to be found", After:=ActiveSheet.Cells(ActiveSheet.Rows.count, ActiveSheet.Columns.count), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)

I'm currently researching the UsedRange to see if I can work my way up from each used row and check the value in Col A.  

Something like:    Set objRange = objWorksheet.UsedRange
objRange.SpecialCells(xlCellTypeLastCell).Activate
0
Chris BottomleySoftware Quality Lead EngineerCommented:
In outlook you need a reference to the excel application for instance xlApp.  then with xlapp you can qualify teh references for instance

activesheet.# becomes xlapp.ActiveWorkbook.ActiveSheet.#

it may be better to use a with clause though.  See snippet as below but presumably you have xlapp defined as something earlier in order for the rest of your code to work ... simply use your definition in place of xlapp.

Chris
with xlapp.ActiveWorkbook.ActiveSheet
    Set cel = .Cells.Find(What:="string to be found", After:=.Cells(.Rows.count, .Columns.count), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
    If not cel Is Nothing Then
' do something with the found datum
        debug.print cel.Offset(0,3)
    end if
end with

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

JadeCaridadAuthor Commented:
What I am trying to explain is that in outlook vba this would work
.Cells.Find(What:="string to be found", After:=.Cells(.Rows.count, .Columns.count), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)

However, when coding in the background of an outlook form, the following does not work.

.Cells.Find(What:="string to be found", After:=.Cells(.Rows.count, .Columns.count), LookIn:=xlValues, LookAt:= xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,searchFormat:=False)


0
Chris BottomleySoftware Quality Lead EngineerCommented:
I overlooked the constants, replace as follows:

xlvalues:    -4163
xlpart:         2
xlbyrows:   1
xlnext:         1

Hopefully that will be an improvement

Chris
0
Chris BottomleySoftware Quality Lead EngineerCommented:
I've started so I'll try and finish .... I missed the reference to formas and they have troubled me in the past but hopefully between your skills and a bit from me perhaps it will work out.

Chris
0
JadeCaridadAuthor Commented:
thanks Chris - I know it's a bit of a pain since in the outlook forms you can't use the Find Method that is acceptable in Excel.  I know you would have to use the SpecialCells feature in outlook to try to work through this request but am having some difficulty in building it since it's my first time working with it.  I wish it was as simple as using excel's find method because I would have been done a while ago :-]

All the best.
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Is it possible using a function subroutine to call the function from the form and take the address as the returned parameter?

Chris
0
JadeCaridadAuthor Commented:
not sure how that would work.....
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Nor me but for instance in a module of outlook put a function:

public function(findstring) as string
dim cel as range
    Set cel = .Cells.Find(What:=findstring, After:=.Cells(.Rows.count, .Columns.count), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
    findstring = cstr(cel.address)
end function

Chris
0
JadeCaridadAuthor Commented:
ahhh...now I understand...and no that will definitely not work.  sorry.
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Ah Phoeey!

What sort of error message, (if any) do you get when you try to use the find method?

Chris
0
JadeCaridadAuthor Commented:
I was working on something like this but am getting a runtime error at the moment.

Sub cmdRmvNm_Click()
Dim objExcel,objWorkbook,objWorksheet,iStart_Row

set cmdRmvNm=Item.GetInspector.ModifiedFormPages("Compliance").controls("cmdRmvNm")

On Error Resume Next
Set objExcel=getobject(,"excel.application")
Err.clear
if objExcel is nothing then
Set objExcel=createobject("excel.application")
end if
on error goto 0


Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbook.open(strPathName")
Set objWorksheet = objWorkbook.Worksheets(1)
iStart_Row=2
objWorksheet.Activate
objWorksheet.columns.item(1).select

Do While objWorksheet.cells(iStart_Row,0).value<>""
If objWorksheet.cells(iStart_Row,0).value=datavalue   then
objWorksheet.cells(iStart_Row,0).select
objWorksheet.cells(iStart_Row,0).offset(0,4)=anotherdata
end if
iStart_Row=iStart_Row+1
Loop

Tried testing it out but I'm getting a runtime error.
0
Chris BottomleySoftware Quality Lead EngineerCommented:
No offence I am not picking but:

Set objWorkbook = objExcel.Workbook.open(strPathName")
or
Set objWorkbook = objExcel.Workbook.open(strPathName)

and I don't see the setting of strpathname so the snippet may not represent true code

Chris
0
JadeCaridadAuthor Commented:
lol....sorry about that...I took out some work related info....
I meant only to send this portion

iStart_Row=2

objWorksheet.Activate
objWorksheet.columns.item(1).select
Do Until objWorksheet.cells(iStart_Row,0).value=""
If objWorksheet.cells(iStart_Row,0).value=Datavalue  then
objWorksheet.cells(iStart_Row,0).offset(0,4).value=OtherDataValue
end if
iStart_Row=iStart_Row+1
Loop

Not sure if this is the right approach though....can't figure out what's causing the runtime error.
0
Chris BottomleySoftware Quality Lead EngineerCommented:
objWorksheet.cells(iStart_Row,0).

Columns should be 1 to x in my experience, (offsets could be zero but not 'cells') therefore:

objWorksheet.cells(iStart_Row,1).

Chris
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JadeCaridadAuthor Commented:
Hi Chis,
I was able to figure it out on my own but wanted to award you the points for your patience and guidance throughout the process.  Your insight made me focus on what I was doing wrong.  

Thanks again.
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Glad to have been of some help and I am most especially pleased for you to have figured out the specifics.

Chris
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.