comparing two sets of arrays by looping and entering values using vlookup

[clarence teo]

Hi,

I am new to vba here. WIll really appreciate some help with my code.

Here's what I'm trying to do:

1. I created array(i,j) to input a certain set of values2. I created another array(k,l) to input another set of values belonging to
another sheet.<o></o>
3. I want to compare array(I,j) to array(k,l), and input a set of values
next to array(K,L) using vlookup below array(i,j)
4. I’ve tried to use looping to run through both sets of arrays

However, Ive encountered some errors. Here's my code:

Sub ManagementFees()

Dim i As Integer
Dim j As Variant
Dim k As Integer
Dim l As Integer

i = 1
j = 20
k = 10
l = 1


ReDim arr(i, j) As Integer
arr = Range("m24:AG24").Value


ReDim arr1(k, l) As Integer
arr1 = Sheets("Management fees").Select.Range("d3:d13").Value


For Each j In arr
If j.Value > arr1(k, l).Value Then arr(i, j) = VLookup(arr1(k, l).Value, Range("D3:f13"), 3)

k = k + 1
j = j + 1

End If
Next j

End Sub


Thank you.

 

[mirabeau]

Hi,

I am new to vba here. WIll really appreciate some help with my code.

Here's what I'm trying to do:

1. I created array(i,j) to input a certain set of values2. I created another array(k,l) to input another set of values belonging to
another sheet.<o></o>
3. I want to compare array(I,j) to array(k,l), and input a set of values
next to array(K,L) using vlookup below array(i,j)
4. I’ve tried to use looping to run through both sets of arrays

However, Ive encountered some errors. Here's my code:

Sub ManagementFees()

Dim i As Integer
Dim j As Variant
Dim k As Integer
Dim l As Integer

i = 1
j = 20
k = 10
l = 1


ReDim arr(i, j) As Integer
arr = Range("m24:AG24").Value


ReDim arr1(k, l) As Integer
arr1 = Sheets("Management fees").Select.Range("d3:d13").Value


For Each j In arr
If j.Value > arr1(k, l).Value Then arr(i, j) = VLookup(arr1(k, l).Value, Range("D3:f13"), 3)

k = k + 1
j = j + 1

End If
Next j

End Sub


Thank you.

Could you post some sample data showing just what you want to achieve? Trying to guess what you want to do on the basis of a code that doesn't work doesn't seem the best way to proceed.

You mention "some errors". Could you be specific about the errors you encounter?

 

[clarence teo]

Hi, thanks for the reply.

Here is what I am trying to achieve:

1. Let's say I have data from cells A1:E1. I am trying to input data into cells A2:E2
2. I need to reference the data in A1:E1 to cells C1:C5 on another sheet. The values in C1:C5 have been arranged in ascending order
3. I need to compare the value in A1 with C1, if the value in A1 is above the value of C1 and below C2, it will return the value in E1 through vlookup and input that to A2. If not, it will continue to loop through C1:C5
4. When comparison of A1 is done, it will move on to cells A2 to A5

Hope this is clear, I am trying to create a code for this. Thanks.

 

[clarence teo]

and btw, the error that I encountered was invalid qualifier.