VBA and selecting non-zero values..

[segran]

Hi,

I am performing regression analysis.

I created a button, and am running the following macro -


Sub Macro5()
'
' Macro5 Macro
'

'
ActiveSheet.Range("$o$16:$w$34").Clear
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$h$15:$h$32"), _
ActiveSheet.Range("$i$15:$l$32"), False, True, , ActiveSheet.Range("$o$16") _
, False, False, False, False, , False
End Sub


Currently, I have defined Y-range as $h$15:$h$32, however, I want to automatically choose y- and x- range.

Currently $h$15:$h$32 is based on historical data. However, this range then has zero data, when another set of y-variable is chose (based on forecasted data). I would like to automatically choose all-non zero data in column h, and automatically chose the corresponding data in range i:l. For example, if the data range with non-zeros start in range ("$h$33:$h$40), then x-range will be ("$i$33:$l$40").

Thanking you in advance for your help.

Demand forecast Model Base Cur exp - Feb2010 ver18aa.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
16		D		1991	4008.1			3.603	0.00	2.11	0.00	0.00
17		E		1992	2511.4			3.400	0.00	2.13	0.00	0.00
18		F		1993	2486.9			3.396	0.00	2.14	0.00	0.00
19		G		1994	2841.9			3.454	0.00	2.18	0.00	0.00
20		H		1995	2800.5			3.447	0.00	2.21	0.00	0.00
21		I		1996	3184.7			3.503	0.00	2.23	0.00	0.00
22		J		1997	2726.1			3.436	0.00	2.26	0.00	0.00
23		K		1998	2243.8			3.351	0.00	2.28	0.00	0.00
24		L		1999	2113.4			3.325	0.00	2.29	0.00	0.00
25		M		2000	1944.5			3.289	0.00	2.31	0.00	0.00
26		N		2001	1977.0			3.296	0.00	2.33	0.00	0.00
27		O		2002	2349.2			3.371	0.00	2.33	0.00	0.00
28		P		2003	1895.2			3.278	0.00	2.49	0.00	0.00
29		Q		2004	1447.7			3.161	0.00	2.51	0.00	0.00
30		R		2005	1293.8			3.112	0.00	2.52	0.00	0.00
31		S		2006	1061.6			3.026	0.00	2.54	0.00	0.00
32		T		2007	1042.9			3.018	0.00	2.55	0.00	0.00
33		U		2008	1212.9	1212.9	16.3%	0.000	0.00	2.56	0.00	0.00
34		V		2009	1180.1	1180.1	-2.7%	0.000	0.00	2.57	0.00	0.00
35		W		2010	1155.8	1155.8	-2.1%	0.000	0.00	2.58	0.00	0.00
36		X		2011	1126.4	1126.4	-2.5%	0.000	0.00	2.59	0.00	0.00
37		Y		2012	1092.5	1092.5	-3.0%	0.000	0.00	2.61	0.00	0.00
38		Z		2013	1059.2	1059.2	-3.1%	0.000	0.00	2.62	0.00	0.00
39		AA		2014	1026.4	1026.4	-3.1%	0.000	0.00	2.64	0.00	0.00
40		AB		2015	994.1	994.1	-3.1%	0.000	0.00	2.65	0.00	0.00
41		AC		2016	962.3	962.3	-3.2%	0.000	0.00	2.66	0.00	0.00
42		AD		2017	930.9	930.9	-3.3%	0.000	0.00	2.68	0.00	0.00
43		AE		2018	900.1	900.1	-3.3%	0.000	0.00	2.69	0.00	0.00
44		AF		2019	882.4	882.4	-2.0%	0.000	0.00	2.70	0.00	0.00
45		AG		2020	866.4	866.4	-1.8%	0.000	0.00	2.71	0.00	0.00
Regression Analysis

 

[segran]

Hi,

I am still only obtaining 15 and 32 instead of 22 and 27.

I am not sure why. Please help?

 

[segran]

I get error message, input range contains non-numeric data?

Please help

 

[Andrew Poulsom]

In you original post you had zeroes for no data. Now you have "". Try:

Sub Test()
    Dim r As Integer
    Dim First As Long
    Dim Last As Long
    r = 15
    With ActiveSheet
        Do While .Range("H" & r) = ""
            r = r + 1
        Loop
        First = r
        Do Until .Range("H" & r) = ""
            r = r + 1
        Loop
        Last = r - 1
    End With
    MsgBox First & " " & Last
End Sub

 

[segran]

That did the trick Thank you Andrew.

But how do I include the labels of columns.
If I change r = 15, will that work?

 

[Andrew Poulsom]

In your original code which part included the labels of columns?

 

[segran]

Range started from 15 - which included the label, and the TRUE in the regression code, I think referred to include label.

Demand forecast Model Base Cur exp - Feb2010 ver18aaa.xlsm
	D	E	F	G	H	I	J	K	L
15	Year	Specific Consumption (kWh)	Forecast Specific Consumption (kWh)	Sales Growth rate(%)	Log Specific Consumption (kWh)	Log Price	Log GDP/Capita	Log Null Values	Log Null Values
16	1991	0.0				1.50	2.11	0.00	0.00
17	1992	0.0				1.81	2.13	0.00	0.00
18	1993	0.0				1.92	2.14	0.00	0.00
19	1994	0.0				2.05	2.18	0.00	0.00
20	1995	0.0				2.07	2.21	0.00	0.00
21	1996	0.0				1.96	2.23	0.00	0.00
22	1997	203620.4			5.309	1.92	2.26	0.00	0.00
23	1998	146329.1			5.165	1.89	2.28	0.00	0.00
24	1999	164130.4			5.215	1.90	2.29	0.00	0.00
25	2000	341595.9			5.534	1.98	2.31	0.00	0.00
26	2001	360726.1			5.557	2.19	2.33	0.00	0.00
27	2002	309259.3			5.490	2.25	2.33	0.00	0.00
28	2003	0.0				2.23	2.49	0.00	0.00
29	2004	0.0				2.24	2.51	0.00	0.00
30	2005	0.0				2.29	2.52	0.00	0.00
Regression Analysis

 

[Andrew Poulsom]

That won't be possible because the regress function expects the labels to be in row 1 of the input range.

 

[segran]

Thank you Andrew. You was extremely helpful. Thank You. Thank you

 

[segran]

Hi,

Hope you all are well.

I have a follow-up question to my last post regarding this example.

The code give me the first and last row line, example row 32, when it outputs.

I have the following excel funstion in column E.

IF(D16<VLOOKUP($F$5,beta,2,FALSE),SUMPRODUCT((consumer=$F$5)*(sega=$F$7)*(date=$D16)*data),F16))

The vlookup function, essentially chooses between years 2008 and 2030.Thereafter, if the reference cell (D16), which refers to column D (years), is less than 2008 for example, then it prints sumproduct in colum E as a result, else F16.

I would like to use the Last result as in the code, such that if D16<VLOOKUP($F$5,beta,2,FALSE)<Last (but converted in terms of years, like column D), then it prints sumproduct, else F16.

Also, how do I place the last result converted to a year, for example Last = 29, refers to year 2004 (in column D), in a cell in the sheet.

I hope I made sense.

Thank you in advance for your help.

 

[Andrew Poulsom]

In your macro you can place the year in a cell, eg A1, like this:

Range("A1").Value = Range("D" & Last).Value