need help with script comparing values of two different workbooks

One more problem

Thanks, the code works just fine until i hit
blank rows because the cell values still
match up so it replaces all the list titles
found in the next cells.
right now i'm trying to come up with code to
skip over the row if the cells(r,2).value is empty
any suggestions?

Lloyd,
You can use this OpenWb sub to open the closed workbook, then you can manipulate the workbook anyway you would like. Put the code at the beginning of the code for everything else you have. At the end include: Application.ScreenUpdating = True. You can also add:
With WorkBooks(FileName)
.Save
.Close
End With
This is about as descriptive as I can get without seeing your code and know exactly what it does. If you need some more help let me know.

Ryan

Sub OpenWb
Dim Msg As String
Dim Path As String
Dim FileName As String

On Error Resume Next
Application.ScreenUpdating = False
Msg = "Unable to find "
Path = PathofClosedWorkbook
FileName1 = NameofClosedWorkbook
Oldbook = ActiveWorkbook.Name
Err = 0

If WorkbookIsOpen(FileName) = False Then
Workbooks.Open FileName:=Path & FileName
Else
Workbooks(FileName).Activate
End If

If Err <> 0 Then
MsgBox Msg & Path & FileName, vbCritical, "Error"
Exit Sub
End If

End Sub

Private Function WorkbookIsOpen(wbName) As Boolean
' Returns TRUE if the workbook is open
Dim X As Workbook
On Error Resume Next
Set X = Workbooks(wbName)
If Err = 0 Then WorkbookIsOpen = True _
Else: WorkbookIsOpen = False
On Error GoTo 0

End Function

Re: One more problem

It's kinda long, and there's a brief time where
you think it's frozen, but it works. If you have
any suggestions for making it shorter, please email
me. again, thanks for your help.

Sub fyCompare()

Dim Msg As String
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String

On Error Resume Next
Application.ScreenUpdating = False
Msg = "Unable to find "
FileName = "fy00act.xls"

Oldbook = ActiveWorkbook.Name
Err = 0

If WorkbookIsOpen(FileName) = False Then
Workbooks.Open FileName:=FileName
Else
Workbooks(FileName).Activate
End If

If Err <> 0 Then
MsgBox Msg & FileName, vbCritical, "Error"
Exit Sub
End If

Workbooks(Oldbook).Activate
r = 4
Line1:
If Workbooks(FileName).Sheets("fy00act").Cells(r, 1).value = "" Then
GoTo Line5
Else: GoTo Line2
End If

Line5:
If Workbooks(FileName).Sheets("fy00act").Cells(r, 2).value = "" Then
GoTo Line2
Else: GoTo Line3
End If

Line2:
r = r + 1
If Cells(r, 1).value = "Total requested" Then
r = 0
GoTo Line4
End If
GoTo Line1

Line3:
For a = 1 To 200
If Workbooks(Oldbook).ActiveSheet.Cells(a, 2).value = _
Workbooks(FileName).Sheets("fy00act").Cells(r, 2).value Then
Cells(a, 3).value = Workbooks(FileName).Sheets("fy00act").Cells(r, 3).value
Cells(a, 4).value = Workbooks(FileName).Sheets("fy00act").Cells(r, 4).value
Cells(a, 7).value = Workbooks(FileName).Sheets("fy00act").Cells(r, 5).value
Cells(a, 8).value = Workbooks(FileName).Sheets("fy00act").Cells(r, 6).value
End If
Next a
GoTo Line2

Line4:
Workbooks(FileName).Close
Application.ScreenUpdating = True

End Sub
Private Function WorkbookIsOpen(wbName) As Boolean
' Returns TRUE if the workbook is open
Dim X As Workbook
On Error Resume Next
Set X = Workbooks(wbName)
If Err = 0 Then WorkbookIsOpen = True _
Else: WorkbookIsOpen = False
On Error GoTo 0

End Function

My coding scans the closed workbook just fine, my problem is with the comparison of individual cells, I have attached my code to this follow up

this is my current coding so far.
it reads the closed workbook just fine,
my problem comes with the section marked
with my comment of what i'm trying to
accomplish. I don't know how to get the
open workbook to compare the value of one of
its cells to the value of the cells in the
closed one.

Private Function GetValue(file, sheet, ref)

Dim arg As String

arg = "'[" & file & "]" & sheet & "'!" & _
Range(ref).Address(, , xlR1C1)

GetValue = ExecuteExcel4Macro(arg)

End Function

Sub auto_update()

Dim arnum As Integer
Dim r As Integer

a = "fy00act.xls"
b = "fy00act"

Application.ScreenUpdating = False
For r = 1 To 200

