problem with an iif() function in a formula

[Paul James]

I'm trying to use VBA to put a formula into a range of cells, but something
is not working right.

Here's what I'm trying to accomplish:

I have a list of numbers in column D, beginning in row 2 and ending in row
numRecords, where numRecords is module level variable that contains the row
number of the bottom number in that list in column D. I'm trying to insert
text values to the right of each of those numbers in column E such that:

1. If the number to the immediate left in column D is greater than 0.1,
insert the text string "REG".
2. If the number to the immediate left in column D is less than or equal to
0.1, leave the cell blank.

I've tried using the following code to do this:

Sub enterTP1 numRecords = Range("A1").CurrentRegion.Rows.Count
ActiveSheet.Range(Cells(2, 5), Cells(numRecords, 5)) =
IIf(Range("A1").Cells(2, 4) > 0.1, "REG", "")
End Sub

This code almost works, because it inserts the text string "REG" in the
correct range of cells, so there are no problems with my range or cell
specifications or the value or scope of the variable numRows. The problem
is with the evaluation of the iif() function, because my code inserts the
"REG" string in ALL the cells in the range, even where the corresponding
values in column D are less than 0.1. So in effect, the iif() function is
not being evaluated in my code.

Can anyone tell me how to modify my code so it will product the desired
results?

Thanks in advance.

Paul

 

[Paul James]

I just figured out where my problem is, but I still don't know what to do
about it.

I showed the code in my previous post:

Sub enterTP1
numRecords = Range("A1").CurrentRegion.Rows.Count
ActiveSheet.Range(Cells(2, 5), Cells(numRecords, 5)) =
IIf(Range("A1").Cells(2, 4) > 0.1, "REG", "")
End Sub

The problem is with the criteria in the iif function:

iif(Range("A1").Cells(2, 4) > 0.1 . . .

I realize now that this expression only checks the value of the first cell
in in row 2 of colum D, but I need it to check the value of the cells in the
other rows in column D as it inserts the values in each corresponding row in
column E.

How can I modify my code so it will accomplish this?

Thanks in advance.

Paul

 

[Myrna Larson]

Here are a couple of ways to do this.

The first is to put a formula in the cells that checks the cell in the same row, column D,
calculate those formulas, then replace them with their values.

Sub EnterTP1()
With Range("E2").Resize(Range("A1").CurrentRegion.Rows.Count - 1, 1)
.Formula = "=IF(D2>0.1,""REG"","""")"
.Calculate
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End Sub

The second is to retrieve the values from column D into an array, check them one at a time and
put the right result into a second array, then dump that second array into column E.

Sub EnterTP1()
Dim CheckCells As Variant
Dim N As Long
Dim R As Long
Dim Rng As Range
Dim Results As Variant

Set Rng = Range("A1").CurrentRegion
CheckCells = Rng.Columns(4).Value 'put values from column D into an array
N = UBound(CheckCells, 1)
ReDim Results(2 To N, 1 To 1)

For R = 2 To N
If CheckCells(R, 1) > 0.1 Then Results(R, 1) = "REG"
Next R

Rng.Cells(2, 5).Resize(N - 1, 1).Value = Results
End Sub

I don't know which of these is faster, but for a relatively small number of rows it shouldn't
matter much.

 

[Myrna Larson]

Interestingly enough (at least to me), the 2nd routine is faster by a factor of about 1.6 when
the range is 1000 rows, 2.6 times faster with 18 rows.

 

[Paul James]

Myrna - thanks so much for the two solutions to my problem. I'm learning a
lot from looking at the code you wrote - the use of the Resize property, the
use of double quotes and the array. Thanks for taking the time to provide
such helpful information.

 

[Myrna Larson]

You're welcome. Post back if there is something you don't understand and/or need more help.