Find value, copy, compute, and more

[jae ryu]

Hello
I need to find a value and then copy adjacent cell to another cell
then compute simple calculation.
since I am a begginer, I don't have any clue where I have to start
with.
please, help me out.

The idea is when I select on a cell (Column E) it find same value in
Index row (Column A), then
copy the value of adjacent cell (Column B). I'll like from there to
look up two values (Col_C and col_D) to compute which
cells are related, then put the result to last column.

If so, here's what I would really like to do:
Col_A Col_B Col_C Col_D Col_E
1 1980 0.15 -0.35 9
2 1981 -0.14 -0.35 9
3 1982 -0.57 0.05 1
4 1983 -0.57 -0.49 4
5 1984 -1 -0.32 16
6 1985 -0.57 -0.43 13
7 1986 -0.77 -0.07 2
8 1987 0.9 -0.33 16
9 1988 -0.27 -0.45 6
10 1989 1.27 0.07 1
11 1990 0.67 0.08 1
12 1991 -0.145 0.05 1
13 1992 -0.41 -0.45 6
14 1993 -0.77 -0.10 9
15 1994 -0.41 -0.36
16 1995 -0.27 0.05 1
17 1996 -0.15 -0.36 9
18 1997 -0.27 -0.02 2
19 1998 0.23 0.42 21
20 1999 -1.3 -0.78 14
21 2000 0.23 -0.21 18

Say col_E1=9, then when the macro starts, search for 9 in Col_A (say
it's found in A9),copy
the value (1988) of adjacent cell (say it's found in B9), then compare
adjacent values
(adjacent value of col_B9 (1988), -0.27 and adjacent value of
col_E1(9), -0.35).
If Col_C > Col_D, then compute Val=Col_c/Col_D, else compute
Val=Col_D/Col_C.
Finally, I put the result in Col_F with format "Col_B*Val", so the
result value of Col_F1..ColF3 corresponding to Col_E1 ..Col_E3 should
be followed.
Col_F
1988*0.77 (here, 0.77=-0.27/-0.35)
1988*0.77 (here, 0.77=-0.27/-0.35)
1980*3.0 (here, 3=0.15/0.05)
....

thank you

 

[Guest]

Sub Macro1()

Dim a As Variant
Dim b As String

Dim c As Variant
Dim d As Variant
Dim e As Variant

Range("E1").Select
'set the ending number on the for statement to last row number
For I = 1 To 20
If ActiveCell.Value = Empty Then
ActiveCell.Offset(1, 0).Activate
End If

a = ActiveCell.Value
b = ActiveCell.Offset(0, 1).Address
Range("A1:A21").Select
Selection.Find(What:=a, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
c = ActiveCell.Offset(0, 1).Value
d = ActiveCell.Offset(0, 2).Value
e = ActiveCell.Offset(0, 3).Value
Range(b).Select
ActiveCell = c
ActiveCell.Offset(0, 1).Activate
If d > e Then
ActiveCell = d / e
Else
ActiveCell = e / d
End If
ActiveCell.Offset(1, -2).Activate
Next I
End Sub

 

[P Daulton]

Sub Something()
For a = 1 To 21
If Cells(a, "A") = Selection.Value Then
myyear = Cells(a, "B")
adjyear = Cells(a, "C")
adjsel = Selection.Offset(0, -1)
If adjyear > adjsel Then myvalue = adjyear / adjsel Else myvalue =
adjsel / adjyear
Selection.Offset(0, 1) = myyear & "*" & Format(myvalue, "0.00")
Exit For
End If
Next a
End Sub

Works on the selected cell. I used adjyear for value ADJacent to the YEAR,
adjsel for the value ADJacent to the SELection.

Below is a similar one eliminating the loop:

Sub Something2()
On Error GoTo mistake
a = WorksheetFunction.Match(Selection, Range("A1:A21"), 0)
myyear = Cells(a, "B")
adjyear = Cells(a, "C")
adjsel = Selection.Offset(0, -1)
If adjyear > adjsel Then myvalue = adjyear / adjsel Else myvalue = adjsel /
adjyear
Selection.Offset(0, 1) = myyear & "*" & Format(myvalue, "0.00")
mistake:
End Sub

Pascal

 

[Mike Fogleman]

How about a formula to put in F1 and copy it down?

=VLOOKUP(E19,$A$1:$C$21,2)*(MAX(D19,VLOOKUP(E19,$A$1:$C$21,3))/MIN(D19,VLOOK
UP(E19,$A$1:$C$21,3)))

Mike F

 

[P Daulton]

...and a small adaptation to do the whole column, in the following code is
the same macro as the previous post (the second macro), but commented out
lines added. Remove the initial single quote marks to enable them:

Sub Something2()
'For myrow = 1 To 21
'Cells(myrow, "E").Select
On Error GoTo mistake
a = WorksheetFunction.Match(Selection, Range("A1:A21"), 0)
myyear = Cells(a, "B")
adjyear = Cells(a, "C")
adjsel = Selection.Offset(0, -1)
If adjyear > adjsel Then myvalue = adjyear / adjsel Else myvalue = adjsel /
adjyear
Selection.Offset(0, 1) = myyear & "*" & Format(myvalue, "0.00")
mistake:
'Next myrow
End Sub

Pascal

 

[P Daulton]

Taking your idea, I think jae ryu was looking more for a string of the form
"1988*0.77", rather than actually multiplying year values (if that's what
they are?):
=VLOOKUP(E1,$A$1:$C$21,2) & "*" &
ROUND(MAX(D1,VLOOKUP(E1,$A$1:$C$21,3))/MIN(D1,VLOOKUP(E1,$A$1:$C$21,3)),2)
placed in F1 and copied down (note yours should be put in F19 and copied up
and down!)
Pascal

How about a formula to put in F1 and copy it down?

=VLOOKUP(E19,$A$1:$C$21,2)*(MAX(D19,VLOOKUP(E19,$A$1:$C$21,3))/MIN(D19,VLOOK
UP(E19,$A$1:$C$21,3)))

Mike F [snip]

be followed.
Col_F
1988*0.77 (here, 0.77=-0.27/-0.35)
1988*0.77 (here, 0.77=-0.27/-0.35)
1980*3.0 (here, 3=0.15/0.05)