• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

DO.. Loop until, not working.

M

Mange08

New Member
Hi,
I have a sheet with formulas that sometimes get an #N/A as result.
I wanted to write a macro that finds those values when i activate the sheet, tells me to give the correct number and then copy the correct value to another sheet for future reference.
The macro works as intended, although poorly written, BUT, it only works once.
The loop doesn't work even if there is more #N/A to change.
What have i missed?

Code: 
Private Sub Worksheet_Activate()
  Dim rngToSearch As Range
  Dim rngFound As Range

  Dim strFirst As String
  Dim msgResult As VbMsgBoxResult

  Set tgt = ThisWorkbook.Sheets("TPS saknade priser")
 
  lastRow = Range("A" & Rows.Count).End(xlUp).Row
  Set rngToSearch = Range("G1:G" & lastRow)
  Set rngFound = rngToSearch.Find(What:="#N/A", _
  LookAt:=xlWhole, _
  LookIn:=xlValues, _
  MatchCase:=True)
  If rngFound Is Nothing Then
  Exit Sub
  Else
  strFirst = rngFound.Address
  Do
  msgResult = MsgBox("#NA i cell " & rngFound.Address & vbCrLf & "Rätta värdet?( Ja / Nej )", vbYesNo)
  If msgResult = vbYes Then

  rngFound.Select
  rngFound = InputBox("Ange värde")
  rngFound.Offset(, 1) = rngFound

  'Kopiera nya värdet
  Set copyR = rngFound
  Set copyMat = rngFound.Offset(, -4)
  copyMat.Copy tgt.Range("A65636").End(xlUp).Offset(1, 0)
  copyR.Copy tgt.Range("B65536").End(xlUp).Offset(1, 0)

  'Räkna ut nya värdet samt ta bort formeln
  ActiveCell.Offset(, -1).FormulaR1C1 = "=RC[-1]*RC[1]"
  ActiveCell.Previous.Select
  Selection.Copy
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
   
  Exit Sub
  ElseIf msgResult = vbNo Then
  Exit Sub
   
  Else
  Set rngFound = rngToSearch.FindNext(rngFound)
  End If
  Loop Until rngFound.Address = strFirst
  End If
End Sub
 
Hi Mange,

looks like that either way if you press yes or no, you have the Exit Sub command, which will stop the code. Try removing both lines of Exit Sub.

Additionally you need to take the
Code: 
Set rngFound = rngToSearch.FindNext(rngFound)
Out of the If clause - I assume you want to move forward to the next value regardless of your yes/no choice earlier.

BR
Nicholas
 
Hi,

thanks for your reply and suggestion.
I didn't got it to work though.
It ended after first match anyway.

Br
Magnus
 
Hi Mange,

the example file by Narayan looks good.

I would add another line of code before the loop though. Otherwise you will get an error once you have fixed all the N/A Errors.

Code: 
If rngFound Is Nothing Then Exit Sub
  Loop Until rngFound.Address = strFirst

I could not find Narayan's comments, but here are mine.

I would consider if it is really necessary to give the user a choice of Yes/No for every item - I assume that you want all #N/A changed by the user?
You could skip that and only show the input box. Or you could simply hide all rows, that do not contain N/A and the user fixes directly in excel... choices, choices :)

Hälsningar från Hamburg ;)
 
Hi ,

This is what the Excel help on the FindNext method says :
When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address.
Click to expand...
Narayan
 
Thanks to you both,
Narayan example worked out well with your addition.

Good feedback regarding the user choice as well. Will change that too.

BR
Magnus
 
Hi ,

The check you mention is not for the FindNext method ; it should be for the Find method , so that if there is no instance found , no processing takes place.

The check may be missing because it was not there in the original file , and I have not added it ; I have not added it because the question was why the code does not find the second and other instances ; my response was only with reference to this. I did not go through the code to see what other corrections could be made.

Narayan
 
You must log in or register to reply here.
Back
Top