Hi,
First of all let's discuss variable declaration because it's very important.
This line of code declares r and q as variants, and b as a string.
Similarly this line of code declares u as a variant as v as an integer.
If you're declaring multiple variables on the same line you need to specify each variable's type:
Rich (BB code):
Dim u As Integer, v As Integer
Dim r As String, q As String, b As String
There are 2 'exceptions' to this, but they are not relevant here.
Looking further down the code, the variable q is assigned a value "Total" which does not subsequently change. Instead of declaring q as a variable it could be declared as a constant.
Rich (BB code):
Const q As String = " Total"
Dim u As Integer, v As Integer
Dim r As String, b As String
We also see that u and v are being used in reference to row numbers. The number of rows on a worksheet (65536+) exceeds the maximum value an integer data type can hold (32767) so we should declare them as Long Integer data types to avoid the possibility of overflow errors:
Rich (BB code):
Const q As String = " Total"
Dim u As Long, v As Long
Dim r As String, b As String
Now let's tidy up the code a bit by removing the unnecessary Select/Selection methods/properties. We end up with something like this (now that the fromsht isn't activated some of the below code is inappropriate, but we will correct that in a moment):
Rich (BB code):
Sub assign_to_pp(tosht As String, fromsht As String)
Const q As String = " Total"
Dim u As Long, v As Long
Dim r As String, b As String
For u = 5 To 104
r = Sheets(tosht).Cells(u, 2).Value
If r <> "" Then
b = r & q
Sheets(fromsht).Cells.Find( _
What:=b, _
After:=Sheets(fromsht).Cells(2, 1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate
On Error GoTo unext
v = ActiveCell.Row
Sheets(tosht).Cells(u, 3).Value = Sheets(fromsht).Cells(v, 11).Value
Else
Sheets(tosht).Cells(u, 3).Value = ""
GoTo unext
End If
unext:
Next u
End Sub
This code is easier to read and analyse. There are two (argubly three) problems.
1. The line that's causing the error is here:
Rich (BB code):
Sheets(fromsht).Cells.Find( _
What:=b, _
After:=Sheets(fromsht).Cells(2, 1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate
The question to ask is, "What happens if the search value isn't found?"
The answer is the range.find() method returns Nothing. You can't activate Nothing, so you will get a runtime error in that scenario.
Therefore you need to introduce some defensive coding to cater for it.
2. Your Error handling structure has a problem.
You're using this Goto statement to branch to the below line in your procedure:
This is no good. You must use a Resume statement in your error handler to continue with the main part of your procedure otherwise the error handler will remain enabled and you will experience unexpected behaviour. I recommend you read this:
http://www.cpearson.com/excel/ErrorHandling.htm
Taking all the above into account I think we get to something like this:
Rich (BB code):
Sub assign_to_pp(tosht As String, fromsht As String)
Const q As String = " Total"
Dim u As Long, v As Long
Dim r As String, b As String
Dim rngFound As Range
For u = 5 To 104
r = Sheets(tosht).Cells(u, 2).Value
If r <> "" Then
b = r & q
Set rngFound = Sheets(fromsht).Cells.Find( _
What:=b, _
After:=Sheets(fromsht).Cells(2, 1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not rngFound Is Nothing Then
v = rngFound.Row
Sheets(tosht).Cells(u, 3).Value = Sheets(fromsht).Cells(v, 11).Value
End If
Else
Sheets(tosht).Cells(u, 3).Value = ""
End If
Next u
End Sub
Hope that helps...
