change cellvalue based on value in adjacent cell

[Sane]

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.

 

[Sam Wilson]

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

 

[Sam Wilson]

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

application.Calculation = xlCalculationManual

and this at the end:
application.Calculation = xlCalculationAutomatic

 

[sali]

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?

 

[Sane]

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

application.Calculation = xlCalculationManual

and this at the end:
application.Calculation = xlCalculationAutomatic





- Show quoted text -

Thanks sam. both put together worked great.

 

[fisch4bill]

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.