I need to look for a specific text string within a spreadsheet then, if it
isn't found, look for another text string.

The problem I have with the existing code is that if the 1st search string
(SD) isn't found, it doesn't look for the second string. If is DOES find the
first string, it goes on to look for the second string anyway (currently
commented out). This wouldn't be a problem if the second string exists, but
if it doesn't then the following doesn't execute.

I know I'm going about this the wrong way, but I'm not sure how to remedy
the situation. Any ideas?

My code is below:

Private Sub CheckVersion(sTPPath, sUpdRoot, dVer, sVerRep, sCurPath,
sUpdFile)
Dim lLoc As Long
Dim dNewVer As Double
' Checks version of existing template
With Application
.EnableEvents = False
Workbooks.Open Filename:= _
sTPPath & "\" & sUpdRoot & ".xlt", _
UpdateLinks:=0, Editable:=True
On Error GoTo noSD
.Cells.Find(What:="SD", After:=.ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
' ***** Second string find to go here *****
' On Error GoTo noSD
' .Cells.Find(What:=sUpdRoot & " v", After:=.ActiveCell,
LookIn:=xlValues, LookAt:= _
' xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
' False).Activate
lLoc = InStrRev(.ActiveCell.Value, "v")
dVer = Val(Right(.ActiveCell.Value, Len(.ActiveCell.Value) - lLoc))
sUpdFile = Dir(sCurPath & "\" & sUpdRoot & "*.xlt")
sUpdFile = Left(sUpdFile, Len(sUpdFile) - 4)
lLoc = InStrRev(sUpdFile, "v")
dNewVer = Val(Right(sUpdFile, Len(sUpdFile) - lLoc))
If dNewVer <= dVer Then
sVerRep = "Failure - Update version is the same or older than
existing template"
Else
sVerRep = "Current version is " & dVer
End If
.Visible = True
.EnableEvents = True
End With
GoTo subend
noSD:
sVerRep = "Failure - Unable to update from this version"
subend:
End Sub


Many thanks.

---
Ian
---

Untested ...

Private Sub CheckVersion(sTPPath, sUpdRoot, dVer, sVerRep, sCurPath,
sUpdFile)
Dim lLoc As Long
Dim dNewVer As Double
Dim cell As Range
Dim cell2 As Range
' Checks version of existing template
With Application

.EnableEvents = False

Workbooks.Open Filename:= _
sTPPath & "\" & sUpdRoot & ".xlt", _
UpdateLinks:=0, Editable:=True

On Error Resume Next
Set cell = .Cells.Find(What:="SD", _
After:=.ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
On Error GoTo 0
If Not cell Is Nothing Then
On Error Resume Next
Set cell2 = .Cells.Find(What:=sUpdRoot & " v", _
After:=.ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
On Error GoTo 0
If Not cell2 Is Nothing Then Set cell = cell2

lLoc = InStrRev(cell.Value, "v")
dVer = Val(Right(cell.Value, Len(cell.Value) - lLoc))
sUpdFile = Dir(sCurPath & "\" & sUpdRoot & "*.xlt")
sUpdFile = Left(sUpdFile, Len(sUpdFile) - 4)
lLoc = InStrRev(sUpdFile, "v")
dNewVer = Val(Right(sUpdFile, Len(sUpdFile) - lLoc))
If dNewVer <= dVer Then
sVerRep = "Failure - Update version is the same or older
than existing template"
Else
sVerRep = "Current version is " & dVer
End If
End If

.Visible = True
.EnableEvents = True
End With
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"IanC" wrote in message
...
I need to look for a specific text string within a spreadsheet then, if it
isn't found, look for another text string.

The problem I have with the existing code is that if the 1st search string
(SD) isn't found, it doesn't look for the second string. If is DOES find
the first string, it goes on to look for the second string anyway
(currently commented out). This wouldn't be a problem if the second string
exists, but if it doesn't then the following doesn't execute.

I know I'm going about this the wrong way, but I'm not sure how to remedy
the situation. Any ideas?

My code is below:

Private Sub CheckVersion(sTPPath, sUpdRoot, dVer, sVerRep, sCurPath,
sUpdFile)
Dim lLoc As Long
Dim dNewVer As Double
' Checks version of existing template
With Application
.EnableEvents = False
Workbooks.Open Filename:= _
sTPPath & "\" & sUpdRoot & ".xlt", _
UpdateLinks:=0, Editable:=True
On Error GoTo noSD
.Cells.Find(What:="SD", After:=.ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
' ***** Second string find to go here *****
' On Error GoTo noSD
' .Cells.Find(What:=sUpdRoot & " v", After:=.ActiveCell,
LookIn:=xlValues, LookAt:= _
' xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
' False).Activate
lLoc = InStrRev(.ActiveCell.Value, "v")
dVer = Val(Right(.ActiveCell.Value, Len(.ActiveCell.Value) - lLoc))
sUpdFile = Dir(sCurPath & "\" & sUpdRoot & "*.xlt")
sUpdFile = Left(sUpdFile, Len(sUpdFile) - 4)
lLoc = InStrRev(sUpdFile, "v")
dNewVer = Val(Right(sUpdFile, Len(sUpdFile) - lLoc))
If dNewVer <= dVer Then
sVerRep = "Failure - Update version is the same or older than
existing template"
Else
sVerRep = "Current version is " & dVer
End If
.Visible = True
.EnableEvents = True
End With
GoTo subend
noSD:
sVerRep = "Failure - Unable to update from this version"
subend:
End Sub


Many thanks.

---
Ian
---

Thanks, Bob. It needed a bit of modification, but it works fine now :-)

--
Ian
--
"Bob Phillips" wrote in message
...
Untested ...

Private Sub CheckVersion(sTPPath, sUpdRoot, dVer, sVerRep, sCurPath,
sUpdFile)
Dim lLoc As Long
Dim dNewVer As Double
Dim cell As Range
Dim cell2 As Range
' Checks version of existing template
With Application

.EnableEvents = False

Workbooks.Open Filename:= _
sTPPath & "\" & sUpdRoot & ".xlt", _
UpdateLinks:=0, Editable:=True

On Error Resume Next
Set cell = .Cells.Find(What:="SD", _
After:=.ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
On Error GoTo 0
If Not cell Is Nothing Then
On Error Resume Next
Set cell2 = .Cells.Find(What:=sUpdRoot & " v", _
After:=.ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
On Error GoTo 0
If Not cell2 Is Nothing Then Set cell = cell2

lLoc = InStrRev(cell.Value, "v")
dVer = Val(Right(cell.Value, Len(cell.Value) - lLoc))
sUpdFile = Dir(sCurPath & "\" & sUpdRoot & "*.xlt")
sUpdFile = Left(sUpdFile, Len(sUpdFile) - 4)
lLoc = InStrRev(sUpdFile, "v")
dNewVer = Val(Right(sUpdFile, Len(sUpdFile) - lLoc))
If dNewVer <= dVer Then
sVerRep = "Failure - Update version is the same or older
than existing template"
Else
sVerRep = "Current version is " & dVer
End If
End If

.Visible = True
.EnableEvents = True
End With
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"IanC" wrote in message
...
I need to look for a specific text string within a spreadsheet then, if it
isn't found, look for another text string.

The problem I have with the existing code is that if the 1st search
string (SD) isn't found, it doesn't look for the second string. If is
DOES find the first string, it goes on to look for the second string
anyway (currently commented out). This wouldn't be a problem if the
second string exists, but if it doesn't then the following doesn't
execute.

I know I'm going about this the wrong way, but I'm not sure how to remedy
the situation. Any ideas?

My code is below:

Private Sub CheckVersion(sTPPath, sUpdRoot, dVer, sVerRep, sCurPath,
sUpdFile)
Dim lLoc As Long
Dim dNewVer As Double
' Checks version of existing template
With Application
.EnableEvents = False
Workbooks.Open Filename:= _
sTPPath & "\" & sUpdRoot & ".xlt", _
UpdateLinks:=0, Editable:=True
On Error GoTo noSD
.Cells.Find(What:="SD", After:=.ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
' ***** Second string find to go here *****
' On Error GoTo noSD
' .Cells.Find(What:=sUpdRoot & " v", After:=.ActiveCell,
LookIn:=xlValues, LookAt:= _
' xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
' False).Activate
lLoc = InStrRev(.ActiveCell.Value, "v")
dVer = Val(Right(.ActiveCell.Value, Len(.ActiveCell.Value) -
lLoc))
sUpdFile = Dir(sCurPath & "\" & sUpdRoot & "*.xlt")
sUpdFile = Left(sUpdFile, Len(sUpdFile) - 4)
lLoc = InStrRev(sUpdFile, "v")
dNewVer = Val(Right(sUpdFile, Len(sUpdFile) - lLoc))
If dNewVer <= dVer Then
sVerRep = "Failure - Update version is the same or older than
existing template"
Else
sVerRep = "Current version is " & dVer
End If
.Visible = True
.EnableEvents = True
End With
GoTo subend
noSD:
sVerRep = "Failure - Unable to update from this version"
subend:
End Sub


Many thanks.

---
Ian
---