Dismiss variables problem ?

[burrowsnz]

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

[majaro]

Hi,

I put you code in the VBA tags for easier readability.

[VBA]Sub Trial3()

' Worksheets("Cabling I&E").Activate
Dim String1 As String
Dim String2 As String

String1 = ActiveCell.Offset(0, 0)
String2 = ActiveCell.Offset(0, 11)

' MsgBox String1
' MsgBox String2

Sheets("CFTS").Activate
Cells.Find(What:=String1, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate


ActiveCell.Offset(0, 12).Range("A1").Select
MsgBox String2
SendKeys (String2)
SendKeys ("~")

'[attempt 1]
' ActiveSheet.Previous.Select
' ActiveSheet.Previous.Select
' ActiveCell.Offset(1, 0).Range("A1").Select

' [Attempt2]
Worksheets("CablingI&E").Activate

End Sub[/VBA]

[rory]

Try this version:
[VBA]Sub Trial3()
Dim rngFound As Range
Dim String1 As String
Dim String2 As String

String1 = ActiveCell.Value
String2 = ActiveCell.Offset(0, 11).Value

' MsgBox String1
' MsgBox String2

Set rngFound = Sheets("CFTS").Cells.Find(What:=String1, _
After:=Sheets("CFTS").Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
' Check there actually was a match or you will get errors
If rngFound Is Nothing Then
' No match
MsgBox "Value: " & String1 & " was not found."
Exit Sub
Else
' Put string2 in cell 12 columns to the right of matched cell
rngFound.Offset(0, 12).Value = String2
End If
' Select one cell below current cell.
ActiveCell.Offset(1, 0).Activate
End Sub
[/VBA]