Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value, copy, compute, and more
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value, copy, compute, and more
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 "jae ryu" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value, copy, compute, and more
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 "jae ryu" wrote in message om... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value, copy, compute, and more
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 "jae ryu" wrote in message om... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value, copy, compute, and more
...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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value, copy, compute, and more
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 "Mike Fogleman" wrote in message ... 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy contents of Find (Find and Replace) | Excel Discussion (Misc queries) | |||
Find and Copy | Excel Discussion (Misc queries) | |||
when I use Ctrl+D to copy a formula, it does not compute right. | Excel Worksheet Functions | |||
Find and copy?? | Excel Worksheet Functions | |||
help with find and copy | Excel Programming |