Ron,

I truly appreciate your reply. However, your formula is way above my
level of understanding.

I would not know where to put the formula and how to execute it.

Is there a bit more information that would be understandable by a
novice? If not, thanks again for your interest.

Vince


--
vinclanz
------------------------------------------------------------------------
vinclanz's Profile: http://www.excelforum.com/member.php...o&userid=14595
View this thread: http://www.excelforum.com/showthread...hreadid=262215

On Sun, 26 Sep 2004 16:47:29 -0500, vinclanz
wrote:


Ron,

I truly appreciate your reply. However, your formula is way above my
level of understanding.

I would not know where to put the formula and how to execute it.

Is there a bit more information that would be understandable by a
novice? If not, thanks again for your interest.

Vince

Sorry, Vince. The Programming NG is usually used for VBA solutions. And your
problem lends itself to a VB solution as you cannot have a formula and a value
in Column A at the same time.

To use the macro that I posted:

<alt<F11 (That means hold down the Alt key and the F11 keys at the same time)
opens the Visual Basic Editor.

Ensure your project is highlighted in the Project Explorer Window.

Then (on the top menu) Insert/Module and paste the code into the window that
opens. I changed the code a bit to put in a test for valid information in the
range to be tested. Note that you should change the range in the Set statement
to reflect the range upon which this macro should operate.

=================
Sub BgtA()
Dim rg As Range
Dim c As Range

Set rg = [B1:B100] 'set this to the appropriate range of B

For Each c In rg
If IsNumeric(c.Value) And IsNumeric(c.Offset(0, -1)) Then
If c.Value c.Offset(0, -1).Value Then
c.Offset(0, -1).Value = c.Value
End If
End If
Next c

End Sub
=====================

Then return to your worksheet window. <alt<F8 opens the Macro dialog box.
You will see BgtA in that box. Select that macro and then select RUN.

Below is a somewhat annotated version of the macro to help you understand what
it is doing.

=========================
Sub BgtA()
Dim rg As Range
Dim c As Range

Set rg = [B1:B100] 'set this to the appropriate range of B

'check every cell in the range "rg"
For Each c In rg

'first see if the value in the cell can be interpreted as a number
If IsNumeric(c.Value) And IsNumeric(c.Offset(0, -1)) Then

'if it is a number, then compare it's value with _
the cell in the same row, one column to the left (Column A)
If c.Value c.Offset(0, -1).Value Then

'if that value is greater, then replace it _
otherwise, do nothing
c.Offset(0, -1).Value = c.Value
End If
End If
Next c

End Sub
===========================

Let me know how it works out.




--ron