+ Reply to Thread
Results 1 to 4 of 4

Compile error (Constant expression required) when using macro twice

Hybrid View

  1. #1
    Registered User
    Join Date
    04-10-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    76

    Compile error (Constant expression required) when using macro twice

    Hi guys,

    I'm trying to use the macro below (twice but with different variables) but everytime I get the error "Compile error: Constant expression required". Does anyone know why?

    Sub main()
        Call SplitDam
        Call ASSS
    End Sub
    Sub SplitDam()
    
    ' alle cellen unmergen
        Dim wSheet As Worksheet
         
        For Each wSheet In Worksheets
            wSheet.UsedRange.UnMerge
        Next wSheet
    
    Const fWhat As String = "Dama"
    Dim R As Range, fAdr As String, nR As Long, cutRng As Range, Ar As Range
    With Sheets("Totaal")
        Set R = .Range("E:E").Find(fWhat, [E1], xlFormulas, xlPart, , , False)
        If Not R Is Nothing Then
            fAdr = R.Address
            Set cutRng = R.Offset(0, -4).Resize(1, .UsedRange.Columns.Count)
            Do
                Set R = .Range("E:E").FindNext(R)
                If R Is Nothing Then Exit Do
                If R.Address = fAdr Then Exit Do
                Set cutRng = Union(cutRng, R.Offset(0, -4).Resize(1, .UsedRange.Columns.Count))
            Loop
        End If
        If Not cutRng Is Nothing Then
            nR = 1
            For Each Ar In cutRng.Areas
                Ar.Cut Destination:=Sheets(.Index + 1).Range("A" & nR)
                nR = Sheets(.Index + 1).Range("A" & Rows.Count).End(xlUp).Row + 1
            Next Ar
        End If
    End With
    
    On Error Resume Next
    Columns("N").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
    End Sub
    Sub ASSS()
    ' alle cellen unmergen
        Dim wSheet As Worksheet
         
        For Each wSheet In Worksheets
            wSheet.UsedRange.UnMerge
        Next wSheet
    
    Const fWhat As String = "AS"
    Dim R As Range, fAdr As String, nR As Long, cutRng As Range, Ar As Range
    With Sheets("Totaal")
        Set R = .Range("N:N").Find(fWhat, [N1], xlFormulas, xlPart, , , False)
        If Not R Is Nothing Then
            fAdr = R.Address
            Set cutRng = R.Offset(0, -4).Resize(1, .UsedRange.Columns.Count)
            Do
                Set R = .Range("N:N").FindNext(R)
                If R Is Nothing Then Exit Do
                If R.Address = fAdr Then Exit Do
                Set cutRng = Union(cutRng, R.Offset(0, -4).Resize(1, .UsedRange.Columns.Count))
            Loop
        End If
        If Not cutRng Is Nothing Then
            nR = 1
            For Each Ar In cutRng.Areas
                Ar.Cut Destination:=Sheets(.Index + 2).Range("A" & nR)
                nR = Sheets(.Index + 2).Range("A" & Rows.Count).End(xlUp).Row + 1
            Next Ar
        End If
    End With
    
    On Error Resume Next
    Columns("N").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
    End Sub

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,650

    Re: Compile error (Constant expression required) when using macro twice

    At a first glance - try changing [E1] (and N1 in next one) to
    .Range("E1")
    you do this within scope of With Sheets("Totaal") while form: [address] refers to active sheet.
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    04-10-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    76

    Re: Compile error (Constant expression required) when using macro twice

    Dear Kaper,

    I've tried it but unfortunatly I get the error "type doesn't match". Any other ideas?

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,650

    Re: Compile error (Constant expression required) when using macro twice

    Hi,
    I tried several times, but dont get the error.
    So probably attachment would make further studying this case easier.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Compile Error: Expected: expression
    By Wheelie686 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2013, 08:51 AM
  2. [SOLVED] Compile error: Constant expression required error when merging two Codes
    By Kezwick in forum Outlook Programming / VBA / Macros
    Replies: 8
    Last Post: 06-26-2013, 09:32 AM
  3. [SOLVED] Compile Error: Expected Expression, Syntax Error
    By gjohn282 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-20-2012, 11:28 PM
  4. Compile error: Object required
    By Hinnerk in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-23-2012, 04:38 PM
  5. creating a public constant by concatenating string......gives compile error
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-26-2011, 07:23 AM

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