title: Cell referencing not working in excel vba
body: <p>I wrote a function which takes inputs from a sheet, performs calculations, and enters the results on the same sheet.  I did this to test the functionality of the code.</p>
  
  <p>Now I need to have the code to have the same functionality, but to take input from 2 different sheets, and write to one of them.  </p>
  
  <p>Lets call the sheets "Data" and "Interface"</p>
  
  <p>I wrote the code initially using the <code>Range</code> object to reference cells and ranges, with no precursor.  I am currently trying to write the code as a <code>macro</code> assigned to a button on one of the sheets.</p>
  
  <p>Things I've tried:</p>
  
  <p>Activating the appropriate sheet and using ranges normally e.g.</p>
  
  <pre><code>Worksheets("Data").Activate
  pressure = Range("S2").Value
  </code></pre>
  
  <p>This causes odd errors, such as: </p>
  
  <blockquote>
    <p>unable to get match property of the worksheetfunction class</p>
  </blockquote>
  
  <p>Fully qualifying all Range objects e.g.</p>
  
  <pre><code>ThisWorkbook.Sheets("Data").Range("A1")
  </code></pre>
  
  <p>Same error as above.</p>
  
  <p>Referencing certain cells using named ranges:</p>
  
  <pre><code>pressure = Range("Pressure").Value
  </code></pre>
  
  <p>From what I've read, this should work.  I have no idea why it doesn't.</p>
  
  <p>Is there a good, or preferred, way to do this?</p>
  
  <p>Full code (2 functions, 1 sub):</p>
  
  <pre><code>Function InterpolateAll(methane As Double, pressure As Double, temp As Double, column As Integer, m_range As Range, p_range As Range, t_range As Range, m1 As Double, m2 As Double, t1 As Double, t2 As Double, t3 As Double, t4 As Double, p1 As Double, p_1_index As Integer, p2 As Double, p_2_index As Integer, p3 As Double, p_3_index As Integer, p4 As Double, p_4_index As Integer, p5 As Double, p_5_index As Integer, p6 As Double, p_6_index As Integer, p7 As Double, p_7_index As Integer, p8 As Double, p_8_index As Integer) As Double
  
  Worksheets("Data").Activate
  
  Dim v1 As Double
  Dim v2 As Double
  Dim v3 As Double
  Dim v4 As Double
  Dim v5 As Double
  Dim v6 As Double
  Dim v7 As Double
  Dim v8 As Double
  Dim vr1 As Double
  Dim vr2 As Double
  Dim vr3 As Double
  Dim vr4 As Double
  Dim vrr1 As Double
  Dim vrr2 As Double
  
  v1 = cells(p_1_index, column)
  v2 = cells(p_2_index, column)
  v3 = cells(p_3_index, column)
  v4 = cells(p_4_index, column)
  v5 = cells(p_5_index, column)
  v6 = cells(p_6_index, column)
  v7 = cells(p_7_index, column)
  v8 = cells(p_8_index, column)
  vr1 = Interpolate(pressure, p1, p2, v1, v2)
  vr2 = Interpolate(pressure, p3, p4, v3, v4)
  vr3 = Interpolate(pressure, p5, p6, v5, v6)
  vr4 = Interpolate(pressure, p7, p8, v7, v8)
  vrr1 = Interpolate(temp, t1, t2, vr1, vr2)
  vrr2 = Interpolate(temp, t3, t4, vr3, vr4)
  vrrr = Interpolate(methane, m1, m2, vrr1, vrr2)
  
  InterpolateAll = vrrr
  
  End Function
  
  Function Interpolate(a_desired As Double, a1 As Double, a2 As Double, p1 As Double, p2 As Double)
  
  Interpolate = (a_desired - a1) / (a2 - a1) * (p2 - p1) + p1
  
  End Function
  
  
  
  
  
  Private Sub CommandButton3_Click()
  
  Dim m1 As Double
  Dim m2 As Double
  Dim t1 As Double
  Dim t2 As Double
  Dim t3 As Double
  Dim t4 As Double
  Dim p1 As Double
  Dim p2 As Double
  Dim p3 As Double
  Dim p4 As Double
  Dim p5 As Double
  Dim p6 As Double
  Dim p7 As Double
  Dim p8 As Double
  
  Worksheets("Data").Activate
  
  pressure = Range("S2").Value
  temp = Range("R2").Value
  percent_methane = Range("Q2").Value
  
  start_range = Range("A1")
  
  
  Dim m_range As Range
  Dim p_range As Range
  Dim t_range As Range
  Dim m_start As Range
  Dim p_start As Range
  Dim t_start As Range
  
  
  Set m_range = Range("A:A")
  Set t_range = Range("B:B")
  Set p_range = Range("C:C")
  Set m_start = Range("A1")
  Set t_start = Range("B1")
  Set p_start = Range("C1")
  
  m1 = m_range.Item(WorksheetFunction.Match(percent_methane, m_range))
  m_range_1_top = WorksheetFunction.Match(percent_methane, m_range)
  m_range_1_bottom = WorksheetFunction.Match(m1, m_range, 0)
  
  
  m2 = m_range.Item(m_range_1_top + 1)
  m_range_2_bottom = m_range_1_top + 1
  m_range_2_top = WorksheetFunction.Match(m2, m_range)
  
  
  
  t1 = t_range.Item(WorksheetFunction.Match(temp, Range(cells(m_range_1_bottom, 2), cells(m_range_1_top, 2))) + m_range_1_bottom - 1)
  
  t_range_1_top = WorksheetFunction.Match(temp, Range(cells(m_range_1_bottom, 2), cells(m_range_1_top, 2))) + m_range_1_bottom - 1
  t_range_1_bottom = WorksheetFunction.Match(t_range.Item(t_range_1_top), Range(cells(m_range_1_bottom, 2), cells(m_range_1_top, 2)), 0) + m_range_1_bottom - 1
  
  t2 = t_range.Item(t_range_1_top + 1)
  
  t_range_2_bottom = t_range_1_top + 1
  t_range_2_top = WorksheetFunction.Match(t2, Range(cells(m_range_1_bottom, 2), cells(m_range_1_top, 2))) + m_range_1_bottom - 1
  
  t_range_3_top = WorksheetFunction.Match(temp, Range(cells(m_range_2_bottom, 2), cells(m_range_2_top, 2))) + m_range_2_bottom - 1
  t_range_3_bottom = WorksheetFunction.Match(t_range.Item(t_range_3_top), Range(cells(m_range_2_bottom, 2), cells(m_range_2_top, 2)), 0) + m_range_2_bottom - 1
  t3 = t_range.Item(t_range_3_bottom)
  
  t4 = t_range.Item(t_range_3_top + 1)
  
  t_range_4_bottom = t_range_3_top + 1
  t_range_4_top = WorksheetFunction.Match(t4, Range(cells(m_range_2_bottom, 2), cells(m_range_2_top, 2))) + m_range_2_bottom - 1
  
  p_1_index = WorksheetFunction.Match(pressure, Range(cells(t_range_1_bottom, 3), cells(t_range_1_top, 3))) + t_range_1_bottom - 1
  p1 = p_range.Item(p_1_index)
  
  p_2_index = p_1_index + 1
  p2 = p_range.Item(p_2_index)
  
  p_3_index = WorksheetFunction.Match(pressure, Range(cells(t_range_2_bottom, 3), cells(t_range_2_top, 3))) + t_range_2_bottom - 1
  p3 = p_range.Item(p_3_index)
  
  p_4_index = p_3_index + 1
  p4 = p_range.Item(p_4_index)
  
  p_5_index = WorksheetFunction.Match(pressure, Range(cells(t_range_3_bottom, 3), cells(t_range_3_top, 3))) + t_range_3_bottom - 1
  p5 = p_range.Item(p_5_index)
  
  p_6_index = p_5_index + 1
  p6 = p_range.Item(p_6_index)
  
  p_7_index = WorksheetFunction.Match(pressure, Range(cells(t_range_4_bottom, 3), cells(t_range_4_top, 3))) + t_range_4_bottom - 1
  p7 = p_range.Item(p_7_index)
  
  p_8_index = p_7_index + 1
  p8 = p_range.Item(p_8_index)
  
  var1 = InterpolateAll(CDbl(percent_methane), CDbl(pressure), CDbl(temp), 4, m_range, p_range, t_range, CDbl(m1), CDbl(m2), CDbl(t1), CDbl(t2), CDbl(t3), CDbl(t4), CDbl(p1), CInt(p_1_index), p2, CInt(p_2_index), p3, CInt(p_3_index), p4, CInt(p_4_index), p5, CInt(p_5_index), p6, CInt(p_6_index), p7, CInt(p_7_index), p8, CInt(p_8_index))
  Range("P4") = var1
  
  End Sub
  </code></pre>