Unable to reference two workbooks, worksheets.

[andycapp28]

I have used the range coding before in a single Workbook.

I require to search Column J in Workbook/Worksheet named TY_Multi_Gifts.xls/TY_Multi_Gifts

Using the range for a single worksheet works fine in other macros, I just can't grasp how to make it work using Workbook and Worksheet names.

I get "invalid or unqualified reference" on the set rLookfor, area highlighted is the .[J1) when compiling.

If I comment it out to check for other compilation errors I get "subscript out of range" on the With Worksheets("TY_Certificate_Fund_Description"). I clearly misunderstand the use of the With.

I hope the solutions are'nt too obvious to be bothering everyone but its so frustrating and taking me too long to resolve.

Sub Get_TY_Certs_Desc()
Dim r As Range
Dim rLookfor As Range
Dim rLookin As Range
Dim curWB As Workbook, destWB As Workbook

    'With Application
        '.EnableEvents = False
        '.ScreenUpdating = False
    'End With

Set curWB = ActiveWorkbook
' Open File
Set destWB = Workbooks.Open("P:\TY_Certificates\TY_Multi_Gifts.xls")
Set destWS = Worksheets("TY_Multi_Gifts")

Set rLookfor = destWS.Range(.[J1], .[J1].End(xlDown))

With Worksheets("TY_Certificate_Fund_Description")
Set rLookin = .Range(.[A1], .[A1].End(xlDown))
End With

For Each c In rLookfor
Set r = rLookin.Find(a.Value)

If Not r Is Nothing Then
r.Offset(0, 2).Copy

c.Offset(0, 2).PasteSpecial xlPasteAll

End If

Next

    'With Application
        '.EnableEvents = True
        '.ScreenUpdating = True
    'End With
    
End Sub

 

[SkipVought]

hi,

Set curWB = ActiveWorkbook
' Open File
Set destWB = Workbooks.Open("P:\TY_Certificates\TY_Multi_Gifts.xls")
Set destWS = [b]destWB[/b].Worksheets("TY_Multi_Gifts")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

...
Set destWS = [!]destWB.[/!]Worksheets("TY_Multi_Gifts")
Set rLookfor = destWS.Range([!]destWS.Range("[/!]J1[!]")[/!], [!]destWS.Range("[/!]J1[!]")[/!].End(xlDown))
With [!]curWB.[/!]Worksheets("TY_Certificate_Fund_Description")
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[andycapp28]

Thanks skip and phv
I've progressed now to this error when running I get object required in the FOR loop on the set r line of code

Also just to confirm at the end how can I close both the workbooks?

Sub Get_TY_Certs_Desc()
Dim r As Range
Dim rLookfor As Range
Dim rLookin As Range
Dim curWB As Workbook, destWB As Workbook

'With Application
'.EnableEvents = False
'.ScreenUpdating = False
'End With

Set curWB = ActiveWorkbook
Set curWS = curWB.Worksheets("Sheet1")

' Open File
Set destWB = Workbooks.Open("P:\TY_Certificates\TY_Multi_Gifts.xls")
Set destWS = destWB.Worksheets("TY_Multi_Gifts")

Set rLookfor = destWS.Range(destWS.Range("J1"), destWS.Range("J1").End(xlDown))

Set rLookin = curWS.Range(curWS.Range("A1"), curWS.Range("A1").End(xlDown))

For Each c In rLookfor
Set r = rLookin.Find(a.Value)

If Not r Is Nothing Then
r.Offset(0, 2).Copy

c.Offset(0, 2).PasteSpecial xlPasteAll

End If

Next

'With Application
'.EnableEvents = True
'.ScreenUpdating = True
'End With

End Sub

 

[PHV]

What is a in a.value ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[andycapp28]

PHV,

I think this should be c.value which is what has been received from rLookfor.

I have added a Dim for c As range

I'm trying to test by using watch window but finding it difficult to see where I should be looing to find what is being searched for in c.value

 

[PHV]

So, did you replace this:
Set r = rLookin.Find(a.Value)
with this ?
Set r = rLookin.Find([!]c[/!].Value)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[andycapp28]

PHV,

I did as you said and seems to hang together until I have added an else.

In the scenario where my c.value is found in r.Lookin but my offset 2 is null(blank) then I want to use offset 1 instead. I have tried with an else but errors on
the else, gives object variable or with block variable not set.
Would you be able to understand this latest requirement and offer an how to do?

Many thanks

AC

Sub Get_TY_Certs_Desc()
Dim r As Range
Dim c As Range

Dim rLookfor As Range
Dim rLookin As Range
Dim curWB As Workbook, destWB As Workbook

    'With Application
        '.EnableEvents = False
        '.ScreenUpdating = False
    'End With

Set curWB = ActiveWorkbook
Set curWS = curWB.Worksheets("Sheet1")

' Open File
Set destWB = Workbooks.Open("P:\TY_Certificates\TY_Multi_Gifts.xls")
Set destWS = destWB.Worksheets("TY_Multi_Gifts")

With destWB.Worksheets("TY_Multi_Gifts")
Set rLookfor = destWS.Range(destWS.Range("I1"), destWS.Range("I1").End(xlDown))
End With

With curWB.Worksheets("Sheet1")

Set rLookin = curWS.Range(curWS.Range("A1"), curWS.Range("A1").End(xlDown))

End With

For Each c In rLookfor
Set r = rLookin.Find(c.Value)

If Not r Is Nothing Then
r.Offset(0, 2).Copy

c.Offset(0, 2).PasteSpecial xlPasteAll
Else
r.Offset(0, 1).Copy
c.Offset(0, 1).PasteSpecial xlPasteAll
End If

Next

    'With Application
        '.EnableEvents = True
        '.ScreenUpdating = True
    'End With
    
    Workbooks.Close
End Sub

lOOKIN It's feasible that in my TY_Funds_Desc workbook column C which is where I am expecting to get a revised description for what I have in

 

[PHV]

Else
r.Offset(0, 1).Copy
But r is NOTHING !!!!

 

[andycapp28]

Yeah, that's correct PHV. If r is Nothing I'm looking for advice on whether and how I can in such a situation use the value from curWS Col B and put it in desWS Col K