workbook in IF statement

[Guest]

I have this:
Dim TB1 As Worksheet, TB2 As Worksheet

Set TB1 = Workbooks("testbook1.xls").Worksheets(1)
If TB1.Cells(i, 1) = TB2.Cells(y, 9) Then

I am trying to replace "testbook1.xls" with the real name of a workbook that
is open.

I can get the book name but.... using this code.
book2 = ActiveWorkbook.Name


But I can not seem to find a way to replace "testbook1.xls" with the real
name.
Help please??? Someone?

 

[Bob Phillips]

Set TB1 = Activeworkbook.Worksheets(1

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

I tried it but did not work. Even when I included the )


I have two workbooks I am working with I don't think that makes a difference
because I can get each worksheet as I move from one to the other.
Anything else I might try?

 

[Guest]

If this will help here is the complete macro.

Sub Compare()
Dim TB1 As Worksheet, TB2 As Worksheet
Dim R1%, R2%, i%, y%
Set TB1 = Workbooks("testbook1.xls").Worksheets(1)
Set TB2 = Workbooks("testbook2.xls").Worksheets(1)
R1 = TB1.Cells(Rows.Count, 1).End(xlUp).Row
R2 = TB2.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To R1
For y = 1 To R2
If TB1.Cells(i, 1) = TB2.Cells(y, 1) Then
TB1.Cells(i, 1).Interior.ColorIndex = 3
TB2.Cells(y, 1).Interior.ColorIndex = 3
TB2.Cells(y, 3).Interior.ColorIndex = 3
Exit For
End If
Next y
Next i

End Sub

 

[Bob Phillips]

What does it mean that it didn't work, what happened?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

When the routine got to the line:
R1 = TB1.Cells(Rows.Count, 1).End(x1Up).Row

It crashed!

"Application-defined or object=defined error"

 

[Bob Phillips]

well it should be xlUp not x1Up, that is l (el) not 1 (one)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Okay it is working now?????

What does it mean that it didn't work, what happened?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

How, if you read my other post, you will see that you used x1Up not xlUp?

--

HTH

RP
(remove nothere from the email address if mailing direct)