' at this point i want to compare cells(r,2) with
the getvalue(a, b, c) and if they match, i want
the rest of the coding to follow through. I
tried an if...then statement, but the way i'm
coding it as is does not work. Also, all the
blank cells scanned on the closed workbook
come out as 0's on the active one, how do i leave
them blank,(there's probably a simple answer
to this one, but since i'm already asking anyways)

c = Cells(r, 2).Address
If Cells(r, 2) = GetValue(a, b, c) Then

d = Cells(r, 3).Address
Cells(r, 3) = GetValue(a, b, d)
e = Cells(r, 4).Address
Cells(r, 4) = GetValue(a, b, e)
f = Cells(r, 5).Address
Cells(r, 7) = GetValue(a, b, f)
g = Cells(r, 6).Address
Cells(r, 8) = GetValue(a, b, g)
Application.ScreenUpdating = True
End If
Next r

End Sub

Re: My coding scans the closed workbook just fine, my problem is with the comparison of individual cells, I have attached my code to this follow up

Wow,
Looks way more confusing then what it has to be, first, what version of XL are you using. Second, try to explain in words what's going on, and I'll (try to) come up w/ a different way. I'll give it a look so don't think it's a waste of time. Hope I can help!
Ryan

Re: My coding scans the closed workbook just fine, my problem is with the comparison of individual cells, I have attached my code to this follow up

Excel 97

What's happening is that the getvalue function
is retrieving the values of cells designated by the
auto_update sub from the closed workbook. I'll go
through the code step by step.

Private Function GetValue(file, sheet, ref)

Dim arg As String

arg = "'[" & file & "]" & sheet & "'!" & _
Range(ref).Address(, , xlR1C1)

'This function takes the variables found below
'(a,b,c,d,e,f) and uses them to determine the
'source of the data for the cells.

GetValue = ExecuteExcel4Macro(arg)
'this runs an excel macro function and returns
'the result

End Function

Sub auto_update()

Dim arnum As Integer
Dim r As Integer
Dim re As String

a = "fy00act.xls"
b = "fy00act"
'a and b are the workbook and sheet names of the source file for the
'data being retrieved

Application.ScreenUpdating = False
For r = 1 To 200

c = Cells(r, 2).Address
re = GetValue(a, b, c)
'this assigns c the cells(r,2) address so that
when the function looks up the value, it has
the workbook, worksheet, and cell designation

If re = GetValue(a, b, c) Then
'this is where i'm stuck. I'm trying to get the
workbook to compare the value of each cell in column
2 to each cell in column 2 of the closed workbook
and if there's a match, it will do the following..

d = Cells(r, 3).Address
Cells(r, 3) = GetValue(a, b, d)
e = Cells(r, 4).Address
Cells(r, 4) = GetValue(a, b, e)
f = Cells(r, 5).Address
Cells(r, 7) = GetValue(a, b, f)
g = Cells(r, 6).Address
Cells(r, 8) = GetValue(a, b, g)

'these assign the cells found in the same row of columns 3,4,7, and 8
'the values of the cells found in the matching row
'of the closed workbook in columns 3,4,5,6(in short
'i'm trying to get row's to look the same, but first
'testing if it's the right row to change the values for)

Application.ScreenUpdating = True
End If
Next r

End Sub

what happens when i run this script is that
the test doesn't happen to check if the
values of the 2nd column match up, but the
rest of the columns are replaced with the information
found in the closed workbook.

(i.e)
if originally the first work book had the following lines

project name supervisor budget
proj1 bob $1
proj2 john $2
proj3 bill $3
proj4 tom $4
the 2nd has the updated copy
project name supervisor budget
proj1 roger $1
proj2 john $2
proj4 tom $4
proj3 mark $5

after running the macro, it would look like this
project name supervisor budget
proj1 roger $1
proj2 john $2
proj3 tom $4
proj4 mark $5

you can see the problem is that it doesn't test
if the project name matches, it simply replaces
the next values.

Does this still sound confusing?
if you could find a simpler way or figure
out my problem i'd appreciate it. Like
i said, i've only been working with macros
for the past 3 days mostly looking up examples
on the web and the help files and trying
to get it to do what i want. took me awhile
to understand how to code the getvalue function
so that it does what i want it to.

Re: My coding scans the closed workbook just fine, my problem is with the comparison of individual cells, I have attached my code to this follow up

Lloyd,
Here you go, like I said. This code seems much more simplier. It worked for me. You will have to change what the path name is set to. Let me know how it works. Good Luck!
Ryan

Sub fyCompare()

Dim Msg As String
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String

On Error Resume Next
Application.ScreenUpdating = False
Msg = "Unable to find "
Path = "C:\My Documents\"
FileName = "fy00act.xls"

Oldbook = ActiveWorkbook.Name
Err = 0

If WorkbookIsOpen(FileName) = False Then
Workbooks.Open FileName:=Path & FileName
Else
Workbooks(FileName).Activate
End If

If Err <> 0 Then
MsgBox Msg & Path & FileName, vbCritical, "Error"
Exit Sub
End If

Workbooks(Oldbook).Activate

For r = 1 To 200
If Workbooks(FileName).ActiveSheet.Cells(r, 2).Value = _
Workbooks(Oldbook).Sheets("fy00act").Cells(r, 2).Value Then

Cells(r, 3).Value = Workbooks(FileName).Sheets("fy00act").Cells(r, 3).Value
Cells(r, 4).Value = Workbooks(FileName).Sheets("fy00act").Cells(r, 4).Value
Cells(r, 7).Value = Workbooks(FileName).Sheets("fy00act").Cells(r, 5).Value
Cells(r, 8).Value = Workbooks(FileName).Sheets("fy00act").Cells(r, 6).Value
End If
Next r

Workbooks(FileName).Close
Application.ScreenUpdating = True

End Sub
Private Function WorkbookIsOpen(wbName) As Boolean
' Returns TRUE if the workbook is open
Dim X As Workbook
On Error Resume Next
Set X = Workbooks(wbName)
If Err = 0 Then WorkbookIsOpen = True _
Else: WorkbookIsOpen = False
On Error GoTo 0

End Function