New array with old array elements

[mareharbor]

I am looking to create a new array with elements of a previously
created array. Where:

For Each cell In Ratings
ReDim Preserve Ratings1(6, rw)

Ratings1(0, rw) = Ratings(0, rw)
Ratings1(1, rw) = Ratings(1, rw)
Ratings1(2, rw) = Ratings(2, rw)
Ratings1(3, rw) = Ratings(3, rw)
Ratings1(4, rw) = Ratings(4, rw)
Ratings1(5, rw) = Ratings(5, rw)
rw = rw - 1
Next Cell

"Ratings" is my old array and "Ratings1" is my new array that i am
creating. "Ratings" is in the same sub and will have the same number
of row elements. For some reason this is not working. When im done not
all elements of Ratings1 will be the same as the Ratings, but im just
trying figure this part out. Thanks

 

[Bob Phillips]

What are you trying to do?

You setup a loop to look through each cell in Ratings, then ignore the value
obtained each time. You redim your array each iteration of the loop, thereby
losing data each time. etc.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[mareharbor]

What are you trying to do?

You setup a loop to look through each cell in Ratings, then ignore the value
obtained each time. You redim your array each iteration of the loop, thereby
losing data each time. etc.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)









- Show quoted text -

I guess i am just trying to create a new array with old array
elements, but i see your point however i am using redim preserve and
not just redim. I want to create array Ratings1 from Ratings.

 

[Peter T]

not just redim. I want to create array Ratings1 from Ratings.

If you mean copy Ratings to Ratings1, simply

Ratings1 = Ratings

Ratings may have been declared as an array but ....

Dim Ratings1 as Variant

Regards,
Peter T

 

[mareharbor]

If you mean copy Ratings to Ratings1, simply

Ratings1 = Ratings

Ratings may have been declared as an array but ....

Dim Ratings1 as Variant

Regards,
Peter T







- Show quoted text -

Thanks, but in reality i want to have some rows of the new array to be
different values, i was more just looking for the reason that i
couldn't just assign elements of the old array to elements of the new
array by saying that for example Ratings1(1, 5) = Ratings(1,5). For
some reason this is not working.

 

[Bob Phillips]

Then maybe either array doesn't have that particular element.

How is Ratings populated, and where does rw get set?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Peter T]

Sorry I don't follow what you are trying to do

Ratings1(1, 5) = Ratings(1,5).

Providing each array is sized to accommodate (1,5), and Ratings1 can accept
similar data type to that in Ratings(1,5), the above will work

Regards,
Peter T

 

[mareharbor]

Sorry I don't follow what you are trying to do

Ratings1(1, 5) = Ratings(1,5).

Providing each array is sized to accommodate (1,5), and Ratings1 can accept
similar data type to that in Ratings(1,5), the above will work

Regards,
Peter T

<snip>





quoted text -


- Show quoted text -

thanks for all your help. Here is my full code where ratings gets set.
I actually want the first element of Ratings1 to somehow lookup and
return a new value that is a little different to what was in the old
Ratings array. But that is a seperate issue, for now i just would like
the basic structure to work. Here is the full code where Ratings is
derived and rw is set:

Public Sub Ratings()
Dim cell As Variant
Dim x, y As Integer
Dim Ratings(), rTicker(), Ratings1() As String
Dim AsDte As Date
Dim iLastRow, i As Long
AsDte = InputBox("What Is the As-of-day?")
Workbooks.Open Filename:= _
"M:\CBO's\Surveillance\TEMPLATES\MKP Rating Tracker\MKP Rating
Tracker V2.xls" _
, UpdateLinks:=0, Notify:=False, ReadOnly:=True

Sheets("All Actions").Activate


