PDA

View Full Version : loop, if and empty



majed
12-16-2010, 01:38 AM
Yi; I am new in VBA, I have a range of 8 Rows by 8 Columns; r10:y17, I want to search each cell in this range , for example we start in r10
if the value of r10=9 and r11<0; then replace the value of r11 to be 1.1,
if the value of r10=9 and r11= blank and r12<0 then replace the value of r12 by 1.1
if the value of r10=9 and r11= blank and r12= blank and r13<0 then replace the value of r13 by 1.1
if the value of r10=9 and r11= blank and r12= blank and r13= blank and r14<0 then replace the value of r14 by 1.1
if the value of r10=9 and r11,r12,r13,r14=blank and r15<0 then replace the value of r15 by 1.1
if the value of r10=9 and r11,r12,r13,r14,r15=blank and r16<0 then replace the value of r16 by 1.1
if the value of r10=9 and r11,r12,r13,r14,r15,r16=blank and r17<0 then replace the value of r17 by 1.1
then I want to chick for each cell in the range
any help is highly appreciated
this is a part of the the code I use but its log and It dosnt work as expected:

Sub mqhMove()



ActiveCell.Offset(0, 0).Select

If ActiveCell.Offset(0, 0) = 9 And ActiveCell.Offset(1, 0) < 0 And ActiveCell.Offset(1, 0) <> Empty Then 'Down
ActiveCell.Offset(1, 0) = 1.1
ElseIf ActiveCell.Offset(0, 0) = 9 And ActiveCell.Offset(2, 0) < 0 And ActiveCell.Offset(2, 0) <> Empty Then ActiveCell.Offset(2, 0) = 1.1
ElseIf ActiveCell.Offset(0, 0) = 9 And ActiveCell.Offset(3, 0) < 0 And ActiveCell.Offset(3, 0) <> Empty Then ActiveCell.Offset(3, 0) = 1.1
ElseIf ActiveCell.Offset(0, 0) = 9 And ActiveCell.Offset(4, 0) < 0 And ActiveCell.Offset(4, 0) <> Empty Then ActiveCell.Offset(4, 0) = 1.1
ElseIf ActiveCell.Offset(0, 0) = 9 And ActiveCell.Offset(5, 0) < 0 And ActiveCell.Offset(5, 0) <> Empty Then ActiveCell.Offset(5, 0) = 1.1
ElseIf ActiveCell.Offset(0, 0) = 9 And ActiveCell.Offset(6, 0) < 0 And ActiveCell.Offset(6, 0) <> Empty Then ActiveCell.Offset(6, 0) = 1.1
ElseIf ActiveCell.Offset(0, 0) = 9 And ActiveCell.Offset(7, 0) < 0 And ActiveCell.Offset(7, 0) <> Empty Then ActiveCell.Offset(7, 0) = 1.1

End If

Bob Phillips
12-16-2010, 02:13 AM
Sub mqhMove()
Dim cell As Range
Dim nextrow As Long

For Each cell In Range("R10:Y10")

If cell.Value = 9 Then

nextrow = cell.End(xlDown).Row
If nextrow <= 17 Then

If Cells(nextrow, cell.Column).Value < 0 Then

Cells(nextrow, cell.Column).Value = 1.1
End If
End If
End If
Next cell
End Sub

majed
12-16-2010, 02:52 AM
thanksmuch xld for your reply, it works only if the value 9 in row 10, if its in other lines 11-17 it dosent

majed
12-16-2010, 03:05 AM
it works great thanks