Log10 function on a range - type 13 mismatch error

Question

I am trying to use the LinEst function to take values from a range of rows of data and input them into a new sheet under some headings. I only want to do this for a particular number of rows (up to row number defined as "c". My VBA skills are very basic, and I have had help producing the code below.

The issue is that I want to transform a range of data (xrng and yrng) by taking log to the base 10 of it. But when I try to use the Log10 or WorksheetFunction.Log10 function, it specifies that the data must be a double and I get a type mistmatch error if I run the code.

Option Explicit

Sub Button7_Click()

    Dim xrng As Range, yrng As Range, lxrng As Range, lyrng As Range
    Dim Drop As Range
    Dim Arr As Variant                          ' LinEst result array
    Dim Rng As Range
    Dim R As Long
    Dim l As Long
    Dim k As Long
    Dim c As Long
    Dim DownSweep As Chart, UpSweep As Chart, cht As Chart
    Dim ws As Worksheet, Smallest As Variant
    Dim dsws As Worksheet

    Set ws = Worksheets("Template")
    Sheets.Add.Name = "Down Sweep Power Law"
    Set dsws = Worksheets("Down Sweep Power Law")
    Set Rng = ws.Range(ws.Range("B11"), ws.Range("B11").End(xlDown))

    Smallest = WorksheetFunction.Small(Rng, 1)
    l = Rng.Find(what:=Smallest, LookIn:=xlValues, LookAt:=xlWhole).Row
    k = Rng.Rows.Count
    c = l - 10
    R = 1

    Set xrng = ws.Range("C11:CP11")
    Set yrng = ws.Range("C201:CP201")
    Set Drop = dsws.Range("C2:CP2").Offset(0, -2)

    dsws.Range("A1").Value = "(n-1) Value"
    dsws.Range("B1").Value = "log(k) Value"
    dsws.Range("C1").Value = "n Value"
    dsws.Range("D1").Value = "k Value"
    dsws.Range("E1").Value = "R Value"

    Do While R < c
        Arr = Application.LogEst(Log10(yrng), Log10(xrng), True, False)
        Drop.Value = Arr    ' or perhaps: = Application.Transpose(Arr)
        Set xrng = xrng.Offset(1, 0)
        Set yrng = yrng.Offset(1, 0)
        Set Drop = Drop.Offset(1, 0)
        R = R + 1
    Loop
End Sub

Any help would be appreciated.

Answer 1

Try this piece of code, please. Not tested from obvious reasons. I must confess that I couldn't understand where the Droprange must exist. Its row must be updated for each cell iteration. Looking to theLogEst` function definition, it receives a range like argument. Not Log10 which cannot be applied to a range... If you need the logarithmic value of each range cell, a preliminary range processing must be done.

Sub testLog10()
 Dim ws As Worksheet, dsws As Worksheet, Arr As Variant, Drop As Range
  Set ws = ActiveSheet 'use here your sheet
  dsws = Worksheets("Your sheet") ' use here your sheet name
        Arr = Application.LogEst(ws.Range(ws.Cells(11, 3), ws.Cells(11, 94)), _
                                ws.Range(ws.Cells(201, 3), ws.Cells(201, 94)), True, False)
      Set Drop = dsws.Range("A2")
      Drop.Resize(1, UBound(Arr)).value = Arr
End Sub