Problem with ubound array after delete elements

[Nervatos]

Hello there

I have tried to make an array and then I want to delete some of the elements. But the problem is that, UBound i still the same after deleting. How can I fix that?

Cause I need it to be pasted to A1 and then to D1 (cause I deleted two letters, If I don't delete some letters then it should be to F1).

So it could look like:
A B C D E F

And then I want to put some vlookup under every letter just for 10 down.

B C E F
Vlookup Vlookup Vlookup Vlookup
Vlookup Vlookup Vlookup Vlookup
Vlookup Vlookup Vlookup Vlookup
Vlookup Vlookup Vlookup Vlookup
Vlookup Vlookup Vlookup Vlookup
Vlookup Vlookup Vlookup Vlookup
Vlookup Vlookup Vlookup Vlookup
Vlookup Vlookup Vlookup Vlookup
Vlookup Vlookup Vlookup Vlookup
Vlookup Vlookup Vlookup Vlookup

The vlookup should I made like formula VBA.

Some there can see what I'm doing wrong?
Hope you can understand my bad English. Have a great night!

Function MyArray()
    Dim arr, arr2
    arr = Array("A", "B", "C", "D", "E", "F")
    Dim c As Long: c = 0
    Dim j As Long, jj As Long: jj = 1
    ReDim arr2(1 To UBound(arr), 1 To UBound(arr, 2))
    
    For j = 1 To UBound(arr)
        If arr(j, 1) <> "A" And arr(j, 1) <> "D" Then
            arr2(jj, 1) = arr(j, 1)
            jj = jj + 1
            c = c + 1
        End If
    Next j
    
    ReDim Preserve arr2(c)
    MyArray = arr2
End Function

 

[StephenCrump]

Sorry, it's not clear why you need to create an array in VBA.

It looks like you want to use VBA to write VLOOKUP formulae in B1:10, C1:C10, E1:E10 and F1:F10?

Is that correct? If so, what do your VLOOKUP formulas look like?

 

[Nervatos]

Hello Stephen

I forgot the another code.

Sub test()
    Dim i, j As Long
    Dim v As Variant
    
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Ark45")
    Dim Target As Range: Set Target = Range("E1")
    
    With ws
        For i = LBound(MyArray(), 1) To UBound(MyArray(), 1)
            Cells(1, i + 4).Value = MyArray()(i, 1)
            For j = 1 To 10
                Cells(j + 1, i + 4).Value = j
            Next j
        Next i
    End With
End Sub

I want to create an VBA array cause of the list is dynamic and I use it on many other sheets. So for me, it's more perfekt to just be in an function.

I got this result then I run this code:

So it's taking coloumn I and J with, cause of UBound(MyArray(), 1) result 6 and not 4.
I want in the cells down like this: =IfError(Vlookup(A2,B!A:C,3,FALSE),"") =IfError(Vlookup(A3,B!A:C,3,FALSE),""). The name of the sheet is in the top. So B is also the named of the Sheet where it have to search.

Hope you understand me now. I'm sorry about my English.

 

[StephenCrump]

Sorry, I still don't understand why you are using VBA code to write numbers to the worksheet.

Is this what you are trying to do with the VLOOKUPs?

	A	B	C	D	E
1		B	C	E	F
2	xxx	3	18	7	11
3	yyy	1	15		22
4	zzz	2	12	6	33
Sheet1

Cell Formulas
Range	Formula
B2:E4	B2	=IFERROR(VLOOKUP($A2,INDIRECT("'"&B$1&"'!A:C"),3,),"")

	A	B	C
1
2
3
4	yyy		1
5
6
7
8	zzz		2
9
10
11	xxx		3
B

	A	B	C
1
2	zzz		12
3
4	yyy		15
5	xxx		18
C

Book4
	A	B	C
1
2	zzz		6
3
4
5	xxx		7
E

	A	B	C
1
2	xxx		11
3
4	yyy		22
5
6
7
8	zzz		33
F

 

[Nervatos]

Yes, the VLOOKUP is perfect - thanks!

I do it in VBA, as I have another code that makes sheets by array (same array that should make an overview). Then I need an overview of all those sheets together. So it finds data from the different sheets. It should run automatically and dynamically, so that if more users come on - then they come automatically.

A B C D E F G is just instead of names, but there are some names that should not be part of this count. That's why I try to do it that way. It's probably not quite right and can probably also be made much better. But I'm not the best at VBA, unfortunately.

 

[StephenCrump]

You could generate these formulae using VBA, perhaps something like this:

arr = Array("B", "C", "E", "F")
c = UBound(arr) - LBound(arr) + 1

Range("B1").Resize(, c).Value = arr
Range("B2").Resize(Range("A" & Rows.Count).End(xlUp).Row - 1, c).Formula = "=IFERROR(VLOOKUP($A2,INDIRECT(""'""&B$1&""'!A:C""),3,),"""")"

 

[Nervatos]

Hello Stephen

That works great. But If I have an array at A B C D E F, and want to remove A and D? Cause I get the array list from cell value from another sheet? How would be the best way to do that be my function?

 

[StephenCrump]

That works great. But If I have an array at A B C D E F, and want to remove A and D? Cause I get the array list from cell value from another sheet? How would be the best way to do that be my function?

You could do something like this, similar to your code in Post #1:

Sub MyArray()
    
    Dim arr As Variant, arr2 As Variant
    Dim i As Long, c As Long
    
    arr = Array("A", "B", "C", "D", "E", "F")
    arr2 = arr
      
    c = LBound(arr)
    For i = LBound(arr) To UBound(arr)
        'Your test here to eliminate some elements of the array
        If arr(i) <> "A" And arr(i) <> "D" Then
            arr2(c) = arr(i)
            c = c + 1
        End If
    Next i
    
    Range("B1").Resize(, c - LBound(arr)).Value = arr2

End Sub

 

[Nervatos]

Thank you Stephen! It's great.

 

[StephenCrump]

You're welcome. I'm glad it works for you.