Results 1 to 4 of 4

Problem with Multiple Ranges

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    06-30-2005
    Location
    Verwood, Dorset, England
    MS-Off Ver
    Excel 2000
    Posts
    479

    Problem with Multiple Ranges

    I am using excel 2000

    I have a spreadsheet into which sales data is enterred, for a range of cells e120:e138, named xEuro, the data is enterred as euros and automatically converts to £

    The code is as follows:
     
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells.Count > 1 Then Exit Sub
        Me.Protect UserInterfaceOnly:=True
        If Not Intersect(Target, Range("e120:e138")) Is Nothing Then
            If IsNumeric(Target) Then
                Application.EnableEvents = False
                Target = Round(Target / [xEuro], 0)
                Target.NumberFormat = "###0,0.00"
                Application.EnableEvents = True
            End If
        End If
         
    End Sub
    I now want to extend the range, to include 12 columns of data, but get an error message "Compile error wrong number of arguments or invalid property assignment" when I have more than 2 columns in the range

    The code I have tried is as follows:
     
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells.Count > 1 Then Exit Sub
        Me.Protect UserInterfaceOnly:=True
        If Not Intersect(Target, Range("e120:e138", "k120:k138", "q120:q138)) Is  Nothing Then
            If IsNumeric(Target) Then
                Application.EnableEvents = False
                Target = Round(Target / [xEuro], 0)
                Target.NumberFormat = "###0,0.00"
                Application.EnableEvents = True
            End If
        End If
         
    End Sub
    What do I need to change to be able to add multiple ranges

    Thanks in advance for any help

    Paul
    Last edited by VBA Noob; 04-12-2007 at 01:29 PM.
    Paul

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