Solving Cirular Reference Equations In Different Cells With Macro / VBA

[wmartin111]

Hello All,

I'm quite new to the VBA and Macro language, so please forgive my syntax and terminology errors.

I'm working on a data entry sheet for an engineering/autodesk hand-shake, where a "customer" can give us the Conveying Width, and 1 of the 3 variables and the others will populate from the 'circular' equations. So I need to be able to read the value put into the cell, then off in another group of cells have a programmed function that reads and writes to the other cells.

I've attached a simple picture of my 'Input sheet' variables.

The Equations are relatively simple, and the conditions can be met easily with IF AND functions:

Centerline = (OR-(CW+30)/2) or (IR+(CW+30)/2)
Outside Radius = (CR+(CW+30)/2) or (IR+CW+30)
Inside Radius = (CR-(CW+30)/2) or (OR-(CW+30))

Excel Forum.PNG


Thanks in advance!

[protonLeah]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

[wmartin111]

Here's a mock up sheet with what I'm trying to attempt. I've made a simple (not fully functional) macro.

Thanks in advance!

[protonLeah]

Your sample only gave conveyingwidth (no sample calc) so I'm not sure about this sol'n.

Formula(!) Name	Formula
_CL1	=(OutsideRadius-(ConveyingWidth+30)/2)
_CL2	=(InsideRadius+(ConveyingWidth+30)/2)
_IR1	=(CenterlineRadius-(ConveyingWidth+30)/2)
_IR2	=(OutsideRadius-(ConveyingWidth+30))
_OR1	=(CenterlineRadius+(ConveyingWidth+30)/2)
_OR2	=InsideRadius+ConveyingWidth+30

Choose equations corresponding to given values:

For example:

Please Login or Register  to view this content.

since there are data vals in cells ConveyingWidth and centerlineradius, the count is 2 and _CL1 is calculated:

given: CW, CR
find: OR,IR

_or1:= (CR + (CW + 30)/2)
_or2:=(IR + CW + 30)
-------------------------
_ir1: CR - (CW + 30)/2)
_ir2: OR - (CW + 30))
-------------------------
_cl1:= (OR - (CW + 30)/2)
_cl2:= (IR + (CW + 30)/2)
-------------------------
Therefore:
---> OR = _OR1.........(that is, calculate OR using formula named _OR1!)
---> IR = _IR1

[wmartin111]

Hey proton,

Thank you! That was greatly informative! Building off that, is there a way to take those values calculated when the given is entered, and put them into the Given Column? I had a chance to speak with a friend who showed the power of the macro record, and I figured that making a button to press to update these numbers might be the easiest way to accomplish this.

So thank you for giving me a starting point! If you might have any more advice that would be greatly appreciated! thanks!