Hi,
I have some code that works fine as is. I now wanted to use it within an If
structure for conditional control. When I try appying my code within an IF
statement it returns nothing i.e no value. myInterest07 picks up a value and
pastes in the worksheet. This no longer works within an If statement. Why?
What am I doing wrong?

Dim mySDate As String
Dim myDate As String
Dim myInterest07 As Double
Dim myRow As Long
Dim sPath As String
Dim myFind As String



myDate = Format(InputBox("Please enter date for input files (dd/mm/yy)", ,
"11/04/05"), "dd/mm/yy")
mySDate = Format(myDate, "mm-dd-yy")
myDate = Format(myDate, "yyyymmdd")
Workbooks.Open FileName:=sPath & myDate & "_interest.csv"

Columns("A:A").Insert Shift:=xlToRight
range("A1:A2149").FormulaR1C1 = "=CONCATENATE(RC[1],RC[4])"
If myFind = "04F641007USD" Then
myInterest07 = Cells.Find(What:=myFind, After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext _
, MatchCase:=False).Offset(0, 9)

myInterest07 = myInterest07 * -1

Workbooks("Testmsinput.xls").Worksheets("MS Input3").Cells(11, 4).Value =
myInterest07

Where is "End If"?

"HeyMickey" wrote in message
...
Hi,
I have some code that works fine as is. I now wanted to use it within an
If
structure for conditional control. When I try appying my code within an IF
statement it returns nothing i.e no value. myInterest07 picks up a value
and
pastes in the worksheet. This no longer works within an If statement. Why?
What am I doing wrong?

Dim mySDate As String
Dim myDate As String
Dim myInterest07 As Double
Dim myRow As Long
Dim sPath As String
Dim myFind As String



myDate = Format(InputBox("Please enter date for input files (dd/mm/yy)", ,
"11/04/05"), "dd/mm/yy")
mySDate = Format(myDate, "mm-dd-yy")
myDate = Format(myDate, "yyyymmdd")
Workbooks.Open FileName:=sPath & myDate & "_interest.csv"

Columns("A:A").Insert Shift:=xlToRight
range("A1:A2149").FormulaR1C1 = "=CONCATENATE(RC[1],RC[4])"
If myFind = "04F641007USD" Then
myInterest07 = Cells.Find(What:=myFind, After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext _
, MatchCase:=False).Offset(0, 9)

myInterest07 = myInterest07 * -1

Workbooks("Testmsinput.xls").Worksheets("MS Input3").Cells(11, 4).Value =
myInterest07

sorry...
macro should read as follows (see below). If the IF part is removed the
macro works fine. I dont understand why myInterest07 does not pick up the
value I am after. Thx.

Sub CollateralKMF()

'finds the adj/collateral value in the MS interest file

Dim mySDate As String
Dim myDate As String
Dim myInterest07 As Double
Dim myRow As Long
Dim sPath As String
Dim myFind As String



myDate = Format(InputBox("Please enter date for input files (dd/mm/yy)", ,
"11/04/05"), "dd/mm/yy")
mySDate = Format(myDate, "mm-dd-yy")
myDate = Format(myDate, "yyyymmdd")
Workbooks.Open FileName:=sPath & myDate & "_interest.csv"

Columns("A:A").Insert Shift:=xlToRight
range("A1:A2149").FormulaR1C1 = "=CONCATENATE(RC[1],RC[4])"

If myFind = "04F641007USD" Then
myInterest07 = Cells.Find(What:=myFind, After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext _
, MatchCase:=False).Offset(0, 9)

'changes the value to negative so we add it up to get net amount

myInterest07 = myInterest07 * -1

'stores the interest figure in the MS sheet

Workbooks("Testmsinput.xls").Worksheets("MS Input3").Cells(11, 4).Value =
myInterest07

End If

Application.Calculation = xlCalculationAutomatic
Workbooks(myDate & "_interest.csv").Close

End Sub

Where is myFind initailized? I see where you test the value in my find with
your if statement but I do not see where myFind got it's value in the first
place.
--
HTH...

Jim Thomlinson


"HeyMickey" wrote:

