Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
very confusing
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
very confusing
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 -- Regards, OssieMac "Ihar" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
very confusing
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 -- jb "Ihar" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
very confusing
Thanks
This worked like a dream "OssieMac" wrote in message ... 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 -- Regards, OssieMac "Ihar" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Confusing spreadsheet | Excel Discussion (Misc queries) | |||
Confusing results | Excel Worksheet Functions | |||
Menu confusing | New Users to Excel | |||
V-/H- LOOKUP still confusing | Excel Worksheet Functions | |||
Help please! I'm confusing myself..... | Excel Programming |