very confusing

[Ihar]

Hi

I have a module in a spreadsheet which executes a piece of code, see below

Set wRep = Worksheets("Report to Region")
wRep.Activate
Set R = wRep.Range("A20:A46")
R.Activate
enc = R.Find(What:=sIprojNo, After:=R.Cells(1, 1), LookIn:=xlValues,
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
If enc = False Then
nRows = WorksheetFunction.CountA(R) + 1
R.Cells(nRows, 1) = sIprojNo
R.Cells(nRows, 2).Select
End If

Which works perfectly, later in the same spreadsheet, in a user form, on a
button to save the data that may be captures, I need to do the same data
check and paste the relevant field, if needed to the end of the list so I
copies and pasted the code andit doesnot work, see below

Set wRep = Worksheets("Report to Region")
wRep.Activate
Set R = wRep.Range("A20:A46")
R.Activate
enc = R.Find(What:=sIprojNo, After:=R.Cells(1, 1), LookIn:=xlValues,
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
If enc = False Then
nRows = WorksheetFunction.CountA(R) + 1
R.Cells(nRows, 1) = sIprojNo
R.Cells(nRows, 2).Select
End If

This same code now returns a Run Time Error '91':

Object Variable or With block variable not set

I use Option Explicit so it cant be a variable, what else could it be, any
ideas or suggestions are most welcome.

in both instances, the variables are defined as follows

Dim sIprojNo As String
Dim wRep As Worksheet
Dim R As Range
Dim enc As Boolean
Dim nRows As Integer

 

[OssieMac]

Remove all the Activate and Select. Cano only perform those commands if it is
the active sheet.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

Setting the find to a range variable allows you to get rid of the select at
the end of the Find function.

After:=R.Cells(R.Cells.Count) forces find to start looking in first cell.
After:=R.Cells(1, 1) actually is just that; After the first cell and find
starts looking from second cell and does not find the first cell until after
it has looked at the last cell.

Sub TestFind()
Dim wRep As Worksheet
Dim R As Range
Dim enc As Range
Dim sIprojNo As Long
Dim nRows As Long

sIprojNo = 54321 'Dummy value for test

Set wRep = Worksheets("Report to Region")

With wRep
Set R = .Range("A20:A46")
End With

Set enc = R.Find(What:=sIprojNo, _
After:=R.Cells(R.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not enc Is Nothing Then
'sIprojNo found
Else
'sIprojNo NOT found
'Assume this code is for NOT found
nRows = WorksheetFunction.CountA(R) + 1
R.Cells(nRows, 1) = sIprojNo
End If

End Sub

 

[john]

see if this helps:

Set wRep = Worksheets("Report to Region")

Set R = wRep.Range("A20:A46")

Set enc = R.Find(What:=sIprojNo, After:=R.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If enc Is Nothing Then
nRows = WorksheetFunction.CountA(R) + 1
R.Cells(nRows, 1) = sIprojNo
R.Cells(nRows, 2).Select
End If

 

[Ihar]

Thanks

This worked like a dream

Remove all the Activate and Select. Cano only perform those commands if it
is
the active sheet.

Note that a space and underscore at the end of a line is a line break in
an
otherwise single line of code.

Setting the find to a range variable allows you to get rid of the select
at
the end of the Find function.

After:=R.Cells(R.Cells.Count) forces find to start looking in first cell.
After:=R.Cells(1, 1) actually is just that; After the first cell and find
starts looking from second cell and does not find the first cell until
after
it has looked at the last cell.

Sub TestFind()
Dim wRep As Worksheet
Dim R As Range
Dim enc As Range
Dim sIprojNo As Long
Dim nRows As Long

sIprojNo = 54321 'Dummy value for test

Set wRep = Worksheets("Report to Region")

With wRep
Set R = .Range("A20:A46")
End With

Set enc = R.Find(What:=sIprojNo, _
After:=R.Cells(R.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not enc Is Nothing Then
'sIprojNo found
Else
'sIprojNo NOT found
'Assume this code is for NOT found
nRows = WorksheetFunction.CountA(R) + 1
R.Cells(nRows, 1) = sIprojNo
End If

End Sub