Create arrays in macro?

[John Smith]

We need to manipulate a column of numbers
In Sheet 1
Column A - mark (can have any number of entries. Entries have a value from 0
to 100)
25 36 49 20 18 etc
Column B - old (2-5 entries)
0 45 60 90
Column C - new (2-5 entries)
0 45 55 85

The macro that I need to implement is:
IF (mark > old[i-1]) and (mark <= old) THEN
mark := (mark-old[i-1])*(new-new[i-1])
/(old-old[i-1]) + new[i-1]

for i in the range 2 to number of breakpoints. Thus old is scaled to
new for each i, with linear interpolation for in between marks.

I can do this in C++ or C#, but have no idea of VBA.

I would appreciate some help.

thank you

Mark

(e-mail address removed)

 

[ManualMan]

Mark,

I have absolutely *no* clue what you mean with linear interpolation,
but using arrays certainly is possible in VBA

The sub underneath fills three arrays with 10 values in columns A, B
and C of the currently selected sheet

Sub Arrays()
Dim ColA(1 To 10) As Long
Dim ColB(1 To 10) As Long
Dim ColC(1 To 10) As Long

For RowCount = 1 To 10
ColA(RowCount) = Cells(RowCount, 1)
ColB(RowCount) = Cells(RowCount, 1)
ColC(RowCount) = Cells(RowCount, 1)
Next RowCount

End Sub

Regards,
ManualMan
http://www.gamesxl.tk

 

[John Smith]

I achieved this but it does not work, it must be close, I would appreciate
some ideas

Public Sub AdjustMarks()
Set m = Range("Mark")
Set o = Range("Old")
Set n = Range("New")
Set a = Range("Adjusted")

Dim b As Integer
Dim c As Integer
Dim u As Integer
u = m.Rows.Count
Dim v As Integer
v = o.Rows.Count

Dim w As Double

Dim d As Double
Dim e As Double
Dim f As Double
Dim g As Double
Dim h As Double


For b = 1 To u
For c = 2 To v
d = m.Cells(p, 1).Value
e = o.Cells(q, 1)
f = o.Cells(q - 1, 1)
g = n.Cells(q, 1)
h = n.Cells(q - 1, 1)
If ((d > e) And (d <= f)) Then
w = ((d - f) * (g - h)) / ((e - f))
a.Cells(p, 1) = w + h
End If
Next c
Next b

'
' IF (mark > old[i-1]) and (mark <= old) THEN
' mark := (mark-old[i-1])*(new-new[i-1])
' /(old-old[i-1]) + new[i-1]
'
End Sub

 

[John Smith]

I have worked out most of it. Now all I need to know is how to determine the
number of rows used in a range where the range is a worksheet column.
The number of rows changes for each operation.
I would appreciate ideas.
mark

I achieved this but it does not work, it must be close, I would appreciate
some ideas

Public Sub AdjustMarks()
Set m = Range("Mark")
Set o = Range("Old")
Set n = Range("New")
Set a = Range("Adjusted")

Dim b As Integer
Dim c As Integer
Dim u As Integer
u = m.Rows.Count
Dim v As Integer
v = o.Rows.Count

Dim w As Double

Dim d As Double
Dim e As Double
Dim f As Double
Dim g As Double
Dim h As Double


For b = 1 To u
For c = 2 To v
d = m.Cells(p, 1).Value
e = o.Cells(q, 1)
f = o.Cells(q - 1, 1)
g = n.Cells(q, 1)
h = n.Cells(q - 1, 1)
If ((d > e) And (d <= f)) Then
w = ((d - f) * (g - h)) / ((e - f))
a.Cells(p, 1) = w + h
End If
Next c
Next b

'
' IF (mark > old[i-1]) and (mark <= old) THEN
' mark := (mark-old[i-1])*(new-new[i-1])
' /(old-old[i-1]) + new[i-1]
'
End Sub

Mark,

I have absolutely *no* clue what you mean with linear interpolation,
but using arrays certainly is possible in VBA

The sub underneath fills three arrays with 10 values in columns A, B
and C of the currently selected sheet

Sub Arrays()
Dim ColA(1 To 10) As Long
Dim ColB(1 To 10) As Long
Dim ColC(1 To 10) As Long

For RowCount = 1 To 10
ColA(RowCount) = Cells(RowCount, 1)
ColB(RowCount) = Cells(RowCount, 1)
ColC(RowCount) = Cells(RowCount, 1)
Next RowCount

End Sub

Regards,
ManualMan
http://www.gamesxl.tk

 

[Bob Phillips]

John,

You can get the last row in a column with

cLastRow = Cells(Rows.Count,"A").End(xlUp).Row

If your data starts in row 1. then cLastRow is the number of rows. If it
starts somewhere else, you will need to subtract that row -1 from it.

If you are using a named range, it is easier with

cRows = Range("myRange").Rows.Count

--

HTH

RP
(remove nothere from the email address if mailing direct)

I have worked out most of it. Now all I need to know is how to determine the
number of rows used in a range where the range is a worksheet column.
The number of rows changes for each operation.
I would appreciate ideas.
mark

I achieved this but it does not work, it must be close, I would appreciate
some ideas

Public Sub AdjustMarks()
Set m = Range("Mark")
Set o = Range("Old")
Set n = Range("New")
Set a = Range("Adjusted")

Dim b As Integer
Dim c As Integer
Dim u As Integer
u = m.Rows.Count
Dim v As Integer
v = o.Rows.Count

Dim w As Double

Dim d As Double
Dim e As Double
Dim f As Double
Dim g As Double
Dim h As Double


For b = 1 To u
For c = 2 To v
d = m.Cells(p, 1).Value
e = o.Cells(q, 1)
f = o.Cells(q - 1, 1)
g = n.Cells(q, 1)
h = n.Cells(q - 1, 1)
If ((d > e) And (d <= f)) Then
w = ((d - f) * (g - h)) / ((e - f))
a.Cells(p, 1) = w + h
End If
Next c
Next b

'
' IF (mark > old[i-1]) and (mark <= old) THEN
' mark := (mark-old[i-1])*(new-new[i-1])
' /(old-old[i-1]) + new[i-1]
'
End Sub

Mark,

I have absolutely *no* clue what you mean with linear interpolation,
but using arrays certainly is possible in VBA

The sub underneath fills three arrays with 10 values in columns A, B
and C of the currently selected sheet

Sub Arrays()
Dim ColA(1 To 10) As Long
Dim ColB(1 To 10) As Long
Dim ColC(1 To 10) As Long

For RowCount = 1 To 10
ColA(RowCount) = Cells(RowCount, 1)
ColB(RowCount) = Cells(RowCount, 1)
ColC(RowCount) = Cells(RowCount, 1)
Next RowCount

End Sub

Regards,
ManualMan
http://www.gamesxl.tk