lakshman
04-03-2019, 07:59 AM
Hi everyone.
I am trying to filter a data set under the sheet "Regression Data" based on a filter criteria - Sheets("Input").Range("$D$4"). Then I want to run a regression based on this filtered data (only visible cells). My Y values are in column O and my X values are in columns C to M.
I feel that I have written the correct code but everytime I run the code I get a msgbox that says "Regression - X Range and Y Range cannot overlap.". I have checked several times the ranges I have specified don't seem to overlap as you can see below. I think there might be a simple solution to this that I am not seeing. I would really appreciate it if you guys can help me out on this. I can send in images of the file if you would like. The excel file is too big (100+ MB) though.
Thank you so much.
Sub Test1()'
' Test1 Macro
'
'
Sheets("Regression Data").Range("$A$1:$M$465779").AutoFilter Field:=1, Criteria1:= _
Sheets("Input").Range("$D$4")
'
Dim LR1 As Long
LR1 = Sheets("Regression Data").Range("C" & Rows.Count).End(xlUp).Row
Dim LR2 As Long
LR2 = Sheets("Regression Data").Range("O" & Rows.Count).End(xlUp).Row
'
Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Regression Data").Range("O2:O" & LR2).SpecialCells(xlCellTypeVisible) _
, Sheets("Regression Data").Range("C2:M" & LR1).SpecialCells(xlCellTypeVisible), False, False, , Sheets("Regression Output").Range( _
"$A$1:$G$31"), False, False, False, False, , False
End Sub
Sub ResetFilters()
'To clear the filter from a Single Column, specify the
'Field number only and no other parameters
Sheets("Regression Data").Range("$A$1:$M$465779").AutoFilter Field:=1
End Sub
I am trying to filter a data set under the sheet "Regression Data" based on a filter criteria - Sheets("Input").Range("$D$4"). Then I want to run a regression based on this filtered data (only visible cells). My Y values are in column O and my X values are in columns C to M.
I feel that I have written the correct code but everytime I run the code I get a msgbox that says "Regression - X Range and Y Range cannot overlap.". I have checked several times the ranges I have specified don't seem to overlap as you can see below. I think there might be a simple solution to this that I am not seeing. I would really appreciate it if you guys can help me out on this. I can send in images of the file if you would like. The excel file is too big (100+ MB) though.
Thank you so much.
Sub Test1()'
' Test1 Macro
'
'
Sheets("Regression Data").Range("$A$1:$M$465779").AutoFilter Field:=1, Criteria1:= _
Sheets("Input").Range("$D$4")
'
Dim LR1 As Long
LR1 = Sheets("Regression Data").Range("C" & Rows.Count).End(xlUp).Row
Dim LR2 As Long
LR2 = Sheets("Regression Data").Range("O" & Rows.Count).End(xlUp).Row
'
Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Regression Data").Range("O2:O" & LR2).SpecialCells(xlCellTypeVisible) _
, Sheets("Regression Data").Range("C2:M" & LR1).SpecialCells(xlCellTypeVisible), False, False, , Sheets("Regression Output").Range( _
"$A$1:$G$31"), False, False, False, False, , False
End Sub
Sub ResetFilters()
'To clear the filter from a Single Column, specify the
'Field number only and no other parameters
Sheets("Regression Data").Range("$A$1:$M$465779").AutoFilter Field:=1
End Sub