Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple Macro?!?! (2K)

    What I want to do is have a user click on a command button when they are done entering some data on Sheet1, when the button is pressed it puts the formula '=Sheet1!$B$3' into the first blank cell in col A of Sheet2. Below is what I have discovered does NOT work <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> Could someone please let me know what I am doing wrong?

    Private Sub CommandButton1_Click()
    Worksheets("SHEET2").Activate
    Dim rng As Range
    Dim lngRow As Long
    Set rng = Range("A1", Range("A65536").End(xlUp))
    For lngRow = rng.Rows.Count To 2 Step -1
    If rng(lngRow - 1) <> "" Then
    Range("A" & (lngRow)).FormulaR1C1 = "=Sheet1!R3C2"
    Exit For
    End If
    Next lngRow
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Simple Macro?!?! (2K)

    How about this:
    <pre>Private Sub CommandButton1_Click()
    sheets("sheet2").Range("A65536").End(xlUp).offset( 1,0).formula = "=Sheet1!R3C2"
    end sub
    </pre>

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Macro?!?! (2K)

    Thank you very much, that is too easy!! I will have to remember that "End" command.

    Stats

Posting Permissions

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