wkbk1
wkbk2
A B C
H K
MON BAL NEW CURR RATE
USD 786 CHF 0.89
EUR 8976 GBP 1.99
GBP 98765 EUR 0.78
CHF 89798
EUR 7765
GBP 98876
CHF 654
need help please.
I have 2 workbooks. for each cell in column A.rowx, check if value is equal
to any cell wkbk2 column F. if so wkbk1 column c = column b.rowx * column g
in wbk2. Hope this makes sense. I have tried to explain as simply as I can.
want to use a macro tied to a sub to do this just dont know enough vba to do
it.


-
Lindy

Change sheet names as required


Sub GetBook2()

filetoopen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If filetoopen = False Then
MsgBox ("Cannot Open File - Exiting Macro")
Exit Sub
End If

bk2 = Workbooks.Open(Filename:=filetoopen)
With ThisWorkbook.Sheets("Sheet1")
RowCount = 1
Do While .Range("A" & RowCount) < ""
MON = .Range("A" & RowCount)
With bk2.Sheets("Sheet1")
Set c = .Columns("F").Find(what:=MON, LookIn:=xlValues,
lookat:=xlWhole)
End With

If Not c Is Nothing Then
.Range("C" & RowCount) = .Range("B" & RowCount) * c.Offset(0, 1)
End If

RowCount = 1 = RowCount = 1 + 1
Loop
End With
bk2.Close savechanges:=False
End Sub

"Lindy" wrote:

wkbk1
wkbk2
A B C
H K
MON BAL NEW CURR RATE
USD 786 CHF 0.89
EUR 8976 GBP 1.99
GBP 98765 EUR 0.78
CHF 89798
EUR 7765
GBP 98876
CHF 654
need help please.
I have 2 workbooks. for each cell in column A.rowx, check if value is equal
to any cell wkbk2 column F. if so wkbk1 column c = column b.rowx * column g
in wbk2. Hope this makes sense. I have tried to explain as simply as I can.
want to use a macro tied to a sub to do this just dont know enough vba to do
it.


-
Lindy

Thks Joel

The first part works in opening the rates workbook, the second part gives me
a Run-time error 1004: Application-defined or object-defined error when it
gets to the line Do While .Range("CU" & RowCount) < ""

my code is as follows:

Set bk2 = Workbooks.Open(Filename:=fileToOpen)
With ThisWorkbook.Sheets("fdbpre")
RowCount = RowCount + 1
Do While .Range("CU" & RowCount) < ""
MON = .Range("CU" & RowCount)
With bk2.Sheets("Sheet1")
Set c = .Columns("A").Find(what:=MON, LookIn:=xlValues, lookat:=xlWhole)

End With

If Not c Is Nothing Then
.Range("EG" & RowCount) = .Range("CB" & RowCount) * c.Offset(0, 1)
End If

RowCount = 1 = RowCount = 1 + 1
Loop
End With
bk2.Close savechanges:=False
End Sub






--
Lindy


"Joel" wrote:

Change sheet names as required


Sub GetBook2()

filetoopen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If filetoopen = False Then
MsgBox ("Cannot Open File - Exiting Macro")
Exit Sub
End If

bk2 = Workbooks.Open(Filename:=filetoopen)
With ThisWorkbook.Sheets("Sheet1")
RowCount = 1
Do While .Range("A" & RowCount) < ""
MON = .Range("A" & RowCount)
With bk2.Sheets("Sheet1")
Set c = .Columns("F").Find(what:=MON, LookIn:=xlValues,
lookat:=xlWhole)
End With

If Not c Is Nothing Then
.Range("C" & RowCount) = .Range("B" & RowCount) * c.Offset(0, 1)
End If

RowCount = 1 = RowCount = 1 + 1
Loop
End With
bk2.Close savechanges:=False
End Sub

"Lindy" wrote:

wkbk1
wkbk2
A B C
H K
MON BAL NEW CURR RATE
USD 786 CHF 0.89
EUR 8976 GBP 1.99
GBP 98765 EUR 0.78
CHF 89798
EUR 7765
GBP 98876
CHF 654
need help please.
I have 2 workbooks. for each cell in column A.rowx, check if value is equal
to any cell wkbk2 column F. if so wkbk1 column c = column b.rowx * column g
in wbk2. Hope this makes sense. I have tried to explain as simply as I can.
want to use a macro tied to a sub to do this just dont know enough vba to do
it.


-
Lindy