Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Northampton,UK
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I'm having difficulty setting a select range in a macro.

    The following line works ok.

    Range("G10:K18").Select

    so does this

    Range("G" & "10" & ":" & "K18").Select

    but when I try to add variables like this

    Range("G" & StartRangeRow & ":" & "K18").Select

    I get the following error

    Run-time error ‘1004’
    Method ‘Range’ of object ‘_Global’ failed.


    What I want to do is set the select range in a variable so I can just have a line as below

    Range(RangeText).Select


    What am I doing wrong?

    Thanks

    Moz

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Need to see more code - such as what is startrangerow and where/how is it set?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    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
    I speculate tht the variable StartRangeRow has nothing in it and thus VB doesn't understand the line. If the variable is a number, it is trying to select:
    Range("G0:K18")

    But if the variable is TEXT it is trying to select:

    Range("G:K18")

    Neither one is acceptable and will cause a run-time error

    Steve

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Northampton,UK
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks,

    Here is the code.

    Sub FormatInfo()

    Dim RowCount As Long, StartRangeRow As Long, RangeText As String

    StartRangeRow = 1
    For RowCount = 1 To 40
    If Cells(RowCount, 1) = "" Then
    RangeText = "A" & Str(StartRangeRow) & ":E" & Str(RowCount - 1)
    Range(RangeText).Select
    Call FormatBorders
    StartRangeRow = RowCount

    End If
    Next RowCount

    End Sub


    - Moz

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Moz,

    Seems like you'd want:
    Code:
    Dim RowCount As Long
    Dim RangeText As String
    
    For RowCount = 2 To 40
    If Cells(RowCount, 1) = "" Then
    RangeText = "A" & Str(RowCount) & ":E" & Str(RowCount - 1)
    Range(RangeText).Select
    Call FormatBorders
    
    End If
    Next RowCount
    
    End Sub
    In your code the first ":E" & Str(rowCount-1) would refer to a row before 1 ... no such thing.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Northampton,UK
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks RetiredGeek,

    I pasted your code into the macro, but I still get the following error message.

    Run-time '1004':
    Method 'Range' of object '_Global' failed


    and the line highlighted in debug is

    Code:
           Range(RangeText).Select
    What have I done wrong?

    Thanks for your help

    Moz

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You don't need (or want, since it adds a leading space) the Str function:

    Code:
    Dim RowCount As Long
    Dim RangeText As String
    
    For RowCount = 2 To 40
    If Cells(RowCount, 1) = "" Then
    RangeText = "A" & RowCount & ":E" & (RowCount - 1)
    Range(RangeText).Select
    Call FormatBorders
    
    End If
    Next RowCount
    
    End Sub

    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    Northampton,UK
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Rory,

    Works a treat.

    I thought I had to convert the number (RowCount) to a string so I could add to the string!

    Thanks again for your help

    Moz

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can if you want, but you should use CStr rather than Str.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    New Lounger
    Join Date
    Dec 2009
    Location
    Northampton,UK
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK Thanks,

    Whats the difference between Str() and Cstr()

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    CStr converts its argument to a string, whereas Str converts a number to a string, but reserves a space for the sign (which it omits if positive but leaves the space there) From the Help for CStr:

    If expression is * * * * * * * * * * * * * * * * * * * * * *CStr returns

    Boolean * * * * * * * * * * * * * * * * * * * *A string containing True or False

    Date * * * * * * * * * * * * * * * * * * * * * * *A string containing a date in the short date format of your system

    Null * * * * * * * * * * * * * * * * * * * * * * * A run-time error

    Empty * * * * * * * * * * * * * * * * * * * * * A zero-length string ("")

    Error * * * * * * * * * * * * * * * * * * * * * * A string containing the word Error followed by the error number

    Other numeric * * * * * * * * * * * * * * A string containing the number


    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    New Lounger
    Join Date
    Dec 2009
    Location
    Northampton,UK
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your help Rory,

    - Moz

Posting Permissions

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