burrowsnz
12-25-2007, 01:54 PM
My first attempt at writing a macro, based on a recorded macro.
In the code below, the message boxes were used to confirm both the correct variables were recorded from the primary sheet [Cabling I&E] and available for use in the target sheet [CFTS].
String 1 values are 6 character alphanumerics; String 2 are $ values not exceeding 99,999.99.
The macro does all that is intended until it comes to returning to the primary sheet and positioning the cursor one row below the location of the source cell of String1 variable.
As currently operating, the macro is carrying the String 2 $ value back and placing it in the source cell for String 1.
Queries:
1] Should the variables be dismissed after Line 21 ? If so, how does one dismiss variables, individually or collectively ?
2] Is there something missing in either of the Attempts that would return the cursor to the cell immediately below the primary source cell ?
1 . Sub Trial3()
2 . ' Worksheets("Cabling I&E").Activate
3 . Dim String1 As String
4 . Dim String2 As String
5 .
6 . String1 = ActiveCell.Offset(0, 0)
7 . String2 = ActiveCell.Offset(0, 11)
8 .
9 . ' MsgBox String1
10 . ' MsgBox String2
11 .
12 . Sheets("CFTS").Activate
13 . Cells.Find(What:=String1, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
14 . :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
15 . False, SearchFormat:=False).Activate
16 .
17 .
18 . ActiveCell.Offset(0, 12).Range("A1").Select
19 . MsgBox String2
20 . SendKeys (String2)
21 . SendKeys ("~")
22 . '[attempt 1]
23 . ' ActiveSheet.Previous.Select
24 . ' ActiveSheet.Previous.Select
25 . ' ActiveCell.Offset(1, 0).Range("A1").Select
26 .
27 . ' [Attempt2]
28 . Worksheets("CablingI&E").Activate
29 .
30 . End Sub
In the code below, the message boxes were used to confirm both the correct variables were recorded from the primary sheet [Cabling I&E] and available for use in the target sheet [CFTS].
String 1 values are 6 character alphanumerics; String 2 are $ values not exceeding 99,999.99.
The macro does all that is intended until it comes to returning to the primary sheet and positioning the cursor one row below the location of the source cell of String1 variable.
As currently operating, the macro is carrying the String 2 $ value back and placing it in the source cell for String 1.
Queries:
1] Should the variables be dismissed after Line 21 ? If so, how does one dismiss variables, individually or collectively ?
2] Is there something missing in either of the Attempts that would return the cursor to the cell immediately below the primary source cell ?
1 . Sub Trial3()
2 . ' Worksheets("Cabling I&E").Activate
3 . Dim String1 As String
4 . Dim String2 As String
5 .
6 . String1 = ActiveCell.Offset(0, 0)
7 . String2 = ActiveCell.Offset(0, 11)
8 .
9 . ' MsgBox String1
10 . ' MsgBox String2
11 .
12 . Sheets("CFTS").Activate
13 . Cells.Find(What:=String1, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
14 . :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
15 . False, SearchFormat:=False).Activate
16 .
17 .
18 . ActiveCell.Offset(0, 12).Range("A1").Select
19 . MsgBox String2
20 . SendKeys (String2)
21 . SendKeys ("~")
22 . '[attempt 1]
23 . ' ActiveSheet.Previous.Select
24 . ' ActiveSheet.Previous.Select
25 . ' ActiveCell.Offset(1, 0).Range("A1").Select
26 .
27 . ' [Attempt2]
28 . Worksheets("CablingI&E").Activate
29 .
30 . End Sub