View Full Version : Solved: Error in macro - FIND function
xluser2007
01-10-2008, 09:09 PM
Hi All,
I am trying to write a macro that sources a date from the active workbook ('callsht' in code below), and opens up another workbook (F01hist.xls).
The macro aims to find the interest rate (in F01hist.xls) that corresponds to the date selected from 'callsht', copies the cell and pastes the link in a different cell in the 'callsht'.
The macro is shown below:
Sub lookup_mthly_interestrate_link()
Dim callsht As Worksheet
Dim rng As Range
Set callsht = ThisWorkbook.Sheets("Parameters")
Dim rate As Variant
' Opens F01hist.xls
Dim wb As Workbook
Set wb = Workbooks.Open([wbpath], UpdateLinks:=0)
wb.Activate
' Set rng = Cells.Find(What:=callsht.Range([run_month]), After:=ActiveCell, LookIn:=xlFormulas, _
' LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
' MatchCase:=False, SearchFormat:=False).Activate
Set rng = Cells.Find(What:=callsht.Range("C8").Value, LookIn:=xlValues).Activate
If Not rng Is Nothing Then ' not sure about syntax
rng.Offset(0, 1).Copy ' copy interest which is the cell to the right of the found date
End If ' Check the above If condition and how this
' refer to correct cell and then paste LINK
callsht.Range(callsht.Range("F8")).Paste Link:=True
End Sub
When i run the above, F01hist.xls opens up fine, but the macro gives a 'run-time error '91' Objectect variable or With block variable not set.'
Could anyone please explain any mistakes I have made in the above code?
mikerickson
01-10-2008, 11:00 PM
With wb.Sheets(1)
Set rng = .Cells.Find(What:=callsht.Range([run_month]), After:=.Range("a1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End With
xluser2007
01-10-2008, 11:16 PM
Hi mikerikson,
Thanks for your reply. I tried your code adjustment, but am now getting a runtime error 13, 'type mismatch'.
[Note that when F01hist.xls also has a worksheet also called F01hist.xls (although not best design, it is one that I download and refer to in the macro below)]
Sub lookup_mthly_interestrate_link()
Dim callsht As Worksheet
Dim rng As Range
Set callsht = ThisWorkbook.Sheets("Parameters")
Dim rate As Variant
' Opens F01hist.xls
Dim wb As Workbook
Set wb = Workbooks.Open([wbpath], UpdateLinks:=0)
wb.Activate
With wb.Sheets("F01HIST.XLS")
Set rng = .Cells.Find(What:=callsht.Range("C8"), After:=.Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End With
If Not rng Is Nothing Then ' not sure about syntax
rng.Offset(0, 1).Copy
End If ' Check the above If condition and how this
' refer to callshet F8 and then paste LINK
callsht.Range("F8").Paste Link:=True
End Sub
Any ideas where the issue lies now?
Without knowing exactly which line(s) are raising the runtime errors, I'm not sure what your main problem is. You could try this.
Sub lookup_mthly_interestrate_link()
Dim callsht As Worksheet
Dim rng As Range
Set callsht = ThisWorkbook.Sheets("Parameters")
Dim rate As Variant
' Opens F01hist.xls
Dim wb As Workbook
Set wb = Workbooks.Open([wbpath], UpdateLinks:=0)
wb.Activate
' Set rng = Cells.Find(What:=callsht.Range([run_month]), After:=ActiveCell, LookIn:=xlFormulas, _
' LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
' MatchCase:=False, SearchFormat:=False).Activate
Set rng = Cells.Find(What:=callsht.Range("C8").Value, LookIn:=xlValues)
If Not rng Is Nothing Then ' not sure about syntax
rng.Offset(0, 1).Copy ' copy interest which is the cell to the right of the found date
End If ' Check the above If condition and how this
' refer to correct cell and then paste LINK
callsht.Activate
callsht.Range("F8").Select
callsht.Paste Link:=True
End Sub
xluser2007
01-11-2008, 04:21 AM
rlv, thanks for your reply, just combined both your and mikerickson's codes to get the following:
Sub lookup_mthly_interestrate_link()
Dim callsht As Worksheet
Dim rng As Range
Set callsht = ThisWorkbook.Sheets("Parameters")
Dim rate As Variant
' Opens F01hist.xls
Dim wb As Workbook
Set wb = Workbooks.Open([wbpath], UpdateLinks:=0)
wb.Activate
With wb.Sheets("F01HIST.XLS")
Set rng = .Cells.Find(What:=callsht.Range("C8"), After:=.Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End With
If Not rng Is Nothing Then ' not sure about syntax
rng.Offset(0, 1).Copy
End If ' Check the above If condition and how this
' refer to callshet F8 and then paste LINK
callsht.Activate
callsht.Range("F8").Select
Selection.Paste Link:=True
End Sub
The error type now is "run-time error 424 'Object required'".
What does this error mean and how is it removed for the above code?
please tell us which line is generating the error (in case run your code with F8). Is "F01HIST.XLS" a file or the name of a sheet? (because you wrote)
With wb.Sheets("F01HIST.XLS")
using it a sheet
Try changing this line:
Set rng = .Cells.Find(What:=callsht.Range("C8"), After:=.Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
to this:
Set rng = .Cells.Find(What:=callsht.Range("C8"), After:=.Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
You can't assign a range object to a variable and activate it in one line.
xluser2007
01-11-2008, 04:47 PM
Hi All,
Ale, with regards to your question, from my my earlier post:
[Note that when F01hist.xls also has a worksheet also called F01hist.xls (although not best design, it is one that I download and refer to in the macro below)]
Rory thanks for that last post.
After trying it again there was an additional error in the last line:
Changed:
Selection.Paste Link:=True
to
Activesheet.Paste Link:=True
as the paste link operation does not like Selection.paste (any reason why?).
Well here is the working code.
Sub lookup_mthly_interestrate_link()
Dim callsht As Worksheet
Dim rng As Range
Set callsht = ThisWorkbook.Sheets("Parameters")
Dim rate As Variant
' Opens F01hist.xls
Dim wb As Workbook
Set wb = Workbooks.Open([wbpath], UpdateLinks:=0)
wb.Activate
With wb.Sheets("F01HIST.XLS")
Set rng = .Cells.Find(What:=callsht.Range("C8"), After:=.Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
rng.Activate
End With
If Not rng Is Nothing Then ' not sure about syntax
rng.Offset(0, 1).Copy
End If ' Check the above If condition and how this
'
' ' refer to callshet F8 and then paste LINK
callsht.Activate
callsht.Range("F8").Select
ActiveSheet.Paste Link:=True
End Sub
One last query, when I copy the rate i.e. rng.Offset(0, 1).Copy, how do I divide it by 100 when pasting into F8 in callsht (last line) using the above code?
Hi All,
One last query, when I copy the rate i.e. rng.Offset(0, 1).Copy, how do I divide it by 100 when pasting into F8 in callsht (last line) using the above code?
I don't think you can. Remember, you are pasting a link, not a value. You could manipulate the link formula after the paste operation though.
callsht.Activate
callsht.Range("F8").Select
callsht.Paste Link:=True
callsht.Range("F8").formula = callsht.Range("F8").formula & "/100"
xluser2007
01-11-2008, 10:40 PM
rlv, that's super. Many thanks :thumb!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.