i = 3368
While (Sheets("All Actions").Cells(i, 9) <> "")
If (UCase(Trim(Sheets("All Actions").Cells(i, 1))) = "CBO1" Or
UCase(Trim(Sheets("All Actions").Cells(i, 1))) = "CBO2" _
Or UCase(Trim(Sheets("All Actions").Cells(i, 1))) = "CBO3" Or
UCase(Trim(Sheets("All Actions").Cells(i, 1))) = "CBO4" _
Or UCase(Trim(Sheets("All Actions").Cells(i, 1))) = "CBO5" Or
UCase(Trim(Sheets("All Actions").Cells(i, 1))) = "CBO6" _
Or UCase(Trim(Sheets("All Actions").Cells(i, 1))) = "CBO7") _
And UCase(Trim(Sheets("All Actions").Cells(i, 9))) > AsDte Then
ReDim Preserve Ratings(6, rw)
Ratings(0, rw) = Sheets("All Actions").Cells(i, 2).Value
Ratings(1, rw) = Sheets("All Actions").Cells(i,
3).Value
Ratings(2, rw) = Sheets("All
Actions").Cells(i, 7).Value
Ratings(3, rw) = Sheets("All Actions").Cells(i,
8).Value
Ratings(4, rw) = Sheets("All Actions").Cells(i, 9).Value
Ratings(5, rw) = Sheets("All Actions").Cells(i, 10).Value
rw = rw + 1
End If
i = i + 1
Wend

Workbooks.Open Filename:="M:\CBO's\Collateral Attributes\MKP CBO
Collateral Attributes.xls" _
, UpdateLinks:=0, Notify:=False, ReadOnly:=True

Sheets("Portfolio").Activate

For Each cell In Ratings
ReDim Preserve Ratings1(6, rw)
Ratings1(0, rw) = Application.VLookup(cell.Value,
Sheets("Portfolio").Range("EK4:EL1200"), 2, False)
Ratings1(1, rw) = Ratings(1, rw)
Ratings1(2, rw) = Ratings(2, rw)
Ratings1(3, rw) = Ratings(3, rw)
Ratings1(4, rw) = Ratings(4, rw)
Ratings1(5, rw) = Ratings(5, rw)
rw = rw - 1
Next cell

Windows("RATINGSupdate.xls").Activate
x = 4
For y = 0 To rw - 1
Cells(x, 1).Value = Ratings1(0, y)
Cells(x, 2).Value = Ratings1(1, y)
Cells(x, 3).Value = Ratings1(2, y)
Cells(x, 4).Value = Ratings1(3, y)
Cells(x, 5).Value = Ratings1(4, y)
Cells(x, 6).Value = Ratings1(5, y)
x = x + 1
Next


End Sub


Thanks, preciate it

 

[Peter T]

I tried to decipher your code but gave up. Is it possible to post a
simplified example that can be pasted into a module.

Suggest don't name your procedure as the same name as one of its arrays,

Public Sub Ratings()
Dim Ratings()

If your array has multiple columns probably best not to do this

For Each cell In Ratings

or how to track which dimension you are in

instead try -

dim r as long, c as long
for r = lbound(Ratings) to ubound(Ratings)
for c = lbound(Ratings, 2) to ubound(Ratings, 2)

x = Ratings(r,c)

Regards,
Peter T

 

[mareharbor]

I tried to decipher your code but gave up. Is it possible to post a
simplified example that can be pasted into a module.

Suggest don't name your procedure as the same name as one of its arrays,


If your array has multiple columns probably best not to do this> For Each cell In Ratings

or how to track which dimension you are in

instead try -

dim r as long, c as long
for r = lbound(Ratings) to ubound(Ratings)
for c = lbound(Ratings, 2) to ubound(Ratings, 2)

x = Ratings(r,c)

Regards,
Peter T














- Show quoted text -

Here i have slimmed down the code and gotten rid of all the file paths
and assumed that the different sheets are all in the same workbook. I
think that it is clearer. Thanks a bunch

Public Sub Updater()
Dim cell As Variant
Dim x, y As Integer
Dim Ratings(), rTicker(), Ratings1() As String
Dim AsDte As Date
Dim iLastRow, i As Long
AsDte = InputBox("What Is the As-of-day?")
'Creates the first array given that the date in that column is greater
then AsDte
i = 2
While (Sheets("All Actions").Cells(i, 9) <> "")
If UCase(Trim(Sheets("All Actions").Cells(i, 9))) > AsDte Then
ReDim Preserve Ratings(3, rw)
Ratings(0, rw) = Sheets("All Actions").Cells(i, 2).Value
Ratings(1, rw) = Sheets("All Actions").Cells(i,
3).Value
Ratings(2, rw) = Sheets("All Actions").Cells(i, 7).Value
rw = rw + 1
End If
i = i + 1
Wend

'this sheet has 2 columns the first with the contents of Ratings(o,
rw) and the second with another identifier
'that i want in the new array Ratings1 instead of the old identifier

Sheets("Portfolio").Activate
'I realize that there are issues with the vlookup but i really want to
know how to create the new array
'with the old data
For Each cell In Ratings
ReDim Preserve Ratings1(3, rw)
Ratings1(0, rw) = Application.VLookup(cell.Value,
Sheets("Portfolio").Range("EK4:EL1200"), 2, False)
Ratings1(1, rw) = Ratings(1, rw)
Ratings1(2, rw) = Ratings(2, rw)
rw = rw - 1
Next cell

End Sub

 

[Peter T]

Not sure but instead of -

For Each cell In Ratings
ReDim Preserve Ratings1(3, rw)
Ratings1(0, rw) = Application.VLookup(cell.Value,
Sheets("Portfolio").Range("EK4:EL1200"), 2, False)

Try
REdim Ratings1(lBound(Ratings) to Ubound(Ratings), _
lBound(Ratings, 2) to Ubound(Ratings, 2)

then as I mentioned before

For rw = lBound(Ratings, 2) to Ubound(Ratings, 2)
Ratings1(0, rw) = I don't understand the lookup part
If you need to track the original cell row ref, give Ratings an extra level
in the first dimension and store
the row number in that for later use with the Lookup

and why the rw = rw - 1
maybe Step - 1 ?
Ratings1(1, rw) = Ratings(1, rw)
Ratings1(2, rw) = Ratings(2, rw)
Next

In passing -

Dim Ratings(), rTicker(), Ratings1() As String

is same as
Dim Ratings() As Variant, rTicker() As Variant, Ratings1() As String

perhaps also
Dim rw as Long

Regards,
Peter T