more efficient VBA?

Hello,
I have the following macro:
------------------------
Sub Test()
Range("N2").Select
Do
ActiveCell.FormulaR1C1 = "=CONCATENATE(C[-7],C[-6])"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -2))
End Sub
------------------------
It works fine, the only problem is that it takes time (20 seconds to treat
1000 rows).
Are you aware of any more efficient way of writing this type of code?
Thanks for any hints!
Regards,
Mark

Try this:

Sub Test()
Dim x As Long, Rng As Range
x& = Range("L" & Rows.Count).End(xlUp).Row
Set Rng = Range("N2:N" & x&)
Rng.FormulaR1C1 = "=CONCATENATE(C[-7],C[-6])"
Set Rng = Nothing
End Sub

Hope this helps,

Hutch

"markx" wrote:

> Hello,
> I have the following macro:
> ------------------------
> Sub Test()
> Range("N2").Select
> Do
> ActiveCell.FormulaR1C1 = "=CONCATENATE(C[-7],C[-6])"
> ActiveCell.Offset(1, 0).Select
> Loop Until IsEmpty(ActiveCell.Offset(0, -2))
> End Sub
> ------------------------
> It works fine, the only problem is that it takes time (20 seconds to treat
> 1000 rows).
> Are you aware of any more efficient way of writing this type of code?
> Thanks for any hints!
> Regards,
> Mark
>
>
>

Hi Mark

Don't select and activate stuff, it's slow and unnecessary.
Also, if you have formulae there, they will recalculate on every cell entry,
it takes time. Turn calculation temporarily off while writing things to
multiple cells.

Try this, it is be pretty fast:

Sub test()
Dim R As Long
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For R = 2 To Cells(Rows.Count, 12).End(xlUp).Row
Cells(R, 14).FormulaR1C1 = _
"=CONCATENATE(C[-7],C[-6])"
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

HTH. Best wishes Harald


"markx" <[email protected]> skrev i melding
news:[email protected]...
> Hello,
> I have the following macro:
> ------------------------
> Sub Test()
> Range("N2").Select
> Do
> ActiveCell.FormulaR1C1 = "=CONCATENATE(C[-7],C[-6])"
> ActiveCell.Offset(1, 0).Select
> Loop Until IsEmpty(ActiveCell.Offset(0, -2))
> End Sub
> ------------------------
> It works fine, the only problem is that it takes time (20 seconds to treat
> 1000 rows).
> Are you aware of any more efficient way of writing this type of code?
> Thanks for any hints!
> Regards,
> Mark
>
>

Wow! Quite efficient (took less than 1 second, I think)! Thanks a lot
Hutch!!!

One other question:
I realised that the formula I want to put in this column should be
different:
with "=CONCATENATE(C[-7],C[-6])" I get =CONCATENATE(G:G;H:H), but in fact I
looked for =CONCATENATE(G2;H2) (or even better =G2&H2) for row 2 and so
on...
The result is the same, but any idea how to modify the R1C1 formula to get
the second option?

Thanks once again,
Mark


"Tom Hutchins" <[email protected]> wrote in message
news:[email protected]...
> Try this:
>
> Sub Test()
> Dim x As Long, Rng As Range
> x& = Range("L" & Rows.Count).End(xlUp).Row
> Set Rng = Range("N2:N" & x&)
> Rng.FormulaR1C1 = "=CONCATENATE(C[-7],C[-6])"
> Set Rng = Nothing
> End Sub
>
> Hope this helps,
>
> Hutch
>
> "markx" wrote:
>
>> Hello,
>> I have the following macro:
>> ------------------------
>> Sub Test()
>> Range("N2").Select
>> Do
>> ActiveCell.FormulaR1C1 = "=CONCATENATE(C[-7],C[-6])"
>> ActiveCell.Offset(1, 0).Select
>> Loop Until IsEmpty(ActiveCell.Offset(0, -2))
>> End Sub
>> ------------------------
>> It works fine, the only problem is that it takes time (20 seconds to
>> treat
>> 1000 rows).
>> Are you aware of any more efficient way of writing this type of code?
>> Thanks for any hints!
>> Regards,
>> Mark
>>
>>
>>

Change Rng.FormulaR1C1 = "=CONCATENATE(RC[-7],C[-6])" to

Rng.FormulaR1C1 = "=CONCATENATE(RC[-7],RC[-6])"

or

Rng.FormulaR1C1 = "=RC[-7]" & "&" & "RC[-6]"

Regards,

Hutch

"markx" wrote:

> Wow! Quite efficient (took less than 1 second, I think)! Thanks a lot
> Hutch!!!
>
> One other question:
> I realised that the formula I want to put in this column should be
> different:
> with "=CONCATENATE(C[-7],C[-6])" I get =CONCATENATE(G:G;H:H), but in fact I
> looked for =CONCATENATE(G2;H2) (or even better =G2&H2) for row 2 and so
> on...
> The result is the same, but any idea how to modify the R1C1 formula to get
> the second option?
>
> Thanks once again,
> Mark
>
>
> "Tom Hutchins" <[email protected]> wrote in message
> news:[email protected]...
> > Try this:
> >
> > Sub Test()
> > Dim x As Long, Rng As Range
> > x& = Range("L" & Rows.Count).End(xlUp).Row
> > Set Rng = Range("N2:N" & x&)
> > Rng.FormulaR1C1 = "=CONCATENATE(C[-7],C[-6])"
> > Set Rng = Nothing
> > End Sub
> >
> > Hope this helps,
> >
> > Hutch
> >
> > "markx" wrote:
> >
> >> Hello,
> >> I have the following macro:
> >> ------------------------
> >> Sub Test()
> >> Range("N2").Select
> >> Do
> >> ActiveCell.FormulaR1C1 = "=CONCATENATE(C[-7],C[-6])"
> >> ActiveCell.Offset(1, 0).Select
> >> Loop Until IsEmpty(ActiveCell.Offset(0, -2))
> >> End Sub
> >> ------------------------
> >> It works fine, the only problem is that it takes time (20 seconds to
> >> treat
> >> 1000 rows).
> >> Are you aware of any more efficient way of writing this type of code?
> >> Thanks for any hints!
> >> Regards,
> >> Mark
> >>
> >>
> >>
>
>
>