Solved: Error in macro - FIND function

[xluser2007]

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]

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]

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?

[rlv]

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]

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?

[ALe]

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

[rory]

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]

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?

[rlv]

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]

rlv, that's super. Many thanks :thumb!