Nick,
You have one case:
ColumnJ, Row 1 is Not Null & ColumnK, Row 2 is Not Null
But you have four cases that you may need to address:
ColumnJ, Row 1 is Null & ColumnK, Row 2 is Null
ColumnJ, Row 1 is Null & ColumnK, Row 2 is Not Null
ColumnJ, Row 1 is Not Null & ColumnK, Row 2 is Null
ColumnJ, Row 1 is Not Null & ColumnK, Row 2 is Not Null
Though, given how you are stepping down the column, you really only have two that you MUST address:
ColumnJ, Row 1 is Not Null & ColumnK, Row 2 is Null
ColumnJ, Row 1 is Not Null & ColumnK, Row 2 is Not Null
So, what do you want if ColumnJ, Row 1 is Not Null & ColumnK, Row 2 is Null ?
HTH,
Bernie
MS Excel MVP
>I need to compare cells to the cell above them and to the right of
> them. Based on the comparisons I'll need to update the original cell
[quoted text clipped - 26 lines]
>
> Nick
ArgarLargar@gmail.com - 06 Aug 2007 17:25 GMT
"So, what do you want if ColumnJ, Row 1 is Not Null & ColumnK, Row 2
is Null ?"
That is an acceptable situation so exit the rule and move on to
evaluate ColumnJ, Row2.
Thanks!
Nick
Bernie Deitrick - 06 Aug 2007 18:45 GMT
Nick,
Try this:
Sub TryNow()
Dim i As Integer
Dim myR As Range
Set myR = ActiveSheet.UsedRange
On Error GoTo noBlanks
For i = 10 To 1 Step -1
With Intersect(myR, Cells(1, i).EntireColumn).SpecialCells(xlCellTypeBlanks)
.FormulaR1C1 = "=IF(RC[1]<>"""",R[-1]C,"""")"
.Interior.ColorIndex = 3
End With
Cells(1, i).EntireColumn.Cells.Value = _
Cells(1, i).EntireColumn.Cells.Value
noBlanks:
Next i
End Sub
The cells that are evaluated are changed to red backgrounds....
HTH,
Bernie
MS Excel MVP
> "So, what do you want if ColumnJ, Row 1 is Not Null & ColumnK, Row 2
> is Null ?"
[quoted text clipped - 5 lines]
>
> Nick
ArgarLargar@gmail.com - 06 Aug 2007 21:15 GMT
Hi Bernie,
I really appreciate your help. This is very close.
The fields that are red in background are all "#REF!" so it's not
copying down the right value, is it?
Thanks!
Nick
Bernie Deitrick - 06 Aug 2007 22:03 GMT
Nick,
Select one of the cells, and write an IF function that returns the value
that you want, based on the conditions that you want. When you get it to
work, select the cell with the formula and then record a macro where you
press F2 and then Enter. This will give you code with which you can
replace:
.FormulaR1C1 = "=IF(RC[1]<>"""",R[-1]C,"""")"
with
.FormulaR1C1 = "=NewFormula that worked"
HTH,
Bernie
MS Excel MVP
> Hi Bernie,
>
[quoted text clipped - 6 lines]
>
> Nick
ArgarLargar@gmail.com - 06 Aug 2007 22:07 GMT