Hi,
I have some code that works fine as is. I now wanted to use it within an If
structure for conditional control. When I try appying my code within an IF
statement it returns nothing i.e no value. myInterest07 picks up a value and
pastes in the worksheet. This no longer works within an If statement. Why?
What am I doing wrong?

Dim mySDate As String
Dim myDate As String
Dim myInterest07 As Double
Dim myRow As Long
Dim sPath As String
Dim myFind As String



myDate = Format(InputBox("Please enter date for input files (dd/mm/yy)", ,
"11/04/05"), "dd/mm/yy")
mySDate = Format(myDate, "mm-dd-yy")
myDate = Format(myDate, "yyyymmdd")
Workbooks.Open FileName:=sPath & myDate & "_interest.csv"

Columns("A:A").Insert Shift:=xlToRight
range("A1:A2149").FormulaR1C1 = "=CONCATENATE(RC[1],RC[4])"
If myFind = "04F641007USD" Then
myInterest07 = Cells.Find(What:=myFind, After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext _
, MatchCase:=False).Offset(0, 9)

myInterest07 = myInterest07 * -1

Workbooks("Testmsinput.xls").Worksheets("MS Input3").Cells(11, 4).Value =
myInterest07

Hi Jim,

myFind is a string found when I do this:
Columns("A:A").Insert Shift:=xlToRight
range("A1:A2149").FormulaR1C1 = "=CONCATENATE(RC[1],RC[4])"

How should I initiliaze correctly myfind?

"Jim Thomlinson" wrote:

Where is myFind initailized? I see where you test the value in my find with
your if statement but I do not see where myFind got it's value in the first
place.
--
HTH...

Jim Thomlinson


"HeyMickey" wrote:

Hi,
I have some code that works fine as is. I now wanted to use it within an If
structure for conditional control. When I try appying my code within an IF
statement it returns nothing i.e no value. myInterest07 picks up a value and
pastes in the worksheet. This no longer works within an If statement. Why?
What am I doing wrong?

Dim mySDate As String
Dim myDate As String
Dim myInterest07 As Double
Dim myRow As Long
Dim sPath As String
Dim myFind As String



myDate = Format(InputBox("Please enter date for input files (dd/mm/yy)", ,
"11/04/05"), "dd/mm/yy")
mySDate = Format(myDate, "mm-dd-yy")
myDate = Format(myDate, "yyyymmdd")
Workbooks.Open FileName:=sPath & myDate & "_interest.csv"

Columns("A:A").Insert Shift:=xlToRight
range("A1:A2149").FormulaR1C1 = "=CONCATENATE(RC[1],RC[4])"
If myFind = "04F641007USD" Then
myInterest07 = Cells.Find(What:=myFind, After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext _
, MatchCase:=False).Offset(0, 9)

myInterest07 = myInterest07 * -1

Workbooks("Testmsinput.xls").Worksheets("MS Input3").Cells(11, 4).Value =
myInterest07

Is it that myFind isn't getting set, so the IF is always False?

--
HTH

Bob Phillips

"HeyMickey" wrote in message
...
Hi,
I have some code that works fine as is. I now wanted to use it within an
If
structure for conditional control. When I try appying my code within an IF
statement it returns nothing i.e no value. myInterest07 picks up a value
and
pastes in the worksheet. This no longer works within an If statement. Why?
What am I doing wrong?

Dim mySDate As String
Dim myDate As String
Dim myInterest07 As Double
Dim myRow As Long
Dim sPath As String
Dim myFind As String



myDate = Format(InputBox("Please enter date for input files (dd/mm/yy)", ,
"11/04/05"), "dd/mm/yy")
mySDate = Format(myDate, "mm-dd-yy")
myDate = Format(myDate, "yyyymmdd")
Workbooks.Open FileName:=sPath & myDate & "_interest.csv"

Columns("A:A").Insert Shift:=xlToRight
range("A1:A2149").FormulaR1C1 = "=CONCATENATE(RC[1],RC[4])"
If myFind = "04F641007USD" Then
myInterest07 = Cells.Find(What:=myFind, After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext _
, MatchCase:=False).Offset(0, 9)

myInterest07 = myInterest07 * -1

Workbooks("Testmsinput.xls").Worksheets("MS Input3").Cells(11, 4).Value =
myInterest07