If activecell.value = "" then not working 1

[newestAF]

Using Windows XP and Office 07. The below code is in a word macro to extract data from an excel spreadsheet. The value "MyPAS" is first a selection within a word document used as the search value to find in excel. What I can't figure out is how to program it to input a default value if the searched value is not found.

*************START CODE******************

Function CheckAdminName()
On Error GoTo Error_Handler

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim defaultname As Range
Dim startcell As Range
Set xlApp = CreateObject("excel.application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("C:\Documents and Settings\neil.west\Desktop\Product Runner PASADMIN.xls")
With xlWB.Worksheets(1)
With xlApp.Cells
.Find(What:=Left(MyPAS, 4), After:=.Cells(2, 3), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
With xlApp.ActiveCell
.Offset(rowOffset:=0, columnOffset:=2).Select
If Activecell.Value = "" Then
MyPAS = "MPF - "
Else
MyPAS = ActiveCell.Value + " - "
End If
End With

Line1: End With
xlApp.Workbooks.Close
End With
xlApp.Quit
GoTo DriverExit

DriverExit:
Set xlWB = Nothing
Set xlApp = Nothing
Exit Function

Error_Handler:
If Err.Number = 91 Then
MyPAS = xlApp.ActiveCell.Value + " - "
GoTo Line1
End If

End Function

************END CODE*****************

 

[PHV]

I'd replace this:
Activecell.Value
with this:
xlApp.ActiveCell.Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MakeItSo]

I wouldn't, PH:

newestAF: you're win a "With xlApp.ActiveCell" block.
just replace

 If Activecell.Value = "" Then

with

 If .Value = "" Then

;-)

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[newestAF]

Didn't work. I think my biggest problem is the err 91 runtime error. The code won't catch the "" value the first time around but the errorhandler for err 91 gives the .find a "" value everytime afterwards. I'm way over my head on this one.

 

[PHV]

I wouldn't, PH:
Despite the .Offset(...).Select ?

 

[SkipVought]

Thr confusion come that a SELECTION is made and a NEW ActiveCell is the active cell. I avoid ActiveCell in instances like this. You never actually use startcell, so...

With xlWB.Worksheets(1)
    With xlApp.Cells
        [b]Set startcell[/b] = .Find(What:=Left(MyPAS, 4), After:=.Cells(2, 3), LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
        [b]Set startcell[/b] = .Offset(rowOffset:=0, columnOffset:=2)
        With [b]startcell[/b]
            If .Value = "" Then
                MyPAS = "MPF - "
            Else
                MyPAS = .Value + " - "
            End If
        End With

Line1:
    End With
xlApp.Workbooks.Close
End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MakeItSo]

Yes, avoiding ActiveCell is certainly the way to go.
I checked and you are correct: ActiveCell within the With block is actually NOT the active cell but the cell which was active on entering the with block...
Weird logic...

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[SkipVought]

oops, had an error...

        Set startcell = startcell.Offset(rowOffset:=0, columnOffset:=2)
        With startcell
            If .Value = "" Then
                MyPAS = "MPF - "
            Else
                MyPAS = .Value + " - "
            End If
        End With

or even

        With startcell.Offset(rowOffset:=0, columnOffset:=2)
            If .Value = "" Then
                MyPAS = "MPF - "
            Else
                MyPAS = .Value + " - "
            End If
        End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[newestAF]

Skip,
You are the man. You saved me a lot of heartache. Tried to figure this out on my own for 3 days straight and you did it in no time.

For those who can use it, below is the final working code:

Function CheckAdminName()
On Error GoTo Error_Handler

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = CreateObject("excel.application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("C:\Documents and Settings\neil.west\Desktop\Product Runner PASADMIN.xls")
With xlWB.Worksheets(1)
With xlApp.Cells
Set startcell = .Find(What:=Left(MyPAS, 4), After:=.Cells(2, 3), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
Set startcell = startcell.Offset(rowOffset:=0, columnOffset:=2)
With startcell
If .Value = "" Then
MyPAS = "MPF - "
Else
MyPAS = .Value + " - "
End If
End With
Line1:
End With
xlApp.Workbooks.Close
End With
xlApp.Quit
GoTo DriverExit

DriverExit:
Set xlWB = Nothing
Set xlApp = Nothing
Exit Function

Error_Handler:
If Err.Number = 91 Then
If xlApp.ActiveCell.Value = "" Then
MyPAS = "MPF - "
Else
MyPAS = xlApp.ActiveCell.Value + " - "
End If

GoTo Line1
End If
If Not Err.Number = 91 Then
MsgBox Err.Source
End If

End Function