xluser2007
01-16-2008, 06:02 PM
Hi All,
I have been wrestling with this for a while now and require the assistance of VB Gurus.
The code is shown below:
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 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:
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 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
I have been wrestling with this for a while now and require the assistance of VB Gurus.
The code is shown below:
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 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:
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 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