JoScho
10-06-2014, 11:38 AM
Hello, I am relatively new to VBA, so please feel free to correct any wrong terminology.
I am creating a program that will essentially create a map based on imported .txt files and user input. My question is in 2 parts.
1. The map is in terms of horizontal and vertical distance. The minimum value will always be 0, and the maximum value is defined by a formula on Sheet4 (horizantal max=P10, vertical max=Q10) of my worksheet. The interval between values on both axis' will be set by the user ( variables x and y).
So, for example, if the the horizontal max is 1000 and the user inputs 10 for the variable x, the values in A1, B1, C1, D1.....of the map will be 0, 10, 20, 30....1,000.
How can I set up a sub to create the horizontal and vertical axis' of this map?
2. Once the axis' are set up, how can I populate the chart area with a formula? In the pre-VBA version of this, the following formula would be in B2, where Excel evaluates a large set of data for values between the proper horizontal and vertical values.
=AVERAGEIFS(Sheet4!$B:$B,Sheet4!$H:$H,">"&$A1,Sheet4!$H:$H,"<="&$A2,Sheet4!$K:$K,">"&A$1,Sheet4!$K:$K,"<="&B$1,Sheet4!$M:$M,"=1")
In B3 the code would read
=AVERAGEIFS(Sheet4!$B:$B,Sheet4!$H:$H,">"&$A2,Sheet4!$H:$H,"<="&$A3,Sheet4!$K:$K,">"&A$1,Sheet4!$K:$K,"<="&B$1,Sheet4!$M:$M,"=1")
Along with how I can populate the chart area with a formula, how can I specify the range taken up by the chart for use in later parts of the VBA code?
Let me know if I need to clarify anything. This is the code I came up with to try answering my first question, but with my limited experience, it doesn't work.
Dim myVar As Double
Dim myVar1 As Double
Worksheets("Sheet4").Activate
Set myVar = WorksheetFunction.RoundUp(Range("P10") / 10, 0).Value
Set myVar1 = WorksheetFunction.RoundUp(Range("Q10") / 10, 0).Value
Worksheets("Yield").Activate
Range("A1").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=x, Stop:=myVar, Trend:=False
Range("A1").Select
Selection.DataSeries Rowcol:=xlRows, Type:=xlLinear, Date:=xlDay, _
Step:=y, Stop:=myVar1, Trend:=False
I am creating a program that will essentially create a map based on imported .txt files and user input. My question is in 2 parts.
1. The map is in terms of horizontal and vertical distance. The minimum value will always be 0, and the maximum value is defined by a formula on Sheet4 (horizantal max=P10, vertical max=Q10) of my worksheet. The interval between values on both axis' will be set by the user ( variables x and y).
So, for example, if the the horizontal max is 1000 and the user inputs 10 for the variable x, the values in A1, B1, C1, D1.....of the map will be 0, 10, 20, 30....1,000.
How can I set up a sub to create the horizontal and vertical axis' of this map?
2. Once the axis' are set up, how can I populate the chart area with a formula? In the pre-VBA version of this, the following formula would be in B2, where Excel evaluates a large set of data for values between the proper horizontal and vertical values.
=AVERAGEIFS(Sheet4!$B:$B,Sheet4!$H:$H,">"&$A1,Sheet4!$H:$H,"<="&$A2,Sheet4!$K:$K,">"&A$1,Sheet4!$K:$K,"<="&B$1,Sheet4!$M:$M,"=1")
In B3 the code would read
=AVERAGEIFS(Sheet4!$B:$B,Sheet4!$H:$H,">"&$A2,Sheet4!$H:$H,"<="&$A3,Sheet4!$K:$K,">"&A$1,Sheet4!$K:$K,"<="&B$1,Sheet4!$M:$M,"=1")
Along with how I can populate the chart area with a formula, how can I specify the range taken up by the chart for use in later parts of the VBA code?
Let me know if I need to clarify anything. This is the code I came up with to try answering my first question, but with my limited experience, it doesn't work.
Dim myVar As Double
Dim myVar1 As Double
Worksheets("Sheet4").Activate
Set myVar = WorksheetFunction.RoundUp(Range("P10") / 10, 0).Value
Set myVar1 = WorksheetFunction.RoundUp(Range("Q10") / 10, 0).Value
Worksheets("Yield").Activate
Range("A1").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=x, Stop:=myVar, Trend:=False
Range("A1").Select
Selection.DataSeries Rowcol:=xlRows, Type:=xlLinear, Date:=xlDay, _
Step:=y, Stop:=myVar1, Trend:=False