Assign values to 2D dynamic arrays

**dluhut** · 06-19-2024, 09:33 AM

Hello,

I have a simple VBA question.

For each 'Y' or 'Yes' values found in column C, I'd like to add its corresponding row values (column A and column B) to a 2D array.

Therefore I need it to be dynamic "row" wise, but column wise it's fixed to 2 columns.

How do I do it?

Note here that screenshot of column A and Column B is a dynamic array (in other words A2# = A2:B9). Column C is where user input will be

Given the print screen below, I'd like my result:
arr(1,1) = B
arr(1,2) = Ran2
arr(2,1) = D
arr(2,2) = Ran4

Please Login or Register  to view this content.

2D Dynamic Array VBA.png

**Sintek** · 06-19-2024, 09:41 AM

One way...

Please Login or Register  to view this content.

**dluhut** · 06-19-2024, 09:47 AM

@Sintek...thanks for the help.

Quick question. In your code, you used a filter and looked for "Y"

What happenend if user input 'Yes', 'yEs', 'y', 'Y' etc?

That's the reason why I'm doing a loop and check via UCASE for both 'YES' and 'Y'.

Can FILTER be able to do check with UCase of values?

**Sintek** · 06-19-2024, 09:51 AM

works for all...

**dluhut** · 06-19-2024, 10:02 AM

Sorry...am not really an expert in VBA.

Tried changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

But got a Run-time error 13 Type mismatch error in

data = Filter(Evaluate("Transpose(If(Left(" & .Columns(3).Address & ",1)=""Y"", row(1:" & Rows.Count & ")))"), False, 0)

**dluhut** · 06-19-2024, 10:13 AM

Solved and reps up to @Sintek

Change a little of your code to

Please Login or Register  to view this content.

**jindon** · 06-19-2024, 10:14 AM

Please Login or Register  to view this content.

Assign values to 2D dynamic arrays

LinkBack

Thread Tools

Rate This Thread

Display

Assign values to 2D dynamic arrays

Re: Assign values to 2D dynamic arrays

Re: Assign values to 2D dynamic arrays

Re: Assign values to 2D dynamic arrays

Re: Assign values to 2D dynamic arrays

Re: Assign values to 2D dynamic arrays

Re: Assign values to 2D dynamic arrays

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] compare dynamic arrays, return missing values

[SOLVED] sum values, each row, dynamic arrays, return spilling column

[SOLVED] map values dynamic arrays

[SOLVED] matching values 2 dynamic arrays

[SOLVED] Sum values based on variable criteria [dynamic arrays]

[SOLVED] Efficient VBA method for looking up cell values - dynamic sheet references and arrays

How do you create three dimensional arrays and assign their values from mult worksheets?

Bookmarks

Bookmarks

Posting Permissions