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!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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.
 

voraciousv

New Member
Joined
Dec 19, 2009
Messages
3
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
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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?
 

voraciousv

New Member
Joined
Dec 19, 2009
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,101
Messages
5,599,731
Members
414,330
Latest member
Rich920

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
Top