Setting values from array

[AlexJr]

Good day,

I'm want to debug.print two variables (Relation, Account) based on the values from an array.

Relation Account
A 1
B 2
C 3
D 4
E 5

When a try to set the variable I receive a object-error.

Does anyone know what I'm doing wrong?

Please Login or Register  to view this content.

[TheSilkCode]

Hi AlexJr-

I believe the following line would generate an error:

Please Login or Register  to view this content.

because since it is after the next j line, j will actually be UBound(ReferenceDataRange, 2)+1 at that point, outside of the array bounds, which would generate an error. Unclear what you are doing with that line of code but make sure that j is within the upper bound of the array's columns when you call it...

Hope that helps,
TheSilkCode

[YasserKhalil]

What are you trying to do?
Do you need to debug the values of the stored items in the array?

[YasserKhalil]

Try this

Please Login or Register  to view this content.

[AlexJr]

Thanks! That resolved it.

[YasserKhalil]

You're welcome. Glad i can offer some help

[AlexJr]

Would you happen to know how I can best have it find (and debug.print) each cell address for each relation?

I added the code that I was working together with your solution but now the address line is broken.

Looking forward to your reply.

Please Login or Register  to view this content.

[protonLeah]

If you leave Relation and Account as Ranges then:
Debug.Print Relation.Value, etc.
Then Debug.Print Relation.Address will give you the string address.

[AlexJr]

If I change Relation and Account as Ranges the below code breaks

Please Login or Register  to view this content.

[protonLeah]

Set Relation...

[AlexJr]

Do you mean as below? It is still broken.

Please Login or Register  to view this content.

[protonLeah]

You must do a FIND before you can use FindNext

No workbook so can't test:

Please Login or Register  to view this content.

[AlexJr]

That makes sense but the below code is still broken.

How can I resolve?

Please Login or Register  to view this content.

[protonLeah]

My mistake, trying to post without testing. ReferenceDataARange is not a range object but a 2D array so the .Value is wrong.
relation.Value = referenceDataRange(i, 1)
account.Value = referenceDataRange(i, 2)

[AlexJr]

This is still not working. I'm receiving a object variable or with block variable not set runtime error 91.

I have tried different variation by adding a with block, objects etc but at this point it is guess work for me.

Below is my actual code.

Do you know what is wrong?

Please Login or Register  to view this content.

[protonLeah]

When you declared the variables Relation and Account as ranges with:

Dim Relation As Range
Dim Account As Range

You were saying that you were going to use Relation and Account to "refer to" or "point to" ranges of cells in a worksheet.
Once you declare your intentions (i.e., DIM), they must be made to actually point to/refer to some cells. This is done with the SET Statement.

Dim only grabs some empty/unused available memory area and reserves (tags) it Relation or Account (in this case). You must point them to a range of cells:
Set Relation = some cell/range of cells.
Set Account = some other cell/range of cells

Once you have pointed them to the cells you can then use

With Relation
.Value = ReferenceDataRange(i, 1)
End With
Account.Value = ReferenceDataRange(i, 2)

There is no Set for Relation and Account so the With Relation translates to With NOWHERE, put the data from my array... which results in the Object or With block error.

---

But!
Notice that, in your code, you are only using Relation and Account to hold data values from an array. They never are used to refer to ranges of cells; therefore, they cannot be declared as Range variables. I don't know what kind of data you are working with (no workbook to look at ) so I'm guessing (again) that it is string or maybe integer.

Dim Relation as ? (variant, string, long?)

Dim Account as ...
Relation = ReferenceDataRange(i, 1)
Account = ReferenceDataRange(i, 2)

I've noticed that you want to assign an account number after Relation is found by the search code, but there is no code to actually put that back in a cell in the workbook. So it looks like you still need code to put the account in some cell in the same row as that where the relation is found (???)

---

Please Login or Register  to view this content.

[AlexJr]

Thanks! Changing Relation and Account to Variant and removing .value did it.

Indeed the last step is for the related account to be entered into the cell two columns to the left of the found address.

I've added the following code and it works perfectly.

Please Login or Register  to view this content.

Thanks for your help!

[protonLeah]

You only need one line

Please Login or Register  to view this content.

[AlexJr]

Oh okay, thanks! That looks better indeed!