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-

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-" <[email protected]> wrote in message
news:[email protected]...
>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-

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-" <[email protected]> wrote in message
news:[email protected]...
> 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-

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-" <[email protected]> wrote in message
> news:[email protected]...
> >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-
>
>
>

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-" <[email protected]> wrote in message
news:[email protected]...
> 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-" <[email protected]> wrote in message
> > news:[email protected]...
> > >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-
> >
> >
> >

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-" <[email protected]> wrote in message
> news:[email protected]...
> > 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-" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >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-
> > >
> > >
> > >
>
>
>

"-JEFF-" <[email protected]> wrote in message
news:[email protected]...
> 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