Cell referencing not working in excel vba

Question

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.

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.

Lets call the sheets "Data" and "Interface"

I wrote the code initially using the Range object to reference cells and ranges, with no precursor. I am currently trying to write the code as a macro assigned to a button on one of the sheets.

Things I've tried:

Activating the appropriate sheet and using ranges normally e.g.

Worksheets("Data").Activate
pressure = Range("S2").Value

This causes odd errors, such as:

unable to get match property of the worksheetfunction class

Fully qualifying all Range objects e.g.

ThisWorkbook.Sheets("Data").Range("A1")

Same error as above.

Referencing certain cells using named ranges:

pressure = Range("Pressure").Value

From what I've read, this should work. I have no idea why it doesn't.

Is there a good, or preferred, way to do this?

Full code (2 functions, 1 sub):

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

Please post a complete code snippet with essential part causing the problem, otherwise it's a bit unclear. Thanks and regards, — Alexander Bell, Dec 17 '14 at 22:24
Have you read [this post](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros)? Btw, the error you get doesn't seem to have any relation to the code you posted. Why don't you try testing just the codes you posted and see if it errors out. AFAIK it won't. — L42, Dec 17 '14 at 22:26
Yes, but it's a bit tangled. Long story short, I wrote code to do interpolation in 4 dimensions- it's sloppy because I was stressed for time and couldn't quickly find a way to make a function out of the range-searching and identifying thing. — bluefabric, Dec 17 '14 at 22:27
tangled is ok - just post as much code as possible - people on here are experienced with tangled code! — whytheq, Dec 17 '14 at 22:28
I would agree with @L42: it seems like OP posted the code unrelated to the real issue causing the error. It's very confusing. — Alexander Bell, Dec 17 '14 at 22:29
I'm guessing that somewhere in the code is "WorksheetFunction.Match(WITH INCORRECT SYNTAX FOR RANGES INSIDE)" — peege, Dec 17 '14 at 22:33
Note that if the `WorksheetFunction.Match()` cannot find a match then the code will produce a run-time error - you may want to look at appropriate error trapping/handling to resolve this. — SierraOscar, Dec 17 '14 at 22:41
@PJRosenburg- the posted code works correctly, it runs and i get the correct answer. The issue is when I try to get it to work when I get pressure, temp, and percent_methane from the macro sheet, and the rest from another sheet. I thought that the worksheets("data").activate would fix this, but it doesn't seem to — bluefabric, Dec 17 '14 at 22:45
It seems you are reluctant to use fully qualified references like `ThisWorkbook.Sheets("Data").Range("A1")`. Use `With ThisWorkbook.Sheets("Data")` together with `End With` and prefix everything related to Sheets("Data") inside the `With ... End With` with a period like `Set m_range = .Range("A:A")`. Then you are free to use *m_range* as you wish. — , Dec 17 '14 at 22:51
alternatively you could also pass which sheet you want to use into the `InterpolateAll` function and just call `Sheets(theSheetYouPassedIn).Cells(p_1_index, column)...` or throw that into a `With` block as jeeped said — chancea, Dec 17 '14 at 22:53
Please consider re-editing this post to update the code sample(s) to their current state. We've been though a few revisions and it is hard to hit a moving target. — , Dec 17 '14 at 23:52

peege · Answer 1 · 2014-12-18T00:53:28.400

1

Skip activating your sheets, and declare the values by using a full address WITH the worksheet.

With ThisWorkbook.Sheets("Data")

    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)

    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")

End With

Run the debugger and put a breakpoint where the WorksheetFunctions start to occur. Then step through them, watching the LOCALS window to see the changes. You will see what is wrong if there is still a problem.

edited Dec 18 '14 at 00:53

answered Dec 17 '14 at 22:50

peege

2,467
1
10
24

1

In blocks of assignments like that, a `With ... End With` may be better. – Dec 17 '14 at 22:53
Agreed, and modified. – peege Dec 17 '14 at 23:00
1

alright- I tried that. The sub is where the error always is. The line of the error is at `m1 = m_range.Item(WorksheetFunction.Match(percent_methane, m_range))`. At that time, though, percent_methane is 5.5, and the range should contain values from 5 to 20. I'm not quite sure why there is an error. (also, I had never heard of/used the LOCALS window before, but will from now on! great resource) – bluefabric Dec 17 '14 at 23:18
Is it possible there are no matches – peege Dec 17 '14 at 23:21
@bluefabric - 1. Are the values in *m_range* in ascending order? You cannot use MATCH for an approximate match as you are unless they are in ascending order. 2. Is *percent_methane* actually 5.5 or 0.055? If it is 0.055 and *m_range* starts at 5, you will receive an `#N/A` error. – Dec 17 '14 at 23:28
In response and @Jeeped: on sheet "Data" from cell A1 to cell A81 there are values of methane in ascending order- there are multiple 5's, 10's, and 15's. There is no 5.5, but match should return the cell index with the greatest value that doesn't exceed 5.5 (in this case, the last 5). Is there a way to see what m_range is actually referring to? – bluefabric Dec 17 '14 at 23:35
In the LOCALS window, under m_range, CurrentArray says there are "no cells found" – bluefabric Dec 17 '14 at 23:36
@bluefabric - immediately after assigning *m_range* insert this code line: `Debug.Print "m_range is: " & m_range.Address(0, 0, xlA1, True)`. Run the sub and when you get back to the VBE, tap `Ctrl+G` to show the result in the *Immediate Window*. – Dec 17 '14 at 23:48
@bluefabric when you set the ranges, do that with the worksheet, using the WITH statement. Either that or declare each one with 'Sheets("sheetname").Range("A:A")' – peege Dec 18 '14 at 00:54
You should break the statement where the error occurs into two lines. Trying do perform calculations inside other functions doesn't always work. Such as 'TempValue = WorksheetFunction.Match(percent_methane, m_range)' Then 'm1 = m_range.Item(TempValue) ' – peege Dec 18 '14 at 12:26

