I am in need of assistance of an excel vba macro that i am creating.
I am calculating the Canadian dollar by multipling cell "F2" * "G2". However, I am using a loop to do this and I am having trouble.
Loop2 works beautifly but my calculation in Loop1 bombs out.
Can anyone help?
Here is the code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/7/2004 by Desktop
'
'
Dim iDollar As Double
ChDir "C:\Documents and Settings\Ryan Ballew\Desktop"
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\Ryan Ballew\Desktop\Untitled.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), _
Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), _
Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15, 2), Array( _
16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), Array(22, 2)) _
, TrailingMinusNumbers:=True
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "0.00"
Range("G1").Select
ActiveCell.FormulaR1C1 = "price"
Range("G2").Select
ActiveCell.FormulaR1C1 = Application.InputBox("What is the value of the Canadian dollar?", "This accepts numbers only", 1)
iDollar = ActiveCell.Value
Call Loop1(iDollar)
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Ryan Ballew\Desktop\Untitled.txt", FileFormat:= _
xlText, CreateBackup:=False
ActiveWindow.Close
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\Ryan Ballew\Desktop\Untitled.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), _
Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), _
Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15, 2), Array( _
16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), Array(22, 2), _
Array(23, 2)), TrailingMinusNumbers:=True
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Workbooks.Open Filename:= _
"C:\Documents and Settings\Ryan Ballew\Desktop\CanadaTemplate.xls", Origin:= _
xlWindows
Windows("Untitled.txt").Activate
Columns("B:B").Select
Selection.Copy
Windows("CanadaTemplate.xls").Activate
Columns("A:A").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("Untitled.txt").Activate
Columns("C:C").Select
Selection.Copy
Windows("CanadaTemplate.xls").Activate
Columns("B:B").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C2").Select
ActiveCell.FormulaR1C1 = " "
Range("D2").Select
Windows("Untitled.txt").Activate
Columns("G:G").Select
Selection.Copy
Windows("CanadaTemplate.xls").Activate
Columns("D:D").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("Untitled.txt").Activate
Columns("D:D").Select
Selection.Copy
Windows("CanadaTemplate.xls").Activate
Columns("E:E").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("Untitled.txt").Activate
Columns("F:F").Select
Selection.Copy
Windows("CanadaTemplate.xls").Activate
Columns("F:F").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("Untitled.txt").Activate
Columns("E:E").Select
Selection.Copy
Windows("CanadaTemplate.xls").Activate
Columns("G:G").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SmallScroll ToRight:=5
Windows("Untitled.txt").Activate
ActiveWindow.SmallScroll ToRight:=5
Columns("L:L").Select
Selection.Copy
Windows("CanadaTemplate.xls").Activate
Columns("H:H").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("Untitled.txt").Activate
Columns("M:M").Select
Selection.Copy
Windows("CanadaTemplate.xls").Activate
Columns("I:I").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J2").Select
Call Loop2
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Ryan Ballew\Desktop\CanadaAddDelete.txt", _
FileFormat:=xlText, CreateBackup:=False
MsgBox "Canada File Successful"
End Sub
Sub Loop1(canadianDollar As Double)
' This loop runs as long as there is something in the next column
Dim i As Integer
Dim rng As Range
i = 2
Do While IsEmpty(ActiveCell.Offset(0, 1)) = False
ActiveCell.FormulaR1C1 = canadianDollar * ("Fi").Select
'ActiveCell.FormulaR1C1 = Application.InputBox("What is the value of the Canadian dollar?", "This accepts numbers only", 1)
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop
End Sub
Sub Loop2()
' This loop repeats a fixed number of times getting its reference from elsewhere
Dim i As Integer
Dim intRowCount As Integer
intRowCount = Range("A1").CurrentRegion.Rows.Count - 1
For i = 1 To intRowCount
ActiveCell.FormulaR1C1 = "22"
ActiveCell.Offset(1, 0).Select
Next i
End Sub
