Formula Array?

[Gabe]

How would I put this formula in VBA?

=IF(D2>3101,"3", IF(D2>2451,"2", IF(D2>0,"1", "N/A")))

So that the data would come out like this:

D E
3102 3
2452 2
1000 1

There's numbers in Column D but blank cells in Column E (where the value
would go), how will it know when to stop if there are no numbers in column D?

Any help would very much appricaited.
Thanks,
~Gabe

 

[Per Jessen]

Hi Gabe

It can be done like this:

Sub test()
Dim FirstRow As Long
Dim LastRow As Long
FirstRow = 2 ' Headings in row 1
LastRow = Range("D" & FirstRow).End(xlDown)
For Each cell In Range("D" & FirstRow, Range("D" & LastRow)).Cells
If cell.Value > 3101 Then
cell.Offset(0, 1) = 3
ElseIf cell.Value > 2451 Then
cell.Offset(0, 1) = 2
ElseIf cell.Value > 0 Then
cell.Offset(0, 1) = 1
Else
cell.Offset(0, 1) = "N/A"
End If
Next
End Sub

Regards,
Per

 

[Per Jessen]

You can also just insert the formula by VBA and then copy down the
formula as required:

Sub test1()
Dim FirstRow As Long
Dim LastRow As Long
Dim DblQuote
DblQuote = """"

FirstRow = 2 ' Headings in row 1
LastRow = Range("D" & FirstRow).End(xlDown)
MyFormula = "=IF(rc[-1]>3101," & DblQuote & "3" & DblQuote _
& ", IF(rc[-1]>2451," & DblQuote & "2" & DblQuote & _
",IF(rc[-1]>0," & DblQuote & "1" & DblQuote & "," & _
DblQuote & "N/A" & DblQuote & ")))"
Range("E" & FirstRow).FormulaR1C1 = MyFormula
Range("E" & FirstRow, Range("E" & LastRow)).FillDown
End Sub

Regards,
Per

 

[Jacob Skaria]

Try the below macro which works on the active sheet.

Sub EnterFormula()
Range("E2:E" & Cells(Rows.Count, "D").End(xlUp).Row).Formula = _
"=IF(D2>3101,3,IF(D2>2451,2,IF(D2>0,1,""N/A"")))"
End Sub

If this post helps click Yes

 

[Gabe]

That got me on the right track! Thank you so much!

~Gabe

 

[Bernd P]

Hello,

Another approach:

Function mylookup(d As Double) As Double
Select Case d
Case Is > 3101#
mylookup = 3#
Case Is > 2451#
mylookup = 2#
Case Is > 0#
mylookup = 1#
Case Else
mylookup = CVErr(xlErrValue)
End Select
End Function

Regards,
Bernd