select corresponding value from named range

[kornbrot]

hello everyone,

i have got two named ranges (1 collumn, 3 to 10 rows) of the same size.

i have written a macro that goes throug a third (larger) range and writes in to a neighbouring cell everytime it encounters a value contained rangeOne of the smaller ranges.

the problem i've got is selecting the corresponding value in rangeTwo to write it in the correct cell.

example:


rangeOne_____rangeTwo

1____________200

7____________150

59___________300

62___________50


long range

1

2

3

4

5

6

7

.

.

.

59

60

61

62


an when i'm done i wont the macro to create something like this:


1_____200

2

3

4

5

6

7_____150

.

.

.

59____300

60

61

62____50


the code i've git so far writes "yes!" into the right collumn (instead of the 200,150,300,50).

how can i select the corresponding value to write into the cells?


code so far:


For i = 0 To h_top Step h_top / height_divisions

ActiveCell.Offset(1, 0).Activate

ActiveCell = i

For Each j In Range("rangeOne")

If j.Value = i Then

ActiveCell.Offset(0, 1) = "yes!"

End If

Next j

Next i


regards,

kornbrot

 

[kornbrot]

worked around it by replacing "yes!" with j.offset(0,1)

not nice, but will do for now

 

[Luke M]

Does this help? It'll take awhile to run, but gets the job done.

[pre]

Sub MacroLookup()
Dim SearchValue
Dim FoundValue
Dim i As Integer

Application.ScreenUpdating = False
For Each c In Range("f4:F10") 'Whatever the big range is
FoundValue = ""
SearchValue = c.Value
i = 0
For Each x In Range("Range1")
i = i + 1
If x.Value = SearchValue Then
FoundValue = Range("Range2").Cells(i, 1).Value
Exit For
End If
Next x
If FoundValue <> "" Then
c.Offset(0, 1).Value = FoundValue
End If
Next c
Application.ScreenUpdating = True
End Sub

[/pre]