Prev: Type Mismatch
Next: Excel Security
From: Alfredo on 12 Apr 2010 11:52
Ok this is going to be long to explain.

Spreed Sheet has 7 columns and an unknown amount of rows as a total
so columns from A to G
and Rows = unknown

Colum definition

A = Date
B = Account #
C = Type of Account
D = Amount posted (on our system)
E = Amount posted (on an external system)
F = Date when it close
G = Total

now there is several different groups that use the same set up in the same
sheet
What I want to do is to make a macro that copy the value from E to G as follow
E.value = G.Value

The problem I'm having is that I do not have a specific range for each of
this group
I can add or delete rows as I see fit, but the macro needs to keep working
no matter what.

my answer was, I named the very first cell of the group (G1_First) using the
named range option in excel and the very last in that group named it
(G1_Last).

The macro I came up with was:
(Group 1)
For I = Range("G1_First").rows + 1 to Range("G1_Last").rows - 1
Cells(I,7).value = Cells(I,5).value
Next I

after doing this macro i went to the sheet and set it to run automaticaly in
the code of that sheet.

one macro per group 5 in total.

the problem I'm facing is that the loop seens to be very slow, even though,
the amount of rows are less than 1000.

Can anyone come up with a better code for this?
From: Dave Peterson on 12 Apr 2010 12:25
When I do this kind of stuff, I can usually depend on another column to have
data in it when that row is used -- for you, maybe the date or account number???

If that's ok with you, you could use something like this that actually does a
copy|paste special|values:

Option Explicit
Sub testme()
Dim LastRow As Long
Dim myRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("E2:E" & LastRow)
myRng.Copy
.Range("G2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
End Sub


I started in row 2 (E2 and G2) -- I figured headers in row 1. Change them if
you need to.

Alfredo wrote:
>
> Ok this is going to be long to explain.
>
> Spreed Sheet has 7 columns and an unknown amount of rows as a total
> so columns from A to G
> and Rows = unknown
>
> Colum definition
>
> A = Date
> B = Account #
> C = Type of Account
> D = Amount posted (on our system)
> E = Amount posted (on an external system)
> F = Date when it close
> G = Total
>
> now there is several different groups that use the same set up in the same
> sheet
> What I want to do is to make a macro that copy the value from E to G as follow
> E.value = G.Value
>
> The problem I'm having is that I do not have a specific range for each of
> this group
> I can add or delete rows as I see fit, but the macro needs to keep working
> no matter what.
>
> my answer was, I named the very first cell of the group (G1_First) using the
> named range option in excel and the very last in that group named it
> (G1_Last).
>
> The macro I came up with was:
> (Group 1)
> For I = Range("G1_First").rows + 1 to Range("G1_Last").rows - 1
> Cells(I,7).value = Cells(I,5).value
> Next I
>
> after doing this macro i went to the sheet and set it to run automaticaly in
> the code of that sheet.
>
> one macro per group 5 in total.
>
> the problem I'm facing is that the loop seens to be very slow, even though,
> the amount of rows are less than 1000.
>
> Can anyone come up with a better code for this?

--

Dave Peterson
From: Wouter HM on 12 Apr 2010 12:35
Hi Alfredo

To me it looks like you made a small typo, try this:

For i = Range("G1_F").Row + 1 To Range("G1_L").Row - 1
Cells(i, 7).Value = Cells(i, 5).Value
Next i

HTH,

Wouter
 | 
Pages: 1
Prev: Type Mismatch
Next: Excel Security