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]
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