[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 605
  • Last Modified:

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.
0
JadeCaridad
Asked:
JadeCaridad
  • 10
  • 8
1 Solution
 
Chris BottomleyCommented:
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 BottomleyCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 BottomleyCommented:
I overlooked the constants, replace as follows:

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

Hopefully that will be an improvement

Chris
0
 
Chris BottomleyCommented:
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 BottomleyCommented:
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 BottomleyCommented:
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 BottomleyCommented:
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 BottomleyCommented:
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 BottomleyCommented:
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
 
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 BottomleyCommented:
Glad to have been of some help and I am most especially pleased for you to have figured out the specifics.

Chris
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now