+ Reply to Thread
Results 1 to 5 of 5

With...End With instead of a Loop

  1. #1
    Forum Contributor
    Join Date
    03-06-2013
    Location
    Salt Lake City, Utah
    MS-Off Ver
    2007, 2010
    Posts
    127

    With...End With instead of a Loop

    I import transaction data with a date format that excel does not recognize as date but as a number. I use a macro to change it to a useable format. The macro is a loop, as shown below, and I'm wondering is there a way to perform this same task using a With....End With statement? Would that be more efficient? My coding is below.



    Dim lDateCount As Long
    Dim lCounter As Long


    'Set my variables values or range
    lDateCount = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Count

    Range("A1").Select
    For lCounter = 1 To lDateCount
    If Len(ActiveCell) = 8 Then ActiveCell.Value = Left(ActiveCell, 2) & "/" & Mid(ActiveCell, 3, 2) & "/" & Right(ActiveCell, 4)
    If Len(ActiveCell) = 7 Then ActiveCell.Value = Left(ActiveCell, 1) & "/" & Mid(ActiveCell, 2, 2) & "/" & Right(ActiveCell, 4)
    ActiveCell.Offset(1, 0).Activate
    Next lCounter

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,621

    Re: With...End With instead of a Loop

    What is the date format Excel isn't recognising?

    It might be possible to convert it in some other way, eg Data>Text to columns...
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    03-06-2013
    Location
    Salt Lake City, Utah
    MS-Off Ver
    2007, 2010
    Posts
    127

    Re: With...End With instead of a Loop

    The dates look like this in Col A:

    Date:
    4152013
    5052013
    10042013
    12122013

    I gave you both formats one for the months that are single digit and one for the months that are double.

  4. #4
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: With...End With instead of a Loop

    Please Login or Register  to view this content.



  5. #5
    Forum Contributor
    Join Date
    03-06-2013
    Location
    Salt Lake City, Utah
    MS-Off Ver
    2007, 2010
    Posts
    127

    Re: With...End With instead of a Loop

    snb,

    Thanks for the quick reply. When I ran your code I got the "Run-time error '13':". Any ideas what caused it?

+ 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