+ Reply to Thread
Results 1 to 3 of 3

macro beginner error

Hybrid View

  1. #1
    Registered User
    Join Date
    11-26-2007
    Posts
    17

    macro beginner error

    Hi, I'm a complete macro noob. but i think I've got this worked out, at least in principle. I'm trying to use a value in Sheet1.Range("A1") to move to a certain row in Sheet2 and add a value. this is my code so far:

    Sub test()
    '
    ' test Macro
    ' Macro recorded 11/27/2007 by none
    '
    
    '
    Dim y As Integer
    y = ActiveSheet.Range("A1").Value
    Sheets("Sheet2").Select
    ActiveSheet.Range.Cells(y, 1) = "some value"
    End Sub
    I get an error on the last line : ActiveSheet.Range.Cells(y, 1) = "some value"

    does anyone have any insight on to what is going wrong.

    Thank you
    Last edited by NBVC; 11-27-2007 at 09:47 AM.

  2. #2
    Registered User
    Join Date
    11-26-2007
    Posts
    17
    I got it working by omitting ActiveSheet.Range. from the last line

    for anyone interested in this topic. this works

    Sub test()
    '
    ' test Macro
    ' Macro recorded 11/27/2007 by none
    '
    
    '
    Dim y As Integer
    y = ActiveSheet.Range("A1").Value
    Sheets("Sheet2").Select
    Cells(y, 1) = "some value"
    End Sub
    the tutorial i have been reading says the following:

    The following three statements are interchangable

    ActiveSheet.Range.Cells(1,1)
    Range.Cells(1,1)
    Cells(1,1)
    why did the code not work with the full path name? any insight is appreciated

    thanx
    Last edited by NBVC; 11-27-2007 at 09:48 AM.

  3. #3
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264
    Hi

    As far as I'm aware (but this may simply be a result of limited or insufficient experience), that in this instance you have to supply an argument to Range ie:

    Activesheet.Range.Cells(1,1) = "Howdy" 'doesn't work!
    
    Activesheet.Range("A1").Cells(1,1) = "Howdy" 'Will work!
    Of course, as you've founf, you can entirely omit the Range qualifier and just use Activesheet.Cells().

    Richard

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1