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

clarence teo

New Member
Joined
Jul 2, 2012
Messages
16
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:p></o:p>

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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

mirabeau

Banned user
Joined
Nov 4, 2010
Messages
2,075
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:p></o:p>

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

New Member
Joined
Jul 2, 2012
Messages
16
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,745
Messages
5,626,624
Members
416,195
Latest member
tonmcg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top