FIND Function Issue

[xluser2007]

Hi All,

I have been wrestling with this for a while now and require the assistance of VB Gurus.

The code is shown below:

[vba]Sub Change_Int()

Dim shtUse As Worksheet

Dim Int_Sheet As String

Application.Calculate

Set shtUse = ThisWorkbook.Sheets("Parameters") ' Use this sheet i.e. 'shtUse' to open and update all relevant workbooks.

For rows = 13 To 13

Int_Sheet = shtUse.Cells(rows, 4) & "Int_" & shtUse.Cells(rows, 3) & "_" & shtUse.Cells(6, 3) & ".xls"

Workbooks.Open Filename:=Int_Sheet, UpdateLinks:=0

ActiveWorkbook.Sheets("Section 2 - Summary").Select

Range("A50:T65").Select

Set c = Selection.Find(CLng(shtUse.Range("C8").Value), After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Debug.Print c

If Not c Is Nothing Then

c.Offset(8, 0) = shtUse.Range("F8")

End If

Next rows

End Sub[/vba] Basically, we I have an activesheet ('Shtuse' in the above code) of parameters, which contains an interest rate figure in a cell "F8".

The aim of the above is to open an external link, find the relevant month (again sourcing from (Shtuse.Range("C8") - which contains a date like 31/10/2007), applies a FIND on a given range in the Opened workbook for this date.

Then 8 rows below the 'found' cell, the interest figure from 'Shtuse' C8 should be pasted. The external link (Int_Sheet) is opening up fine. However:

The bit of code:

[vba] ActiveWorkbook.Sheets("Section 2 - Summary").Select

Range("A50:T65").Select

Set c = Selection.Find(CLng(shtUse.Range("C8").Value), After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Debug.Print c

If Not c Is Nothing Then

c.Offset(8, 0) = shtUse.Range("F8")

End If [/vba] is causing issues. It is not printing 'c' in the immediate window, even though I know that c exists by manual inspection.

Could anyone please let me know what I'm doing wrong?

regards

[Bob Phillips]

This worked for me

[vba]

Sub Change_Int()
Dim shtUse As Worksheet
Dim Int_Sheet As String
Dim i As Long

Application.Calculate

Set shtUse = ThisWorkbook.Sheets("Parameters") ' Use this sheet i.e. 'shtUse' to open and update all relevant workbooks.

For i = 13 To 13

Int_Sheet = shtUse.Cells(i, 4) & "Int_" & shtUse.Cells(i, 3) & "_" & shtUse.Cells(6, 3) & ".xls"
Workbooks.Open Filename:=Int_Sheet, UpdateLinks:=0
ActiveWorkbook.Sheets("Section 2 - Summary").Select
Range("A50:T65").Select

Set c = Selection.Find(shtUse.Range("C8").Value, After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Debug.Print c

If Not c Is Nothing Then

c.Offset(8, 0) = shtUse.Range("F8")
End If
Next i

End Sub
[/vba]

[Dr.K]

EDIT: Oops, I misunderstood your issue.

[xluser2007]

xld, firstly thanks for your code.

Unfortunately it didn't work out for me.

The following code did:

[vba]Set c = Selection.Find(Format(shtUse.Range("C8").Value, "mmm-yy"), After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Debug.Print c[/vba]
It is strange that your code worked for you but not for me.

In the target spreadsheet ('Int_Sheet'), where we are actually applying the Find function the dates start with say 30/06/2007 and the following dates are given by =eomonth(previous date, 3) i.e. the next quarter. These eomonth dates are formatted in the "mmm-yy" format.

The above search on the format does work, but I'm surprised that your solution doesn't work for me .

I also tried to use the following to extract the date and compare to the target range using the CDate function but it also didn't work:

[vba] Set c = Selection.Find(CDate(shtUse.Range("C8").Value), After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False) [/vba]
Any ideas why the methods didin't work as I'm learning and would like to know how to improve my skills (and how to make it work for future use)?

Also another question I had is, all the pasting operations happen only after the relevant spreadsheet 'Int_Sheet' is successfully open.

I wanted to add a line of validation code to check that 'Int Sheet' does in fact exist. I Tried the following:

[vba]Sub Change_Int()

Dim shtUse As Worksheet

Dim Int_Sheet As String

Application.Calculate

Set shtUse = ThisWorkbook.Sheets("Parameters") ' Use this sheet i.e. 'shtUse' to open and update all relevant workbooks.

For Int_Sheet = 13 To 13

Int_Sheet = shtUse.Cells(rows, 4) & "FOMIC05_" & shtUse.Cells(rows, 3) & "_" & shtUse.Cells(6, 3) & ".xls"

Debug.Print Int_Sheet

If Not Int_Sheet Is Nothing Then

Workbooks.Open Filename:=Int_Sheet, UpdateLinks:=0

ActiveWorkbook.Sheets("Section 2 - Summary").Select

Range("A50:T65").Select

Set c = Selection.Find(Format(shtUse.Range("C8").Value, "mmm-yy"), After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Debug.Print c

If Not c Is Nothing Then

shtUse.Range("F8").Copy

c.Offset(8, 0).Select

Selection.PasteSpecial Paste:=xlPasteFormulas

Calculate

End If

ActiveWorkbook.Sheets("Section 1 - Intro").Select

Set Curr = Range("E13")

shtUse.Range("C8").Copy

Curr.Select

Selection.PasteSpecial Paste:=xlPasteValues

End If


Next rows

shtUse.Activate

End Sub[/vba]
This gives Compile Error "Type Mismatch" and highlights Int_Sheet in the line:

[vba] If Not Int_Sheet Is Nothing Then [/vba]
What am I doing wrong here and how do I correct this code?