2 VBA enhancements

[markx]

Hello once again,

I have a very pretty macro now (thanks Duncan, thanks Kaak!)
-------------
Range("G2").Select
Do
If IsNumeric(Trim(ActiveCell.Value)) Then ActiveCell.Value =
Trim(ActiveCell.Value) * 1
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
-------------

At the present time, I would like however to add two small (general)
enhancements...
1) If I have to repeat the macro for 5 or 6 (or more) columns, I would
prefer not to copy/paste the code x times one after another, changing the
letter from G2 to K2, then M2 etc..., but to create the initial line in VBA
where I could specify a list (f. ex. =[G, K, M, O, P, X]) of columns to
treat with the code... (then modifying the "Range("G2").Select" line to
treat "G" like variable)...

2) Concerning the last line of the code => Loop Until
IsEmpty(ActiveCell.Offset(0, -1))
Is it possible to modify the code to tell:
Loop Until IsEmpty("cell in the same row that ActiveCell, but in the column
A")?


Many thanks in advance to all of you that could give me at least a hint how
to achieve this!!
Mark

[Bob Phillips]

Dim aryCols
Dim i As Long, j As Long

aryCols = Array("G", "K", "M", "O", "P", "X")

For i = LBound(aryCols) To UBound(aryCols)

Do
With Range(aryCols(i) & 2 + j)
If IsNumeric(Trim(.Value)) Then _
.Value = Trim(.Value) * 1
End With
j = j + 1
Loop Until IsEmpty(Cells(2 + j, "A").Value)

Next i


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"markx" <[email protected]> wrote in message
news:[email protected]...
> Hello once again,
>
> I have a very pretty macro now (thanks Duncan, thanks Kaak!)
> -------------
> Range("G2").Select
> Do
> If IsNumeric(Trim(ActiveCell.Value)) Then ActiveCell.Value =
> Trim(ActiveCell.Value) * 1
> ActiveCell.Offset(1, 0).Select
> Loop Until IsEmpty(ActiveCell.Offset(0, -1))
> -------------
>
> At the present time, I would like however to add two small (general)
> enhancements...
> 1) If I have to repeat the macro for 5 or 6 (or more) columns, I would
> prefer not to copy/paste the code x times one after another, changing the
> letter from G2 to K2, then M2 etc..., but to create the initial line in
VBA
> where I could specify a list (f. ex. =[G, K, M, O, P, X]) of columns to
> treat with the code... (then modifying the "Range("G2").Select" line to
> treat "G" like variable)...
>
> 2) Concerning the last line of the code => Loop Until
> IsEmpty(ActiveCell.Offset(0, -1))
> Is it possible to modify the code to tell:
> Loop Until IsEmpty("cell in the same row that ActiveCell, but in the
column
> A")?
>
>
> Many thanks in advance to all of you that could give me at least a hint
how
> to achieve this!!
> Mark
>
>
>

[Don Guillett]

try this
Sub multiplybyone()
For col = 1 To Cells(Columns.Count).End(xlToLeft).Column
For i = 1 To Cells(Rows.Count, col).End(xlUp).Row
mc = Trim(Cells(i, 2))
If IsNumeric(mc) Then mc = mc * 1
Next i
Next col
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"markx" <[email protected]> wrote in message
news:[email protected]...
> Hello once again,
>
> I have a very pretty macro now (thanks Duncan, thanks Kaak!)
> -------------
> Range("G2").Select
> Do
> If IsNumeric(Trim(ActiveCell.Value)) Then ActiveCell.Value =
> Trim(ActiveCell.Value) * 1
> ActiveCell.Offset(1, 0).Select
> Loop Until IsEmpty(ActiveCell.Offset(0, -1))
> -------------
>
> At the present time, I would like however to add two small (general)
> enhancements...
> 1) If I have to repeat the macro for 5 or 6 (or more) columns, I would
> prefer not to copy/paste the code x times one after another, changing the
> letter from G2 to K2, then M2 etc..., but to create the initial line in
> VBA where I could specify a list (f. ex. =[G, K, M, O, P, X]) of columns
> to treat with the code... (then modifying the "Range("G2").Select" line to
> treat "G" like variable)...
>
> 2) Concerning the last line of the code => Loop Until
> IsEmpty(ActiveCell.Offset(0, -1))
> Is it possible to modify the code to tell:
> Loop Until IsEmpty("cell in the same row that ActiveCell, but in the
> column A")?
>
>
> Many thanks in advance to all of you that could give me at least a hint
> how to achieve this!!
> Mark
>
>
>

[Ken Johnson]

Hi Mark,

The easiest solution to your first problem is to get rid of the first
line.
This way all you have to do is select the row 2 cell of a column you
want processed then run the macro.

The second change would be to convert...

Loop Until IsEmpty(ActiveCell.Offset(0, -1))

To...

Loop Until IsEmpty(Cells(Activecell.Row,1))

Try that out on a copy of you sheet.

I know it would be nice to have an inputbox to input the columns to
process, but for such a small number of columns would it be worth the
effort?

Ken Johnson

[markx]

Wwwoooooooowwwwww! Many thanks for such quick and elegant answers to you
three!
(I'm asking myself if one day I would be able get closer to your level of
VBA mastery...)

I'll test your solutions in the next 24h, but I'm already sure that they are
more than fine...
Once again, thank you very much,
Mark


"Ken Johnson" <[email protected]> wrote in message
news:[email protected]...
> Hi Mark,
>
> The easiest solution to your first problem is to get rid of the first
> line.
> This way all you have to do is select the row 2 cell of a column you
> want processed then run the macro.
>
> The second change would be to convert...
>
> Loop Until IsEmpty(ActiveCell.Offset(0, -1))
>
> To...
>
> Loop Until IsEmpty(Cells(Activecell.Row,1))
>
> Try that out on a copy of you sheet.
>
> I know it would be nice to have an inputbox to input the columns to
> process, but for such a small number of columns would it be worth the
> effort?
>
> Ken Johnson
>