Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / August 2007

Tip: Looking for answers? Try searching our database.

Compare cells, update based on Ifs

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
ArgarLargar@gmail.com - 05 Aug 2007 18:32 GMT
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 - 06 Aug 2007 14:42 GMT
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
Great!  Thanks-

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.