title: using a sheet in an excel user defined function
body: <p>The VBA I'm trying to write is fairly simple but Ive never written VBA and coming from the visual studio &amp; C# world, this truly is hell!!</p>
  <p>So I'll really be grateful for any help/pointers/hints here</p>
  <p><img src="https://i.stack.imgur.com/JfuJE.jpg" alt="alt text" /></p>
  <p>So I have two important sheets. The Range sheet has 2 values per date. It needs a result, The Calc sheet takes two values and gets me a result.</p>
  <p>I want to Put the Current, and OneYear values <strong>for each date</strong> into the Calc sheet and get the result into the result column.</p>
  <p>So I tried defining a UDF but I learned later I cannot modify sheet contents in a UDF.</p>
  <p>I tried recording a macro and then hoped to go from there, but I got something like this:</p>
  <pre><code>Range(&quot;A2&quot;).Select
  Sheets(&quot;24Feb05-24FEB10&quot;).Select
  Range(&quot;K9&quot;).Select
  ActiveCell.FormulaR1C1 = &quot;=Calc!R[-8]C[-10]&quot;
  Range(&quot;K9&quot;).Select
  </code></pre>
  <p>Im assuming R1C1 is Row and Column but why on earth does it say -8 and -10!??</p>
  <p>Can someone get me in the right direction?</p>
  <p>Note: There are about 2000+ dates.</p>
  <p>Hope my question is clear!</p>
  <hr />
  <p><strong>Update</strong>
  I'll clear up a few more things:</p>
  <p>Im <strong>grossly</strong> simplifying the Calc sheet, in reality, it needs 5 values (there are columns upto SixYears) And then that sheet calls about 4 other sheets and does some finance calculations that some <strong>non</strong>-tech-savvy guy wrote.</p>
  <p>I tried to get the finance guy to express to me in algebra what he's doing, so I could just write up a C# function.</p>
  <p>I also tried writing a UDF, but like I said it needs to <strong>put</strong> in the values and get the result, which means the UDF will <strong>modify the sheet</strong>.</p>
  <pre><code>Function Func1(txt As Variant) As String
  //Returns the nth element from a delimited text string
      Dim txt As String
      txt = text
     Sheet4.Cells(1, 1) = txt
     Func1 = Sheet4.Cells(2, 1)//get the result value'
  End Function
  </code></pre>
  <p>Basically Im having a head ache learning/understand VBA syntax and the API.</p>
  <p><strong>Im looking for maybe a small example</strong> of how I would take values from one sheet into another and then get the some other values back? Maybe in a loop!?</p>