Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for zero value within a selected range
Hello,
I am new to this post, so please bear with me. I am trying to search for a value of zero in a row ABOVE the actual row I will be adding a formula. For example, I have a row that has some numeric information in certain columns, other columns have a zero. I would like for my formula to be placed in only the cells below the ones with a zero. I would like this to be done for a range in the worksheet where the amount of rows can be different. For Intx = 1 To lngRow Cells(Intx, 2).Select If InStr(1, ActiveCell.Value, "appw1d") 0 Then If ActiveCell.Offset(0, -1).Value = " Target Renewal" Then Range(Cells(Intx, 6), Cells(Intx, 20)).Select ' this finds the rows to put the formula Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 14)).Select 'this selects the row above If Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 14)).Value = 0 Then 'this gives me a type mismatch error Range(Cells(Intx, 6), Cells(Intx, 20)).Select Selection.Formula = "=VLOOKUP(""appw1d"",$f$1:$g$16,2,FALSE)* $b$2" 'Loop End If End If End If Next Is there a better way to do this? I appreciate any help. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for zero value within a selected range
A single cell can have a value. A range with multiple cells won't. And you're
checking a few cells. If Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 14)).Value = 0 I'm not sure if this works--I got a circular reference with the formula: Option Explicit Sub testme01() Dim iRow As Long Dim iCol As Long Dim lngRow As Long lngRow = 10 ' With ActiveSheet 'start with row 2 so there is a row above. For iRow = 2 To lngRow If InStr(1, .Cells(iRow, 2).Value, "appw1d", vbTextCompare) Then If LCase(.Cells(iRow, 1).Value) _ = LCase(" Target Renewal") Then For iCol = 6 To 20 If .Cells(iRow - 1, iCol).Value = 0 _ And IsEmpty(.Cells(iRow - 1, iCol)) = False Then .Cells(iRow, iCol).Formula _ = "=VLOOKUP(""appw1d"",$f$1:$g$16,2,FALSE)* $b$2" End If Next iCol End If End If Next iRow End With End Sub And an empty cell would be a zero in VBA's eyes. TR wrote: Hello, I am new to this post, so please bear with me. I am trying to search for a value of zero in a row ABOVE the actual row I will be adding a formula. For example, I have a row that has some numeric information in certain columns, other columns have a zero. I would like for my formula to be placed in only the cells below the ones with a zero. I would like this to be done for a range in the worksheet where the amount of rows can be different. For Intx = 1 To lngRow Cells(Intx, 2).Select If InStr(1, ActiveCell.Value, "appw1d") 0 Then If ActiveCell.Offset(0, -1).Value = " Target Renewal" Then Range(Cells(Intx, 6), Cells(Intx, 20)).Select ' this finds the rows to put the formula Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 14)).Select 'this selects the row above If Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 14)).Value = 0 Then 'this gives me a type mismatch error Range(Cells(Intx, 6), Cells(Intx, 20)).Select Selection.Formula = "=VLOOKUP(""appw1d"",$f$1:$g$16,2,FALSE)* $b$2" 'Loop End If End If End If Next Is there a better way to do this? I appreciate any help. Thanks! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for zero value within a selected range
Thanks so much! This worked perfectly.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SEARCHING RANGE | Excel Discussion (Misc queries) | |||
searching a text file and importing selected data | Excel Discussion (Misc queries) | |||
searching a range for a sum... | Excel Worksheet Functions | |||
3d range searching | Excel Discussion (Misc queries) | |||
searching a range | Excel Programming |