On Aug 22, 2:48 pm, "Peter T" <peter_t@discussions wrote:
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

wrote in message

ups.com...



On Aug 22, 9:46 am, "Peter T" <peter_t@discussions wrote:
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

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.-
Hide
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.- Hide 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- Hide quoted text -

- 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