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!

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

Searching for zero value within a selected range
 

Thanks so much! This worked perfectly.