Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to vba if possible please
Hello from Steved
Is it possible please to run the below in VBA The formula is in E6 to E255 =IF(ISNUMBER(MATCH(D6,$S$2:$S$2000,0)),VLOOKUP(D6, $S$2:$W$2000,2,0)&CHAR(10)&VLOOKUP(D6,$S$2:$W$2000 ,3,0),"") Thankyou. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to vba if possible please
Steve,
I turned on the macro recorder and entered your formula into a cell. A little tweaking of the resulting code led to this: Range("E6:E255").FormulaR1C1 = _ "=IF(ISNUMBER(MATCH(RC[-1],R2C19:R2000C19,0)),VLOOKUP(RC[-1],R2C19:R2000C23,2,0)&CHAR(10)&VLOOKUP(RC[-1],R2C19:R2000C23,3,0),"""")" Or did you actually want to do the calcs in VBA? If so where do you want the results? hth, Doug "Steved" wrote in message ... Hello from Steved Is it possible please to run the below in VBA The formula is in E6 to E255 =IF(ISNUMBER(MATCH(D6,$S$2:$S$2000,0)),VLOOKUP(D6, $S$2:$W$2000,2,0)&CHAR(10)&VLOOKUP(D6,$S$2:$W$2000 ,3,0),"") Thankyou. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to vba if possible please
Hi,
Are you trying to put this formula in E6 to E255 with updating D6? If so, '------------------------------ Dim Rg as Range Set Rg=Range("E6:E255") Rg.Formula= "=IF(ISNUMBER(MATCH(D6,$S$2:$S$2000,0)),VLOOKUP(D6 ,$S$2:$W$2000,2,0)&CHAR(10)&VLOOKUP(D6,$S$2:$W$200 0,3,0),"""")" '----------------------- When applied to a multi-cell range, Excel sets the formula for the first cell of the range and automatically update the formula for the other cells as if you were doing a copy/paste of the formula. -- Regards, Sébastien <http://www.ondemandanalysis.com "Steved" wrote: Hello from Steved Is it possible please to run the below in VBA The formula is in E6 to E255 =IF(ISNUMBER(MATCH(D6,$S$2:$S$2000,0)),VLOOKUP(D6, $S$2:$W$2000,2,0)&CHAR(10)&VLOOKUP(D6,$S$2:$W$2000 ,3,0),"") Thankyou. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to vba if possible please
Dim res as Variant
Dim rng as Range Dim cell as Range set rng = Range("S2:S2000") for each cell in Range("E6:E255") res = Application.Match(Cell.offset(0,-1),rng,0) if not iserror(res) then cell.Value = rng(res).offset(0,1) & chr(10) & _ rng(res).offset(0,2) end if Next -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved Is it possible please to run the below in VBA The formula is in E6 to E255 =IF(ISNUMBER(MATCH(D6,$S$2:$S$2000,0)),VLOOKUP(D6, $S$2:$W$2000,2,0)&CHAR(10) &VLOOKUP(D6,$S$2:$W$2000,3,0),"") Thankyou. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to vba if possible please
Hello Doug From Steved
Doug it looks up a list of names. ie if I type 10921 in D6 the formula looks up the name and puts it in E6 Thankyou "Doug Glancy" wrote: Steve, I turned on the macro recorder and entered your formula into a cell. A little tweaking of the resulting code led to this: Range("E6:E255").FormulaR1C1 = _ "=IF(ISNUMBER(MATCH(RC[-1],R2C19:R2000C19,0)),VLOOKUP(RC[-1],R2C19:R2000C23,2,0)&CHAR(10)&VLOOKUP(RC[-1],R2C19:R2000C23,3,0),"""")" Or did you actually want to do the calcs in VBA? If so where do you want the results? hth, Doug "Steved" wrote in message ... Hello from Steved Is it possible please to run the below in VBA The formula is in E6 to E255 =IF(ISNUMBER(MATCH(D6,$S$2:$S$2000,0)),VLOOKUP(D6, $S$2:$W$2000,2,0)&CHAR(10)&VLOOKUP(D6,$S$2:$W$2000 ,3,0),"") Thankyou. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to vba if possible please
Hello Tom From Steved
Tom I put the below in and works but if I type in the second value in Col D it freezes the spreadsheet any idea's please Private Sub Worksheet_Change(ByVal Target As Range) Dim res as Variant Dim rng as Range Dim cell as Range set rng = Range("S2:S2000") for each cell in Range("E6:E255") res = Application.Match(Cell.offset(0,-1),rng,0) if not iserror(res) then cell.Value = rng(res).offset(0,1) & chr(10) & _ rng(res).offset(0,2) end if Next End Sub "Tom Ogilvy" wrote: Dim res as Variant Dim rng as Range Dim cell as Range set rng = Range("S2:S2000") for each cell in Range("E6:E255") res = Application.Match(Cell.offset(0,-1),rng,0) if not iserror(res) then cell.Value = rng(res).offset(0,1) & chr(10) & _ rng(res).offset(0,2) end if Next -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved Is it possible please to run the below in VBA The formula is in E6 to E255 =IF(ISNUMBER(MATCH(D6,$S$2:$S$2000,0)),VLOOKUP(D6, $S$2:$W$2000,2,0)&CHAR(10) &VLOOKUP(D6,$S$2:$W$2000,3,0),"") Thankyou. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to vba if possible please
If you are going to have it in a change event - it needs to only process the
changed cell. See revised: Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant Dim rng As Range Dim cell As Range On Error GoTo ErrHandler Set rng = Range("S2:S2000") If Target.Count 1 Then Exit Sub 'for each cell in Range("E6:E255") If Not Intersect(Target, Range("D6:D255")) Is Nothing Then res = Application.Match(Target, rng, 0) If Not IsError(res) Then Application.EnableEvents = False Cells(Target.Row, 5).Value = rng(res).Offset(0, 1) & Chr(10) & _ rng(res).Offset(0, 2) Application.EnableEvents = True End If End If ErrHandler: Application.EnableEvents = True 'Next End Sub Worked for me. -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello Tom From Steved Tom I put the below in and works but if I type in the second value in Col D it freezes the spreadsheet any idea's please Private Sub Worksheet_Change(ByVal Target As Range) Dim res as Variant Dim rng as Range Dim cell as Range set rng = Range("S2:S2000") for each cell in Range("E6:E255") res = Application.Match(Cell.offset(0,-1),rng,0) if not iserror(res) then cell.Value = rng(res).offset(0,1) & chr(10) & _ rng(res).offset(0,2) end if Next End Sub "Tom Ogilvy" wrote: Dim res as Variant Dim rng as Range Dim cell as Range set rng = Range("S2:S2000") for each cell in Range("E6:E255") res = Application.Match(Cell.offset(0,-1),rng,0) if not iserror(res) then cell.Value = rng(res).offset(0,1) & chr(10) & _ rng(res).offset(0,2) end if Next -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved Is it possible please to run the below in VBA The formula is in E6 to E255 =IF(ISNUMBER(MATCH(D6,$S$2:$S$2000,0)),VLOOKUP(D6, $S$2:$W$2000,2,0)&CHAR(10) &VLOOKUP(D6,$S$2:$W$2000,3,0),"") Thankyou. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to vba if possible please
Hello Tom From Steved
Thankyou. it is excellent. "Tom Ogilvy" wrote: If you are going to have it in a change event - it needs to only process the changed cell. See revised: Private Sub Worksheet_Change(ByVal Target As Range) Dim res As Variant Dim rng As Range Dim cell As Range On Error GoTo ErrHandler Set rng = Range("S2:S2000") If Target.Count 1 Then Exit Sub 'for each cell in Range("E6:E255") If Not Intersect(Target, Range("D6:D255")) Is Nothing Then res = Application.Match(Target, rng, 0) If Not IsError(res) Then Application.EnableEvents = False Cells(Target.Row, 5).Value = rng(res).Offset(0, 1) & Chr(10) & _ rng(res).Offset(0, 2) Application.EnableEvents = True End If End If ErrHandler: Application.EnableEvents = True 'Next End Sub Worked for me. -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello Tom From Steved Tom I put the below in and works but if I type in the second value in Col D it freezes the spreadsheet any idea's please Private Sub Worksheet_Change(ByVal Target As Range) Dim res as Variant Dim rng as Range Dim cell as Range set rng = Range("S2:S2000") for each cell in Range("E6:E255") res = Application.Match(Cell.offset(0,-1),rng,0) if not iserror(res) then cell.Value = rng(res).offset(0,1) & chr(10) & _ rng(res).offset(0,2) end if Next End Sub "Tom Ogilvy" wrote: Dim res as Variant Dim rng as Range Dim cell as Range set rng = Range("S2:S2000") for each cell in Range("E6:E255") res = Application.Match(Cell.offset(0,-1),rng,0) if not iserror(res) then cell.Value = rng(res).offset(0,1) & chr(10) & _ rng(res).offset(0,2) end if Next -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved Is it possible please to run the below in VBA The formula is in E6 to E255 =IF(ISNUMBER(MATCH(D6,$S$2:$S$2000,0)),VLOOKUP(D6, $S$2:$W$2000,2,0)&CHAR(10) &VLOOKUP(D6,$S$2:$W$2000,3,0),"") Thankyou. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |