workbook in IF statement
 

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?
--
Jesseb

workbook in IF statement
 

Set TB1 = Activeworkbook.Worksheets(1

--

HTH

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


"Jesseb" wrote in message
...
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?
--
Jesseb

workbook in IF statement
 

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?

"Bob Phillips" wrote:

Set TB1 = Activeworkbook.Worksheets(1

--

HTH

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


"Jesseb" wrote in message
...
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?
--
Jesseb

workbook in IF statement
 

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" wrote:

Set TB1 = Activeworkbook.Worksheets(1

--

HTH

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


"Jesseb" wrote in message
...
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?
--
Jesseb

workbook in IF statement
 

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

--

HTH

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


"Jesseb" wrote in message
...
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?

"Bob Phillips" wrote:

Set TB1 = Activeworkbook.Worksheets(1

--

HTH

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


"Jesseb" wrote in message
...
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?
--
Jesseb

workbook in IF statement
 

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" wrote:

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

--

HTH

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


"Jesseb" wrote in message
...
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?

"Bob Phillips" wrote:

Set TB1 = Activeworkbook.Worksheets(1

--

HTH

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


"Jesseb" wrote in message
...
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?
--
Jesseb

workbook in IF statement
 

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)


"Jesseb" wrote in message
...
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" wrote:

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

--

HTH

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


"Jesseb" wrote in message
...
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?

"Bob Phillips" wrote:

Set TB1 = Activeworkbook.Worksheets(1

--

HTH

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


"Jesseb" wrote in message
...
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?
--
Jesseb

workbook in IF statement
 

Okay it is working now?????

"Bob Phillips" wrote:

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

--

HTH

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


"Jesseb" wrote in message
...
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?

"Bob Phillips" wrote:

Set TB1 = Activeworkbook.Worksheets(1

--

HTH

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


"Jesseb" wrote in message
...
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?
--
Jesseb

workbook in IF statement
 

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)


"Jesseb" wrote in message
...
Okay it is working now?????

"Bob Phillips" wrote:

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

--

HTH

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


"Jesseb" wrote in message
...
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?

"Bob Phillips" wrote:

Set TB1 = Activeworkbook.Worksheets(1

--

HTH

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


"Jesseb" wrote in message
...
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?
--
Jesseb