change cellvalue based on value in adjacent cell
 

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.

change cellvalue based on value in adjacent cell
 

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.
.

change cellvalue based on value in adjacent cell
 

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.
.

change cellvalue based on value in adjacent cell
 

"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?

change cellvalue based on value in adjacent cell
 

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.

change cellvalue based on value in adjacent cell
 

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.
.