Nicely phrased.
one further note:
I would "normally" use instr() because whenever you "interact" with the
spreadsheet, you sacrifice cpu cycles.
in this case, the Excel functions are written in "some" compiled language (not
sure which)
this compiled code requires the Excel worksheet "framework" to operate.
When you use VBA Worksheetfunction, you're basically taking one programming
environment (VBA) to load a separate environment (worksheet) to execute and
return values back through the stack.
In this case, the Worksheetfunction is returning a Worksheet error code, not a
VBA error code, which is not translating back to the original environment...
(this is not a technically accurate explanation, but it helps to grasp WHY there
is a problem handling the return codes)
If you switch to using "instr", you probably ought to also use ucase() to ensure
doing a case-independent search
and you no longer need the "on error resume next" statement.
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: Derek Turner <g4swy@yahoo.com>
To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
Sent: Wed, September 19, 2012 1:38:47 PM
Subject: Re: [ExcelVBA] help with "If IsError .. "
Dear HawtSweetie240
First of all may I suggest to you and anybody else posting on this forum that it
is most helpful to those of us sitting here poised ready to provide solutions
that you should always describe the error you are getting rather than just say
'I am basically getting an error '. David also asked reasonable questions as
your original posting contained other errors that confused us.
Paul made a very useful contribution in proposing that you use VB error
handling. My comment on that is that error trapping statements should surround
the one line you are focusing on rather than just being switched on at the start
of the routine and left operative. Otherwise you will end up puzzling over the
wrong error. (In real code you should also test the error to make sure it is
the one you expected in case something else is happening. I have not done this
for the sake of simplicity.)
Answering the original question, amazingly, the worksheet function Search throws
an error if the string being searched for is not present in the string being
searched. This may be OK in a spreadsheet but is not a good idea to make use of
in VB code
In the modification of Paul's code below I used the VB function Instr instead of
the original worksheet function Search. Note that its parameters are in a
different order from Search.
Instr returns zero if there is no match and that is easy to deal with. (Indeed
the only use of the error trap in this routine is to ignore error values such as
DIV 1/0 ! in the spreadsheet itself.)
In the code I do not test for the error itself as the value of nMultiplePosition
is zero until a match is found, so I test that.
Sub FindMultiple()
Dim nColumn As Long, nMultiplePosition As Long
For nColumn = 1 To Rows(1).SpecialCells(xlLastCell).Column
On Error Resume Next
nMultiplePosition = InStr(Cells(1, nColumn).Value, "Multiple")
On Error GoTo 0
If nMultiplePosition > 0 Then
Debug.Print "Multiple is in column"; nColumn; "at position ";
nMultiplePosition
Exit For
End If
Next nColumn
If nMultiplePosition = 0 Then
Debug.Print "Multiple not found"
End If
End Sub
Regards
Derek +++
>________________________________
> From: Paul Schreiner <schreiner_paul@att.net>
>To: ExcelVBA@yahoogroups.com
>Sent: Wednesday, 19 September 2012, 13:30
>Subject: Re: [ExcelVBA] help with "If IsError .. "
>
>
>
>It looks like your code is designed to search the column headings in row 1
>to find a cell that has the word "multiple" in it.
>
>There seems to be multiple problems.
>#1) While colF < 1
>I'm assuming you have set colF to a value prior to this section of code.
>In order for this to evaluate to TRUE, the column you're testing would have to
>be column number 0 or a negative.
>Which will never happen.
>
>#2) Since colF MUST be greater than 0, then your macro should've never even
>REACHED your search line.
>So, I must assume that you mis-keyed it when you posted it, and you actually
>are using While colF > 1
>But then, since you're increasing the column numbers (colF = colF + 1) then the
>macro will continue to run
>until you reach the maximum column (16384) then error out.
>unless you put in a exitloop when the string is found.
>
>#3) When the search doesn't find anything, the Worksheetfunction retruns a VBA
>Error/run time error.
>the ISerror function expects the value to be a numeric value that REPRESENTS
>an error.
>
>What I would do is use use VBA Error handler:
>--------------------------------------------------------
>On Error Resume Next
>For ColF = 1 to ActiveCell.SpecialCells(xlLastCell).Column
> Err.Clear
> Multiple = Application.WorksheetFunction.Search("Multiple", WkShtf.Cells(1,
>ColF).Value, 1)
> If (Err.Number = 0) Then
> Cells(ColF, 2).Select
> Exit For
> End If
>Next ColF
>-------------------------------------------------------
>Paul
>-----------------------------------------
>"Do all the good you can,
>By all the means you can,
>In all the ways you can,
>In all the places you can,
>At all the times you can,
>To all the people you can,
>As long as ever you can." - John Wesley
>-----------------------------------------
>
>It would be more helpful to know what the value of colF is on entry. It has
>to be less than 1 or the loop will be skipped. But you are using it as the
>second parameter for a call to Cells(). This must be a column number so it
>must be an integer, 1 or greater. But we can only get there is colF is less
>than one.
>
>Then there is a zero, all on its own. Don't know what that is doing.
>
>Then colF is increased by 1 so it is not going through the loop again unless
>it started as a negative.
>
>It would really be better to paste the whole sub (if it is not too long) so
>that we can see the Dim statements for the variables, etc.
>
>Best Regards
>David Grugeon
>Excel VBA Group Moderator
> ________________________________
>From: HawtSweetie240 <tapmba@yahoo.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Tue, September 18, 2012 7:17:00 PM
>Subject: [ExcelVBA] help with "If IsError .. "
>
>
>Could some one please help me find the issue that is causing error in the part
>of the code below? I am basically getting error is the If IsError part of the
>code
>
>Do While colF < 1
>If IsError(WorksheetFunction.Search("Multiple", wkShtF.Cells(1, colF), 1)) Then
>0
>colF = colF + 1
>Else
>multiple = WorksheetFunction.Search("Multiple", wkShtF.Cells(1, colF), 1)
>colF = colF + 1
>End If
>Loop
>
>Thank you in advance
>
>[Non-text portions of this message have been removed]
>
>
>
>
>
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar