Create arrays in macro?
 

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[i]) THEN
mark := (mark-old[i-1])*(new[i]-new[i-1])
/(old[i]-old[i-1]) + new[i-1]

for i in the range 2 to number of breakpoints. Thus old[i] is scaled to
new[i] 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

Create arrays in macro?
 

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

Create arrays in macro?
 

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[i]) THEN
' mark := (mark-old[i-1])*(new[i]-new[i-1])
' /(old[i]-old[i-1]) + new[i-1]
'
End Sub




"ManualMan" wrote in message
oups.com...
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

Create arrays in macro?
 

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

"John Smith" wrote in message
...[i]
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[i]) THEN
' mark := (mark-old[i-1])*(new[i]-new[i-1])
' /(old-old[i-1]) + new[i-1]
'
End Sub




"ManualMan" wrote in message
oups.com...
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

Create arrays in macro?
 

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)


"John Smith" wrote in message
...
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

"John Smith" wrote in message
...
I achieved this but it does not work, it must be close, I would
appreciate[i][i]
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[i]) THEN
' mark := (mark-old[i-1])*(new-new[i-1])
' /(old-old[i-1]) + new[i-1]
'
End Sub




"ManualMan" wrote in message
oups.com...
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