Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
object variable error message
I am attempting to use the following routine to identify all of the matches
between two columns. rngA contains the short-name data and rngB contains text which includes one short-name within it. Once identified, the short-name is to be pasted in the appropriate cell to the right of the text containing it. In its present form, I can step through to "rng = rngB.Find...". at this point comes the "Object variable or With block variable not set." I have tried dozens of fixes which either only result in the same message or cause a different one. The fix is probably so simple I cannot see it so I need some help. Any and all suggestions will be appreciated. Thanks in advance, Jim -- Pops Jackson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
object variable error message
Since rng (or rngA or rngB) are objects (they represent ranges--with all the
range properties and methods), you need to use the Set statement: Set rng = rngb.find(... Pops Jackson wrote: I am attempting to use the following routine to identify all of the matches between two columns. rngA contains the short-name data and rngB contains text which includes one short-name within it. Once identified, the short-name is to be pasted in the appropriate cell to the right of the text containing it. In its present form, I can step through to "rng = rngB.Find...". at this point comes the "Object variable or With block variable not set." I have tried dozens of fixes which either only result in the same message or cause a different one. The fix is probably so simple I cannot see it so I need some help. Any and all suggestions will be appreciated. Thanks in advance, Jim -- Pops Jackson -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
object variable error message
I told you it was so simple I could not see it! Thanks, Dave! It works
perfectly now. -- Pops Jackson "Dave Peterson" wrote: Since rng (or rngA or rngB) are objects (they represent ranges--with all the range properties and methods), you need to use the Set statement: Set rng = rngb.find(... Pops Jackson wrote: I am attempting to use the following routine to identify all of the matches between two columns. rngA contains the short-name data and rngB contains text which includes one short-name within it. Once identified, the short-name is to be pasted in the appropriate cell to the right of the text containing it. In its present form, I can step through to "rng = rngB.Find...". at this point comes the "Object variable or With block variable not set." I have tried dozens of fixes which either only result in the same message or cause a different one. The fix is probably so simple I cannot see it so I need some help. Any and all suggestions will be appreciated. Thanks in advance, Jim -- Pops Jackson -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
object variable error message
Dave,
As I stated in my earlier reply, the routine is now working, but not perfectly as I thought. It captures two matches of a particular name but skips the other five to go on to the next one. I goes through the source list but just does not find all the matches. I have checked for "matchability" and see no reason for its not catching them. Do you have any ideas on this? I did try a number of things but either came up with no matches or had a loop which could not find the escape route. Thanks, Jim -- Pops Jackson "Dave Peterson" wrote: Since rng (or rngA or rngB) are objects (they represent ranges--with all the range properties and methods), you need to use the Set statement: Set rng = rngb.find(... Pops Jackson wrote: I am attempting to use the following routine to identify all of the matches between two columns. rngA contains the short-name data and rngB contains text which includes one short-name within it. Once identified, the short-name is to be pasted in the appropriate cell to the right of the text containing it. In its present form, I can step through to "rng = rngB.Find...". at this point comes the "Object variable or With block variable not set." I have tried dozens of fixes which either only result in the same message or cause a different one. The fix is probably so simple I cannot see it so I need some help. Any and all suggestions will be appreciated. Thanks in advance, Jim -- Pops Jackson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
object variable error message
I don't have any specific guesses without seeing your code.
Could it be that your .find() is looking at xlwhole and you need xlpart? Matchcase is set correctly? And is the data ok? No leading/trailing/embedded spaces in the name (with xlwhole)? Pops Jackson wrote: Dave, As I stated in my earlier reply, the routine is now working, but not perfectly as I thought. It captures two matches of a particular name but skips the other five to go on to the next one. I goes through the source list but just does not find all the matches. I have checked for "matchability" and see no reason for its not catching them. Do you have any ideas on this? I did try a number of things but either came up with no matches or had a loop which could not find the escape route. Thanks, Jim -- Pops Jackson "Dave Peterson" wrote: Since rng (or rngA or rngB) are objects (they represent ranges--with all the range properties and methods), you need to use the Set statement: Set rng = rngb.find(... Pops Jackson wrote: I am attempting to use the following routine to identify all of the matches between two columns. rngA contains the short-name data and rngB contains text which includes one short-name within it. Once identified, the short-name is to be pasted in the appropriate cell to the right of the text containing it. In its present form, I can step through to "rng = rngB.Find...". at this point comes the "Object variable or With block variable not set." I have tried dozens of fixes which either only result in the same message or cause a different one. The fix is probably so simple I cannot see it so I need some help. Any and all suggestions will be appreciated. Thanks in advance, Jim -- Pops Jackson -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
object variable error message
I added "for a = 1 to 20" and the routine picks everything up. There are
instances where the match is not "perfect" and we are having to adopt some strict naming conventions to make it work every time. If you have a better suggestion I would appreciate your passing it on. Sub abc() Workbooks.Open ("T:\Fxbckoff\FxStats\Fees Project\FXDH.xls") Windows("FXDH.xls").Activate Dim sAddr As String Dim rngA As Range, rngB As Range Dim rng As Range, cell As Range Dim res As Variant ActiveSheet.Range("E3:E1000").Select Selection.Copy Sheets("Sheet1").Activate ActiveSheet.Range("C2").Select ActiveCell.PasteSpecial Application.CutCopyMode = False Sheets("Sheet1").Range("B2").Select With Worksheets("Sheet1") Set rngA = .Range(.Cells(2, 2), .Cells(2, 2).End(xlDown)) Set rngB = .Range(.Cells(2, 3), .Cells(2, 3).End(xlDown)) End With For Each cell In rngA Set rng = rngB.Find(cell.Value, _ After:=rngB(rngB.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then sAddr = rng.Address For a = 1 To 20 Do rng.Font.Color = RGB(255, 0, 0) rng.Font.Bold = True rng.Offset(0, 0) = cell.Value Set rng = rngB.FindNext(rng) Loop While rng.Address < sAddr Next a End If Next Sheets("Sheet1").Range("B2").Select End Sub Thanks, Jim -- Pops Jackson "Dave Peterson" wrote: I don't have any specific guesses without seeing your code. Could it be that your .find() is looking at xlwhole and you need xlpart? Matchcase is set correctly? And is the data ok? No leading/trailing/embedded spaces in the name (with xlwhole)? Pops Jackson wrote: Dave, As I stated in my earlier reply, the routine is now working, but not perfectly as I thought. It captures two matches of a particular name but skips the other five to go on to the next one. I goes through the source list but just does not find all the matches. I have checked for "matchability" and see no reason for its not catching them. Do you have any ideas on this? I did try a number of things but either came up with no matches or had a loop which could not find the escape route. Thanks, Jim -- Pops Jackson "Dave Peterson" wrote: Since rng (or rngA or rngB) are objects (they represent ranges--with all the range properties and methods), you need to use the Set statement: Set rng = rngb.find(... Pops Jackson wrote: I am attempting to use the following routine to identify all of the matches between two columns. rngA contains the short-name data and rngB contains text which includes one short-name within it. Once identified, the short-name is to be pasted in the appropriate cell to the right of the text containing it. In its present form, I can step through to "rng = rngB.Find...". at this point comes the "Object variable or With block variable not set." I have tried dozens of fixes which either only result in the same message or cause a different one. The fix is probably so simple I cannot see it so I need some help. Any and all suggestions will be appreciated. Thanks in advance, Jim -- Pops Jackson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
object variable error message
I don't have any good way to do that kind of close match.
But I think I'd drop the counting loop and just keep looking until I couldn't find that value. I've made other completely arbitrary changes to this. I like each of my Dim's on separate lines. RngA points at column B RngB points at column C That doesn't bother VBA/excel, but it surely confuses me. I changed the names to RngB (points at B) and RngC (points at C). I like to use the variable FoundCell to represent the Found cell. I find it easier to understand when I come back later. I also changed sAddr to FirstAddress--again, just because it makes more sense when I'm reading the code. I don't like the variable Cell, either. I use myCell. (Cell isn't a reserved word, but it's darn close to .cells().) You rely on the correct sheet to be active when you open that workbook. I don't like to rely on that. I'd specify the worksheet in the code. This is untested, but it did compile: Option Explicit Sub abc() Dim FirstAddress As String Dim rngB As Range Dim rngC As Range Dim FoundCell As Range Dim myCell As Range 'Dim res As Variant 'not used ???? Dim FXDHWkbk As Workbook Set FXDHWkbk = Workbooks.Open("T:\Fxbckoff\FxStats\Fees Project\FXDH.xls") 'I wouldn't depend on the activesheet being the correct sheet With FXDHWkbk .Worksheets("sheet99").Range("E3:E1000").Copy _ Destination:=.Worksheets("Sheet1").Range("C2") Application.CutCopyMode = False With .Worksheets("Sheet1") Set rngB = .Range(.Cells(2, 2), .Cells(2, 2).End(xlDown)) Set rngC = .Range(.Cells(2, 3), .Cells(2, 3).End(xlDown)) End With For Each myCell In rngB.Cells With rngC Set FoundCell = .Cells.Find(myCell.Value, _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then 'do nothing Else FirstAddress = FoundCell.Address Do FoundCell.Font.Color = RGB(255, 0, 0) FoundCell.Font.Bold = True FoundCell.Value = myCell.Value Set FoundCell = .FindNext(FoundCell) If FoundCell Is Nothing Then Exit Do End If If FoundCell.Address = FirstAddress Then Exit Do End If Loop End If End With Next myCell Application.Goto .Worksheets("Sheet1").Range("B2"), scroll:=True End With End Sub Most of the changes were not necessary. But I would be careful with that activesheet stuff. Pops Jackson wrote: I added "for a = 1 to 20" and the routine picks everything up. There are instances where the match is not "perfect" and we are having to adopt some strict naming conventions to make it work every time. If you have a better suggestion I would appreciate your passing it on. Sub abc() Workbooks.Open ("T:\Fxbckoff\FxStats\Fees Project\FXDH.xls") Windows("FXDH.xls").Activate Dim sAddr As String Dim rngA As Range, rngB As Range Dim rng As Range, cell As Range Dim res As Variant ActiveSheet.Range("E3:E1000").Select Selection.Copy Sheets("Sheet1").Activate ActiveSheet.Range("C2").Select ActiveCell.PasteSpecial Application.CutCopyMode = False Sheets("Sheet1").Range("B2").Select With Worksheets("Sheet1") Set rngA = .Range(.Cells(2, 2), .Cells(2, 2).End(xlDown)) Set rngB = .Range(.Cells(2, 3), .Cells(2, 3).End(xlDown)) End With For Each cell In rngA Set rng = rngB.Find(cell.Value, _ After:=rngB(rngB.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng Is Nothing Then sAddr = rng.Address For a = 1 To 20 Do rng.Font.Color = RGB(255, 0, 0) rng.Font.Bold = True rng.Offset(0, 0) = cell.Value Set rng = rngB.FindNext(rng) Loop While rng.Address < sAddr Next a End If Next Sheets("Sheet1").Range("B2").Select End Sub Thanks, Jim -- Pops Jackson "Dave Peterson" wrote: I don't have any specific guesses without seeing your code. Could it be that your .find() is looking at xlwhole and you need xlpart? Matchcase is set correctly? And is the data ok? No leading/trailing/embedded spaces in the name (with xlwhole)? Pops Jackson wrote: Dave, As I stated in my earlier reply, the routine is now working, but not perfectly as I thought. It captures two matches of a particular name but skips the other five to go on to the next one. I goes through the source list but just does not find all the matches. I have checked for "matchability" and see no reason for its not catching them. Do you have any ideas on this? I did try a number of things but either came up with no matches or had a loop which could not find the escape route. Thanks, Jim -- Pops Jackson "Dave Peterson" wrote: Since rng (or rngA or rngB) are objects (they represent ranges--with all the range properties and methods), you need to use the Set statement: Set rng = rngb.find(... Pops Jackson wrote: I am attempting to use the following routine to identify all of the matches between two columns. rngA contains the short-name data and rngB contains text which includes one short-name within it. Once identified, the short-name is to be pasted in the appropriate cell to the right of the text containing it. In its present form, I can step through to "rng = rngB.Find...". at this point comes the "Object variable or With block variable not set." I have tried dozens of fixes which either only result in the same message or cause a different one. The fix is probably so simple I cannot see it so I need some help. Any and all suggestions will be appreciated. Thanks in advance, Jim -- Pops Jackson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error '91' Object variable or With block variable not set | Excel Discussion (Misc queries) | |||
Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
Getting inconsistent Error 91-Object variable or With block variable not set | Excel Programming | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming |