Dealing with ranges of any size

[mhorsnell]

Hi Everyone,

I am writing a sub that asks the user to enter a 2D range, which is then sorted, averaged and interpolated/extrapolated to find a y value belonging to an x value entered by the user. The sub needs to be capable of dealing with ranges of any length. I am fairly new to VBA so I do not understand how to do this. I know this is not right but it is what I started with just to write something that worked for the averaging, sorting and interpolation.

The range entered is defined as a range, it is split into two columns, xr and yr. The only way I could make it work was to have xr and yr defined as Single. Below is my Dim section

Dim rowc, i, j, k, yrcount As Integer
Dim xr(1 To 10), yr(1 To 10), xrsort(1 To 10), yrsort(1 To 10), yrsortav(1 To 10) As Single
Dim x As Single ' x is the number not present in the range entered by the user
Dim x1, x2, tempx, tempy As Single
Dim y1, y2, y As Single '
Dim rangein As Range ' this is the range that must be entered by the user after the button is pressed
Dim Ret As String
Dim yrsum, yrav As Single

What do I need to Dim xr and yr as to make them work for ranges of all sizes?

Thanks in advance
Michael

 

[hiker95]

mhorsnell,

I would suggest that your change the following line of code:

Dim rowc, i, j, k, yrcount As Integer

To this:

Dim rowc As Long, i As Long, j As Long, k As Long, yrcount As Long


And, do the same type of declaration statements for the rest of your variables in a single row statement.

 

[hiker95]

mhorsnell,

See if something, in the below link(s), of my most up to date list will help you:

Training / Books / Sites as of 11/15/2017

https://www.mrexcel.com/forum/excel-questions/1031751-two-novice-questions.html