Results 1 to 8 of 8

Dynamic Range + error message

Threaded View

  1. #1
    Forum Contributor
    Join Date
    01-26-2018
    Location
    London, England
    MS-Off Ver
    365
    Posts
    314

    Dynamic Range + error message

    Been working on the following macro:

    Formula: copy to clipboard
    Sub Macro1()
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=(TEXT(RC[3],""ddmmyy""))+(TEXT(RC[4],""hhmm""))"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A311")
    Range("A2:A311").Select
    Columns("A:A").Select
    Selection.NumberFormat = "0.00"
    Cells.Select
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("A2:A311") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("G2:G311") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Data").Sort
    .SetRange Range("A1:R311")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Range("B:C,F:F,L:R").Select
    Range("L1").Activate
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    End Sub


    Although I've come across two problems which I can't quite solve:

    1. The "311" part of the range needs to be dependant on whether there's a data point after that value (i.e. dynamic).

    2. As the macro is working, it'll post that a column is being overwritten. This needs to be automatically approved - instead of me clicking "yes" every time

    Solutions to the following problems would be massively appreciated

    Ty
    Mdn
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Error message if value is noth within range
    By vonrainer in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-30-2014, 01:19 AM
  2. Dynamic Chart - Invalid reference error message
    By Anthony2 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-12-2014, 04:00 AM
  3. Replies: 0
    Last Post: 07-31-2013, 06:03 AM
  4. Replies: 0
    Last Post: 03-10-2013, 11:17 PM
  5. [SOLVED] Name a dynamic range - Run-time error 1004 Method 'Range' of object'_Worksheet' failed
    By DavidBW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-03-2012, 08:00 AM
  6. Replies: 5
    Last Post: 07-27-2010, 10:58 AM
  7. out of range error message
    By juergenkemeter in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-19-2006, 02:10 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