Results 1 to 2 of 2

Thread: Dismiss variables Problem ?

  1. #1
    Woo! Hoo! my 1st post
    Join Date
    2007-12
    Posts
    1
    Login to Give a bone
    0

    Default Dismiss variables Problem ?

    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 $ 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 ?

    Code:
     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
    Last edited by Opie; 2007-12-26 at 02:49 PM. Reason: [CODE] tags added

  2. #2
    The Silent Type RobertB's Avatar
    Join Date
    2000-01
    Location
    Seattle WA USA
    Posts
    5,859
    Login to Give a bone
    0

    Default Re: Dismiss variables Problem ?

    SendKeys is a terrible way to place data in cells. You have the Range object. Look at the VBA docs for Excel on the Range object.
    R. Robert Bell
    Design Technology Manager
    Stantec
    Opinions expressed are mine alone and do not reflect the views of Stantec.

Similar Threads

  1. Variables for QLEADER
    By fclao in forum AutoCAD Customization
    Replies: 8
    Last Post: 2010-02-16, 04:48 AM
  2. system variables
    By robarc in forum AutoCAD 3D (2006 or below)
    Replies: 2
    Last Post: 2009-06-16, 01:33 PM
  3. LDD Variables?
    By scomer in forum AutoCAD Customization
    Replies: 8
    Last Post: 2007-07-31, 02:18 PM
  4. Are There Variables.....
    By avn in forum Land Desktop - General
    Replies: 0
    Last Post: 2005-08-18, 02:52 PM
  5. system variables
    By ajtrahan in forum AutoCAD General
    Replies: 3
    Last Post: 2005-01-26, 02:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •