In my account data i have amount in column h and Debit, Credit (DR/
CR) in next column. To modify the values in column h the code is
........
For Each c In Range("H2:h" & t)
If c.Offset(0, 1) = "DR" Then
c.Value = c
Else
c.Value = -c
End If
Next
........
This part of macro takes abnormally high time. Please suggest any
alternative to make this faster.

How many rows?

One quick thing - you don't need both if conditions. The first one does
nothing. Try this instead:

for each c in range("H2:h" & t)
if c.offset(0,1).value = "CR" then c.value = -c.value
next c


Sam

"Sane" wrote:

> In my account data i have amount in column h and Debit, Credit (DR/
> CR) in next column. To modify the values in column h the code is
> ........
> For Each c In Range("H2:h" & t)
> If c.Offset(0, 1) = "DR" Then
> c.Value = c
> Else
> c.Value = -c
> End If
> Next
> ........
> This part of macro takes abnormally high time. Please suggest any
> alternative to make this faster.
> .
>

Another thing that may help, put this at the start of the macro:

application.Calculation = xlCalculationManual

and this at the end:
application.Calculation = xlCalculationAutomatic

"Sane" wrote:

> In my account data i have amount in column h and Debit, Credit (DR/
> CR) in next column. To modify the values in column h the code is
> ........
> For Each c In Range("H2:h" & t)
> If c.Offset(0, 1) = "DR" Then
> c.Value = c
> Else
> c.Value = -c
> End If
> Next
> ........
> This part of macro takes abnormally high time. Please suggest any
> alternative to make this faster.
> .
>

"Sane" <(E-Mail Removed)> je napisao u poruci interesnoj
grupi:f9a2f0b1-ceb8-4956-a35d-(E-Mail Removed)...
> In my account data i have amount in column h and Debit, Credit (DR/
> CR) in next column. To modify the values in column h the code is
> .......
> For Each c In Range("H2:h" & t)
> If c.Offset(0, 1) = "DR" Then
> c.Value = c
> Else
> c.Value = -c
> End If
> Next

it is not a good idea to use "c.value=-c", because it just *inverts* the
value, called [mistakenly] twice, you have reverted to original value.
better is to have *new* calculted column [f.e at offset=2], like:

If c.Offset(0, 1) = "DR" Then
c.offset(0,2).Value = c
Else
c.offset(0,2).Value = -c
End If

so your original column "h" is preserved, and multiple execution doesn't
harm

and how many rows [t] you have, and what is meaning "long execution" for
you, 5 seconds, or more?
when lacking memory, excel becomes fragmented, and sudenly execution slows
down very much. can you trace the memory usage at task-manager?

On Dec 16, 4:55*pm, Sam Wilson <SamWil...@discussions.microsoft.com>
wrote:
> Another thing that may help, put this at the start of the macro:
>
> application.Calculation = xlCalculationManual
>
> and this at the end:
> application.Calculation = xlCalculationAutomatic
>
>
>
> "Sane" wrote:
> > In my account data i have amount in column h and Debit, Credit (DR/
> > CR) *in next column. To modify the values in column h the code is
> > ........
> > For Each c In Range("H2:h" & t)
> > * * If c.Offset(0, 1) = "DR" Then
> > * * c.Value = c
> > * * Else
> > * * c.Value = -c
> > * * End If
> > * * Next
> > ........
> > This part of macro takes abnormally high time. Please suggest any
> > alternative to make this faster.
> > .- Hide quoted text -
>
> - Show quoted text -

Thanks sam. both put together worked great.

In addition to Sam's suggestion, You might try adding the following line
before any execution lines:

Application.ScreenUpdating = False

This will prevent Excel from "re-drawing" the screen each time it evaluates
a cell. The manual calculation toggle should do just about the same thing. If
you're in fact having memory issues, using both frees up quite a bit of
resources especially if you're evaluating many cells.


"Sane" wrote:

> In my account data i have amount in column h and Debit, Credit (DR/
> CR) in next column. To modify the values in column h the code is
> ........
> For Each c In Range("H2:h" & t)
> If c.Offset(0, 1) = "DR" Then
> c.Value = c
> Else
> c.Value = -c
> End If
> Next
> ........
> This part of macro takes abnormally high time. Please suggest any
> alternative to make this faster.
> .
>