score 1 · Accepted Answer · answered Dec 18 '14 at 00:41

A common mistake is neglecting to subordinate all of the appropriate .Range and .Cells references within a With ... End With block. Since you are using .Cells references to construct your .Range references, this becomes very important. Please review this rewrite; I've added some comments but the majority was simple referencing and reorganization.

'Never a good idea to use a reserved word like 'column' as a variable. I changed to 'colm'
Function InterpolateAll(methane As Double, pressure As Double, temp As Double, _
  colm As Long, 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 Long, p2 As Double, p_2_index As Long, p3 As Double, p_3_index As Long, _
  p4 As Double, p_4_index As Long, p5 As Double, p_5_index As Long, p6 As Double, _
  p_6_index As Long, p7 As Double, p_7_index As Long, p8 As Double, p_8_index As Long) As Double

    Dim v1 As Double, v2 As Double, v3 As Double, v4 As Double
    Dim v5 As Double, v6 As Double, v7 As Double, v8 As Double
    Dim vr1 As Double, vr2 As Double, vr3 As Double, vr4 As Double
    Dim vrr1 As Double, vrr2 As Double, vrrr As Double

    With Worksheets("Data")
        v1 = .Cells(p_1_index, colm)
        v2 = .Cells(p_2_index, colm)
        v3 = .Cells(p_3_index, colm)
        v4 = .Cells(p_4_index, colm)
        v5 = .Cells(p_5_index, colm)
        v6 = .Cells(p_6_index, colm)
        v7 = .Cells(p_7_index, colm)
        v8 = .Cells(p_8_index, colm)
    End With

    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 ComButt()

    Dim m1 As Double, m2 As Double
    Dim t1 As Double, t2 As Double, t3 As Double, t4 As Double
    Dim p1 As Double, p2 As Double, p3 As Double, p4 As Double
    Dim p5 As Double, p6 As Double, p7 As Double, p8 As Double
    Dim m_range As Range, p_range As Range, t_range As Range
    Dim m_start As Range, p_start As Range, t_start As Range
    Dim m_range_1_top As Long, m_range_1_bottom As Long, m_range_2_top As Long, m_range_2_bottom As Long
    Dim t_range_1_top As Long, t_range_1_bottom As Long, t_range_2_top As Long, t_range_2_bottom As Long
    Dim t_range_3_top As Long, t_range_3_bottom As Long, t_range_4_top As Long, t_range_4_bottom As Long
    Dim p_1_index As Long, p_2_index As Long, p_3_index As Long, p_4_index As Long
    Dim p_5_index As Long, p_6_index As Long, p_7_index As Long, p_8_index As Long
    Dim pressure As Double, temp As Double, percent_methane As Double
    Dim var1 As Double

    With Worksheets("Data")
        pressure = .Range("S2").Value
        temp = .Range("R2").Value
        percent_methane = .Range("Q2").Value

        'this sets start_range as a value, not the A1 cell as a range. Maybe set start_range = .Range("A1")
        'in any event, it doesn't appear to be used after this so commented out
        'start_range = .Range("A1")
        'set start_range = .Range("A1")

        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)
        ' again, are _top and _bottom transposed here?
        m_range_2_top = WorksheetFunction.Match(m2, m_range)
        m_range_2_bottom = m_range_1_top + 1

        'ALL of the range and cell references need to be referenced to the With ... End With
        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 With

    Set m_range = Nothing
    Set t_range = Nothing
    Set p_range = Nothing
    Set m_start = Nothing
    Set t_start = Nothing
    Set p_start = Nothing
End Sub

OK, so one of the first things I tried was replacing all `range(` instances with the fully qualified version: `ThisWorkbook.Sheets("Data").Range("A1")]` using [ctrl+H] in word. The revisions above cause everything to work properly (Thank you!!) so was the issue that all of the `Cells()` objects were not properly qualified? As an aside, do you know why the `worksheet("data").Activate` trick does not work? — bluefabric, Dec 18 '14 at 14:21
@bluefabric - Yes, if that has overcome the difficulties then it was trying to define a `Range` with what boiled down to `.Cells(upper boundary)` and `.Cells(lower boundary)`. The nuances of what can redefine the *ActiveSheet* in XL's perspective are many and varied. Suffice to say that it is never a reliable method of committing a cell or range reference's parentage (and totally unnecessary/unproductive). Besides, everything looks cooler wrapped in a `With ... End With` block just as long as you remember to prefix with a full stop (e.g. period or .) — , Dec 18 '14 at 16:19

Cell referencing not working in excel vba

2 Answers2