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" je napisao u poruci interesnoj
...
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
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.
.