Interpolation of an entire row

Question

I have this code to interpolate for a certain value and its subsequent columns but I am having problems reporting it to another sheet. I am currently having problems reporting the derived results to another sheet

Sub interp()
    Dim temp As Long
    Dim var As Long
    Dim var1 As Long
    Dim xs As Range
    Dim ys As Range
    Dim lastrow As Long
    Dim lastcol As Long
    Dim vy As Long

    temp = Sheet2.Range("a1").Value

    With Worksheets(1)
        lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        lastcol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
        Set xs = Range(Cells(2, 1), Cells(lastrow, 1)) 'range for xs values

        For vy = 2 To lastcol                          'interpolating from second column to last
            Set ys = Range(Cells(2, vy), Cells(lastrow, vy))'selecting the column based on value of vy
            var = WorksheetFunction.Match(temp, xs, 1)'for forecasting estimated values
            var1 = var + 1   'for forecasting estimated values
            y = .Range(ys.Cells(var, 1), ys.Cells(var1, 1)).Value   'for forecasting estimated values
            x = .Range(xs.Cells(var, 1), xs.Cells(var1, 1)).Value   'for forecasting estimated values
            Sheet2.Range(Cells(1, vy)).Value = Application.WorksheetFunction.Forecast(temp, y, x) 'interpolated value is put in a cell dependent the value of vy
        Next vy
    End With
End Sub

please show some example data as well as expected output, and more info about the error - like "where" — ashleedawg, Apr 01 '18 at 21:46
As you haven't mentioned which line throws the error, I suspect it where you are using **Range** without the **`.`** within the **`WITH`** condition. But as @ashleedawg mentioned, you need to tell us where the error occurs — Zac, Apr 01 '18 at 21:49
You need to mention the line of code where the error appears. To find out, when the error message pops up click on the Debug button and this will take you to the VBA editor where it will highlight the line of code where it stopped — paul bica, Apr 01 '18 at 22:21
and please cleanup the code by using proper indentation and vertical spacing (one issue could be caused by the line above `Next vy`) — paul bica, Apr 01 '18 at 22:33
@paulbica yes the line above the (Next vy) that is bringing up the error message. Also tried the recommended answer suggested but didn't work. — dejioni, Apr 02 '18 at 00:28
Ok, so you fixed the indentation (and fixed the `Next vy` at the same time) which great! There are still some issues left: 1. Always use `Option Explicit` at the top of every module, and this will force you to be disciplined and declare all your variables - in your code variable `x` and `y` are not declared. 2. You must be specific with **every range** you use - this is what chris's answer explains. Never use `ActiveSheet`. If you have 2 sheets (Sheet1 and Sheet2) use either Sheet1 or Sheet2 for each range. For example when you set `xs` value... (continued in the next comment) — paul bica, Apr 02 '18 at 13:36
This line: `Set xs = Range(Cells(2, 1), Cells(lastrow, 1))` should be explicit about Sheet1 or Sheet2 - `Set xs = Sheet1.Range(Sheet1.Cells(2, 1), Sheet1.Cells(lastrow, 1))`; the same goes for `ys`. Another issue is when you set `y` and `x`: you cannot combine ranges from 2 different sheets in nested statements. This next example is not valid: `Set xs = Sheet1.Range(Sheet2.Cells(2, 1), Sheet2.Cells(lastrow, 1))` — paul bica, Apr 02 '18 at 13:41
If you create a new question no one can answer it properly because you are ambiguous about your ranges, and Excel gets confused as well: you want to determine `lastrow` and `lastcol` on what sheet (Sheet1 or Sheet2)? If you still cannot figure it out, you need to think of a minimal example to ask: _I want to get data from Sheet2.Range("A1:C5") to Sheet1.Range("D1:F5")_ — paul bica, Apr 02 '18 at 13:51

Interpolation of an entire row

0 Answers0