On Error Select Row

[helpexcel]

Hi - I have code that pulls data from one sheet and puts it into another. It matches certain values to determine which row to put the data, however sometimes the values won't match exactly and it errors out. Is there a way to put code in that says on error ask user to select row? thanks!

 

[Joe4]

I think we would need to see your code.

 

[helpexcel]

Here's the code

Dim sPath As StringDim sFile


Dim ws1 As Worksheet
Set ws1 = Sheet1


MsgBox "Make sure name has already been added to the list"


sPath = "\\Server\File"


If SetFilePath(sPath) = 0 Then
    MsgBox "Error in setting the path - " & sPath
Else
    sFile = Application.GetOpenFilename("Excel,*.xls;*.xlsx")
End If


If sFile <> False Then
    Set WB = Workbooks.Open(sFile)
Else
    MsgBox "No File Selected"
        Exit Sub
End If


Set Ws2 = WB.Sheet1
Set Nam = WB.Sheet1.Range("A1")


With Sheet1
    Set found = .Range("A:A").Find(what:=Nam, LookIn:=xlValues, lookat:=xlWhole)
End With


With Ws2
  .Range("C10").Copy
  ws1.Range("B" & found.Row).PasteSpecial xlPasteValues
    .Range("D25").Copy
    ws1.Range("C" & found.Row).PasteSpecial xlPasteValues
    .Range("E15").Copy
    ws1.Range("D" & found.Row).PasteSpecial xlPasteValues
End With


WB.Close False

 

[Joe4]

It matches certain values to determine which row to put the data, however sometimes the values won't match exactly and it errors out.

I don't see anything in your code that is doing anything like this.
Where exactly is this matching occurring?

 

[helpexcel]

[COLOR=#333333] Set found = .Range("A:A").Find(what:=Nam, LookIn:=xlValues, lookat:=xlWhole)[/COLOR]

 

[Joe4]

OK, I see now.

Try replacing this part of your code:

With Sheet1
    Set found = .Range("A:A").Find(what:=Nam, LookIn:=xlValues, lookat:=xlWhole)
End With

With Ws2
  .Range("C10").Copy
  ws1.Range("B" & found.Row).PasteSpecial xlPasteValues
    .Range("D25").Copy
    ws1.Range("C" & found.Row).PasteSpecial xlPasteValues
    .Range("E15").Copy
    ws1.Range("D" & found.Row).PasteSpecial xlPasteValues
End With

with this:

If IsError(Sheet1.Range("A:A").Find(what:=Nam, LookIn:=xlValues, lookat:=xlWhole)) Then
    fRow = InputBox("Value not found, enter the row number manually")
Else
    fRow = Sheet1.Range("A:A").Find(what:=Nam, LookIn:=xlValues, lookat:=xlWhole).Row
End If


With Ws2
  .Range("C10").Copy
  ws1.Range("B" & fRow).PasteSpecial xlPasteValues
    .Range("D25").Copy
    ws1.Range("C" & fRow).PasteSpecial xlPasteValues
    .Range("E15").Copy
    ws1.Range("D" & fRow).PasteSpecial xlPasteValues
End With

I haven't tested it myself, because I haven't tried recreating your scenario.
If it doesn't work right, then I will take the time to do that.

 

[helpexcel]

It's not recognizing its an error in the IsError statement and breaks after the Else.

 

[Fluff]

How about

With Ws2
   Set found = .Range("A:A").Find(what:=Nam, lookIn:=xlValues, lookat:=xlWhole)
   If found Is Nothing Then
      frow = InputBox("Value not found, enter the row number manually")
   Else
      frow = found.Row
   End If
  .Range("C10").Copy
  Ws1.Range("B" & frow).PasteSpecial xlPasteValues
    .Range("D25").Copy
    Ws1.Range("C" & frow).PasteSpecial xlPasteValues
    .Range("E15").Copy
    Ws1.Range("D" & frow).PasteSpecial xlPasteValues
End With

 

[Joe4]

OK, in doing the testing, it appears that part is not returning the error. It just sets found to be an empry range.
The error occurs when we try to take the Row property of it.

So, replace that part of the code with this instead:

With Sheet1
    Set found = .Range("A:A").Find(what:=Nam, LookIn:=xlValues, lookat:=xlWhole)
End With

Dim fRow As Long

If found Is Nothing Then
    fRow = InputBox("Value not found, enter the row number manually")
Else
    fRow = found.Row
End If

With Ws2
  .Range("C10").Copy
  ws1.Range("B" & fRow).PasteSpecial xlPasteValues
    .Range("D25").Copy
    ws1.Range("C" & fRow).PasteSpecial xlPasteValues
    .Range("E15").Copy
    ws1.Range("D" & fRow).PasteSpecial xlPasteValues
End With

 

[helpexcel]

@Fluff is was just coming back to post this code (I actually figured it out) - it works great. Thanks as always!! @Joe4 thanks for the help and getting me pointed in the right direction!!