Compare cells, update based on Ifs

[ArgarLargar]

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
with one of those adjoining cell values. After I finish with one
column then I need to repeat the procedure on the column to the LEFT
of the original column.

I know IF, THEN and ELSE statesments but I don't know VB for Microsoft
Office products.

Range could be all 65,000+ rows on a workseet


Start on ColumnJ, Row2



If ColumnJ, Row2 is Null _
If ColumnK, Row 2 is Not Null _
If ColumnJ, Row 1 is Not Null _
ColumnJ, Row2 Value is updated to match ColumnJ, Row1
End If
End If
End If

Loop Back and compare the next Row on the original column.
When all Rows are checked then start over at ColumnI, Row2

Even a macro doing this one column at a time would be incredibly
helpful.

Thanks!

Nick

 

[Bernie Deitrick]

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

 

[ArgarLargar]

"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]

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

 

[ArgarLargar]

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]

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

 

[ArgarLargar]

Great! Thanks-