Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with an iif() function in a formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with an iif() function in a formula
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with an iif() function in a formula
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(D20.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. On Fri, 25 Jul 2003 19:36:38 -0700, "Paul James" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with an iif() function in a formula
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. On Fri, 25 Jul 2003 22:20:20 -0500, Myrna Larson wrote: 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(D20.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. On Fri, 25 Jul 2003 19:36:38 -0700, "Paul James" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with an iif() function in a formula
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with an iif() function in a formula
You're welcome. Post back if there is something you don't understand and/or need more help.
On Sat, 26 Jul 2003 15:45:01 GMT, "Paul James" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula / Function Problem | Excel Discussion (Misc queries) | |||
Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
Function problem | Excel Discussion (Misc queries) | |||
Problem with IF function.... | Excel Worksheet Functions | |||
Problem with IF function | Excel Discussion (Misc queries) |