+ Reply to Thread
Results 1 to 7 of 7

HOW TO RUN MACRO ON ENTIRE COLUMN

  1. #1
    -JEFF-
    Guest

    HOW TO RUN MACRO ON ENTIRE COLUMN

    I have a simple format macro that runs on a single cell. I need example code
    to have it run on either selection or entire column. -JEFF-

  2. #2
    JulieD
    Guest

    Re: HOW TO RUN MACRO ON ENTIRE COLUMN

    Hi Jeff

    if you'ld like to post your code it would make it easier for us to give a
    useful answer

    --
    Cheers
    JulieD
    check out www.hcts.net.au/tipsandtricks.htm
    ....well i'm working on it anyway
    "-JEFF-" <-JEFF-@discussions.microsoft.com> wrote in message
    news:4C774583-8374-4FB7-A607-9B0C5B313751@microsoft.com...
    >I have a simple format macro that runs on a single cell. I need example
    >code
    > to have it run on either selection or entire column. -JEFF-




  3. #3
    Bob Phillips
    Guest

    Re: HOW TO RUN MACRO ON ENTIRE COLUMN

    Something like

    For i = 1 To Cells(Rows.Count,"C").End(xlUp).Row
    thisCell = Cells(i,"C").Value
    ' do your stuff on thisCell
    Next i

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "-JEFF-" <-JEFF-@discussions.microsoft.com> wrote in message
    news:4C774583-8374-4FB7-A607-9B0C5B313751@microsoft.com...
    > I have a simple format macro that runs on a single cell. I need example

    code
    > to have it run on either selection or entire column. -JEFF-




  4. #4
    -JEFF-
    Guest

    Re: HOW TO RUN MACRO ON ENTIRE COLUMN

    Here's my code. I need to be able to run this either on a selection of cells
    or on a column. Either one will do.

    Sub JSN_format()
    Dim l_tmp
    Dim r_tmp
    Dim tmp_jsn
    tmp_jsn = ActiveCell.Value
    If Len(tmp_jsn = 13) And IsNumeric(Left(tmp_jsn, 1)) Then
    tmp_jsn = Right(tmp_jsn, 8)
    End If
    If Not InStr(tmp_jsn, "-") Then
    l_tmp = Left(tmp_jsn, 4)
    r_tmp = Right(tmp_jsn, 4)
    tmp_jsn = l_tmp + "-" + r_tmp
    ActiveCell.Value = tmp_jsn
    End If
    End Sub



    "JulieD" wrote:

    > Hi Jeff
    >
    > if you'ld like to post your code it would make it easier for us to give a
    > useful answer
    >
    > --
    > Cheers
    > JulieD
    > check out www.hcts.net.au/tipsandtricks.htm
    > ....well i'm working on it anyway
    > "-JEFF-" <-JEFF-@discussions.microsoft.com> wrote in message
    > news:4C774583-8374-4FB7-A607-9B0C5B313751@microsoft.com...
    > >I have a simple format macro that runs on a single cell. I need example
    > >code
    > > to have it run on either selection or entire column. -JEFF-

    >
    >
    >


  5. #5
    Bob Phillips
    Guest

    Re: HOW TO RUN MACRO ON ENTIRE COLUMN

    Untested

    Sub JSN_format()
    Dim l_tmp
    Dim r_tmp
    Dim tmp_jsn
    Dim iLastRow As Long
    Dim i As Long

    With Activecell
    iLastRow = Cells(Rows.Count, .Column).End(xlUp).Row
    For i = 1 To iLastRow
    tmp_jsn = Cells(i,.Column).Value
    If Len(tmp_jsn = 13) And IsNumeric(Left(tmp_jsn, 1)) Then
    tmp_jsn = Right(tmp_jsn, 8)
    End If
    If Not InStr(tmp_jsn, "-") Then
    l_tmp = Left(tmp_jsn, 4)
    r_tmp = Right(tmp_jsn, 4)
    tmp_jsn = l_tmp + "-" + r_tmp
    Cells(i,.Column).Value = tmp_jsn
    End If
    End With
    End Sub



    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "-JEFF-" <-JEFF-@discussions.microsoft.com> wrote in message
    news:2C31F2A3-1915-4677-8A47-FCACA31958AF@microsoft.com...
    > Here's my code. I need to be able to run this either on a selection of

    cells
    > or on a column. Either one will do.
    >
    > Sub JSN_format()
    > Dim l_tmp
    > Dim r_tmp
    > Dim tmp_jsn
    > tmp_jsn = ActiveCell.Value
    > If Len(tmp_jsn = 13) And IsNumeric(Left(tmp_jsn, 1)) Then
    > tmp_jsn = Right(tmp_jsn, 8)
    > End If
    > If Not InStr(tmp_jsn, "-") Then
    > l_tmp = Left(tmp_jsn, 4)
    > r_tmp = Right(tmp_jsn, 4)
    > tmp_jsn = l_tmp + "-" + r_tmp
    > ActiveCell.Value = tmp_jsn
    > End If
    > End Sub
    >
    >
    >
    > "JulieD" wrote:
    >
    > > Hi Jeff
    > >
    > > if you'ld like to post your code it would make it easier for us to give

    a
    > > useful answer
    > >
    > > --
    > > Cheers
    > > JulieD
    > > check out www.hcts.net.au/tipsandtricks.htm
    > > ....well i'm working on it anyway
    > > "-JEFF-" <-JEFF-@discussions.microsoft.com> wrote in message
    > > news:4C774583-8374-4FB7-A607-9B0C5B313751@microsoft.com...
    > > >I have a simple format macro that runs on a single cell. I need

    example
    > > >code
    > > > to have it run on either selection or entire column. -JEFF-

    > >
    > >
    > >




  6. #6
    -JEFF-
    Guest

    Re: HOW TO RUN MACRO ON ENTIRE COLUMN

    Thank you Bob. I just got it running with your first suggestion. I see a
    couple of differences between you first and second post. I'll run with it.
    -JEFF-

    "Bob Phillips" wrote:

    > Untested
    >
    > Sub JSN_format()
    > Dim l_tmp
    > Dim r_tmp
    > Dim tmp_jsn
    > Dim iLastRow As Long
    > Dim i As Long
    >
    > With Activecell
    > iLastRow = Cells(Rows.Count, .Column).End(xlUp).Row
    > For i = 1 To iLastRow
    > tmp_jsn = Cells(i,.Column).Value
    > If Len(tmp_jsn = 13) And IsNumeric(Left(tmp_jsn, 1)) Then
    > tmp_jsn = Right(tmp_jsn, 8)
    > End If
    > If Not InStr(tmp_jsn, "-") Then
    > l_tmp = Left(tmp_jsn, 4)
    > r_tmp = Right(tmp_jsn, 4)
    > tmp_jsn = l_tmp + "-" + r_tmp
    > Cells(i,.Column).Value = tmp_jsn
    > End If
    > End With
    > End Sub
    >
    >
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "-JEFF-" <-JEFF-@discussions.microsoft.com> wrote in message
    > news:2C31F2A3-1915-4677-8A47-FCACA31958AF@microsoft.com...
    > > Here's my code. I need to be able to run this either on a selection of

    > cells
    > > or on a column. Either one will do.
    > >
    > > Sub JSN_format()
    > > Dim l_tmp
    > > Dim r_tmp
    > > Dim tmp_jsn
    > > tmp_jsn = ActiveCell.Value
    > > If Len(tmp_jsn = 13) And IsNumeric(Left(tmp_jsn, 1)) Then
    > > tmp_jsn = Right(tmp_jsn, 8)
    > > End If
    > > If Not InStr(tmp_jsn, "-") Then
    > > l_tmp = Left(tmp_jsn, 4)
    > > r_tmp = Right(tmp_jsn, 4)
    > > tmp_jsn = l_tmp + "-" + r_tmp
    > > ActiveCell.Value = tmp_jsn
    > > End If
    > > End Sub
    > >
    > >
    > >
    > > "JulieD" wrote:
    > >
    > > > Hi Jeff
    > > >
    > > > if you'ld like to post your code it would make it easier for us to give

    > a
    > > > useful answer
    > > >
    > > > --
    > > > Cheers
    > > > JulieD
    > > > check out www.hcts.net.au/tipsandtricks.htm
    > > > ....well i'm working on it anyway
    > > > "-JEFF-" <-JEFF-@discussions.microsoft.com> wrote in message
    > > > news:4C774583-8374-4FB7-A607-9B0C5B313751@microsoft.com...
    > > > >I have a simple format macro that runs on a single cell. I need

    > example
    > > > >code
    > > > > to have it run on either selection or entire column. -JEFF-
    > > >
    > > >
    > > >

    >
    >
    >


  7. #7
    Bob Phillips
    Guest

    Re: HOW TO RUN MACRO ON ENTIRE COLUMN


    "-JEFF-" <JEFF@discussions.microsoft.com> wrote in message
    news:40E240F6-BF9A-41CD-9893-00D4FF4EBDAC@microsoft.com...
    > Thank you Bob. I just got it running with your first suggestion. I see a
    > couple of differences between you first and second post. I'll run with

    it.

    Hi Jeff,

    I adapted to your code :-)

    Bob



+ 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