Answered by:
Log of Range
Question

Can you take the log of a range of data? Like under optionbutton2 I am trying to update the ycol range so it is a range of natural log'd values
Private Sub CommandButton3_Click() Dim xval As String Dim yval As String Dim xcol As Range Dim ycol As Range Dim ycol1 As Range Dim a As Double 'a is slope Dim b As Double 'b is intercept xval = RefEdit1.Value yval = RefEdit2.Value Set xcol = Range(xval) Set ycol = Range(yval) If OptionButton1 = True Then a = Application.WorksheetFunction.Slope(ycol, xcol) b = Application.WorksheetFunction.Intercept(ycol, xcol) MsgBox "y = ax + b" & vbNewLine & "a= " & a & vbNewLine & "b= " & b End If If OptionButton2 = True Then ycol = Log(ycol) a = Application.WorksheetFunction.Slope(ycol1, xcol) b = Application.WorksheetFunction.Intercept(ycol1, xcol) MsgBox a End If
Answers

ycol = Log(ycol) won't work if ycol consists of more than one cell. You could use
Dim cel As Range For Each cel in ycol.Cells cel.Value = Log(cel.Value) Next cel
Regards, Hans Vogelaar
 Marked as answer by Dummy yoyoModerator Monday, April 8, 2013 5:19 AM
All replies

ycol = Log(ycol) won't work if ycol consists of more than one cell. You could use
Dim cel As Range For Each cel in ycol.Cells cel.Value = Log(cel.Value) Next cel
Regards, Hans Vogelaar
 Marked as answer by Dummy yoyoModerator Monday, April 8, 2013 5:19 AM



For another one I have to do it to both X and Y so I made a celx and cely. However, when I try to run it I get an "Error 5." I don't see anything wrong with my code, do you? I tried your initial code and it worked so I don't see why this one does not.
If OptionButton3 = True Then For Each celx In xcol.Cells celx.Offset(0, 2).Value = Log(celx.Value) Next celx For Each cely In ycol.Cells cely.Offset(0, 2).Value = Log(cely.Value) Next cely a = Application.WorksheetFunction.Slope(cely, celx) b = Application.WorksheetFunction.Intercept(cely, celx) MsgBox a End If

You don't want to use celx and cely in the Slope and Intercept functions  they represent single cells. You need to use the ranges populated with log values, i.e.
a = Application.WorksheetFunction.Slope(ycol.Offset(0, 2), xcol.Offset(0, 2)) b = Application.WorksheetFunction.Intercept(ycol.Offset(0, 2), xcol.Offset(0, 2))
Regards, Hans Vogelaar

