How to update depenent cells mid-loop

voraciousv

New Member
Joined
Dec 19, 2009
Messages
3
My first macro in VB, I'm trying to:

1)increment a cell (variable called "target") by 0.1
2)recalc anther cell (S50) at each count
3)compare new S50 to its previous value, store if higher to find the max value

target = Range("g2").Value
correl = Range("s50").Value

For Count = 1 To 500
target = target + 0.1
Application.CalculateFullRebuild
If Range("s50").Value > correl Then
corropt = Range("s50").Value
countopt = Count
End If
Next Count

"target" goes through the count fine, the problem is corropt keeps reporting the original S50 value, and countopt keeps saying zero. It's like cell S50 never updates during the loop (cell s50 is indirectly dependent on "target" among other things"). Using excel 2007, thanks for any help!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hello and welcome to MrExcel.

Nothing in the code snippet that you've posted updates S50. Perhaps you should post the full code using code tags.
 
Upvote 0
Thanks, I will attempt to use code tags.

What I have posted is basically the meat of it. I had hoped the "Application.calculatefullrebuild" was updating all the cells in the workbook, I guess that's not the case?

Is there something I can use to get this effect?

TIA

Code:
Sub increment()
Dim correl As Double
Dim target As Double
Dim corropt As Double
Dim countopt As Integer

target = Range("g2").Value
correl = Range("s50").Value

    For Count = 1 To 5
    target = target + 0.1
    Application.CalculateFullRebuild
    If Range("s50").Value > correl Then
    corropt = Range("s50").Value
    countopt = Count
    End If
    Next Count
     
MsgBox "input " & target
MsgBox "R2 " & corropt
MsgBox "countopt " & countopt
End Sub
 
Upvote 0
Sorry but I don't understand what you are expecting to happen. You aren't writing anything back to the sheet so why would forcing a calculation of the sheet have any effect?
 
Upvote 0
Haha, okay. Thank you for pointing that out. I think I'm on the right track now. Only my variable is being changed not the actual cell... got it.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top