Formula to vba if possible please

[Steved]

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.

 

[Doug Glancy]

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

 

[sebastienm]

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$2000,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.

 

[Tom Ogilvy]

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

 

[Steved]

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

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

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.

 

[Steved]

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]

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("D6255")) 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.

 

[Steved]

Hello Tom From Steved
Thankyou. it is excellent.

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("D6255")) 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.