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

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

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
>

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
>

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
>

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

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
>

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