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
|
Similar Threads
