If...then structures

[HeyMickey]

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

 

[yamashiro]

Where is "End If"?

 

[Jim Thomlinson]

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.

 

[Bob Phillips]

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

 

[HeyMickey]

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

 

[HeyMickey]

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?

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

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