Column values

[marc747]

Hi,
I have a worksheet that very 3 columns are grouped, they are not
merged but every 3 columns are related. example it starts from column
(D,E,F) (G,H,I) (J,K,L) and so on. I am trying to make a macro that
will look into the first column example (D9) and see;
if (D9) is = to the number in (F9) then Delete the Values in (E9) and
(F9)
if (D9) is < to the number in (F9) then enter "of" on (E9)
and if (D9) is (empty, no value) then Delete the Values in (E9) and
(F9)
and then move to the next row in that columns all the way from
(D971)
after it is done then next group (G,H,I) (G9)

the Sheet is (Active Sheet)
the Cells are from D9:IL71

I would appreciate if anyone can help me with this.
Thank You

 

[JR Form]

Marc,
Are there any empty (no data) cloumns between the sets of three?

 

[marc747]

Hi,
No empty cloumns between the sets of three.

Thanks.

 

[JR Form]

marc,

Try this code:
first copy the worksheet and try it out on the copy.
'**********************************************
Sub DoMyRowsColumns()
Dim i, k As Integer
Range("d9").Select
Application.ScreenUpdating = False
k = 0
Do Until k = 80
i = 0

Do Until i = 62

If ActiveCell.Value = ActiveCell.Offset(0, 2).Value Then
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents
Else
If ActiveCell.Value < ActiveCell.Offset(0, 2).Value Then
ActiveCell.Offset(0, 1) = "OF"
Else
ActiveCell.Offset(0, 0).ClearContents
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents
End If
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop

ActiveCell.Offset(-i, 3).Select '-i sends it back to the top of the next
column

k = k + 1

Loop
Application.ScreenUpdating = True
End Sub

'************************************8

 

[marc747]

Hi, thanks for your help, it works but we need to refine it a little.

I belive this is the part that enters the "OF" if so then I need to
add a line.

If ActiveCell.Value < ActiveCell.Offset(0, 2).Value Then
ActiveCell.Offset(0, 1) = "of"

I am trying to add a two requirement in order to enter an "OF"
[if (D9) is < to the number in (F9)] and [(D9) is >=1] if both
requirements are ok! then enter "of" on (E9) ]

And at no point (D9) gets cleared.
And if (D9) has no value in it then (E9 and F9) should be cleared

Thanks you so much....

 

[marc747]

Hi, thanks for your help, it works but we need to refine it a little.

I belive below is the part that enters the "OF" if so then I need to
add a line.

If ActiveCell.Value < ActiveCell.Offset(0, 2).Value Then
ActiveCell.Offset(0, 1) = "of"

I am trying to add a two requirement in order to enter an "OF"
[if (D9) is < to the number in (F9)] and [(D9) is >=1] if both
requirements are ok! then enter "OF" in (E9) ]

And at no point (D9) gets cleared.
And if (D9) has no value in it then (E9 and F9) should be cleared

Also is it possible to enter a short text next to the code so that I
can understand which code does what.

Thanks you so much....

 

[JR Form]

marc,

Here it again with the changes

'**********************************
Sub DoMyRowsColumns()
Dim i, k As Integer
Range("d9").Select
Application.ScreenUpdating = False
k = 0
Do Until k = 80
i = 0

Do Until i = 62

If ActiveCell.Value = ActiveCell.Offset(0, 2).Value Then
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents
Else
If ((ActiveCell.Value < ActiveCell.Offset(0, 2).Value) And _
(ActiveCell.Value <= 1)) Then 'new requirement
ActiveCell.Offset(0, 1) = "OF"
Else
ActiveCell.Offset(0, 0).ClearContents
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents
End If
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop

ActiveCell.Offset(-i, 3).Select '-i sends it back to the top of the next
column

k = k + 1

Loop
Application.ScreenUpdating = True
End Sub

'**********************************

Hi, thanks for your help, it works but we need to refine it a little.

I belive below is the part that enters the "OF" if so then I need to
add a line.

If ActiveCell.Value < ActiveCell.Offset(0, 2).Value Then
ActiveCell.Offset(0, 1) = "of"

I am trying to add a two requirement in order to enter an "OF"
[if (D9) is < to the number in (F9)] and [(D9) is >=1] if both
requirements are ok! then enter "OF" in (E9) ]

And at no point (D9) gets cleared.
And if (D9) has no value in it then (E9 and F9) should be cleared

Also is it possible to enter a short text next to the code so that I
can understand which code does what.

Thanks you so much....

marc,

Try this code:
first copy the worksheet and try it out on the copy.
'**********************************************
Sub DoMyRowsColumns()
Dim i, k As Integer
Range("d9").Select
Application.ScreenUpdating = False
k = 0
Do Until k = 80
i = 0

Do Until i = 62

If ActiveCell.Value = ActiveCell.Offset(0, 2).Value Then
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents
Else
If ActiveCell.Value < ActiveCell.Offset(0, 2).Value Then
ActiveCell.Offset(0, 1) = "OF"
Else
ActiveCell.Offset(0, 0).ClearContents
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents
End If
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop

ActiveCell.Offset(-i, 3).Select '-i sends it back to the top of the next
column

k = k + 1

Loop
Application.ScreenUpdating = True
End Sub

'************************************8










- Show quoted text -

 

[marc747]

Thanks, it worked great.
I have one more question, for this Macro we have a set of 3 Columns
grouped, how would I change the Macro to make it a set of 4 Columns
grouped
I appreciate your help..... thanks!

marc,

Here it again with the changes

'**********************************
Sub DoMyRowsColumns()
Dim i, k As Integer
Range("d9").Select
Application.ScreenUpdating = False
k = 0
  Do Until k = 80
     i = 0

    Do Until i = 62

      If ActiveCell.Value = ActiveCell.Offset(0, 2).Value Then
        ActiveCell.Offset(0, 1).ClearContents
        ActiveCell.Offset(0, 2).ClearContents
      Else
          If ((ActiveCell.Value < ActiveCell.Offset(0, 2).Value) And _
              (ActiveCell.Value <= 1)) Then  'new requirement
            ActiveCell.Offset(0, 1) = "OF"
          Else
            ActiveCell.Offset(0, 0).ClearContents
            ActiveCell.Offset(0, 1).ClearContents
            ActiveCell.Offset(0, 2).ClearContents
          End If
      End If
      ActiveCell.Offset(1, 0).Select
      i = i + 1
    Loop

  ActiveCell.Offset(-i, 3).Select  '-i sends it back to the top of the next
column

  k = k + 1

Loop
Application.ScreenUpdating = True
End Sub

'**********************************

Hi, thanks for your help, it works but we need to refine it a little.

I belive below is the part that enters the "OF" if so then I need to
add a line.

            If ActiveCell.Value < ActiveCell.Offset(0, 2).Value Then
                     ActiveCell.Offset(0, 1) = "of"

I am trying to add a two requirement in order to enter an "OF"
 [if (D9) is < to the number in (F9)]  and  [(D9) is >=1] if both
requirements are ok! then enter "OF" in (E9) ]

And at no point (D9) gets cleared.
And if (D9) has no value in it then (E9 and F9) should be cleared

Also is it possible to enter a short text next to the code so that I
can understand which code does what.

Thanks you so much....

- Show quoted text -

 

[JR Form]

marc,

Are you doing an evaluation on the 4th column? You would need to add some
code to make it do the evaluation and clear contents if that is what you
wanted. Also change the offset to 4 from 3 so it moves 4 columns at a time.


Here it again with the changes

'**********************************
Sub DoMyRowsColumns()
Dim i, k As Integer
Range("d9").Select
Application.ScreenUpdating = False
k = 0
Do Until k = 80
i = 0

Do Until i = 62

If ActiveCell.Value = ActiveCell.Offset(0, 2).Value Then
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents
ActiveCell.Offset(0, 3).ClearContents '4th column

Else
If ((ActiveCell.Value < ActiveCell.Offset(0, 2).Value) And _
(ActiveCell.Value <= 1)) Then 'new requirement
ActiveCell.Offset(0, 1) = "OF"
Else
ActiveCell.Offset(0, 0).ClearContents
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents
ActiveCell.Offset(0, 3).ClearContents '4th column

End If
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop
'below changed for 4th column request
ActiveCell.Offset(-i, 4).Select '-i sends it back to the top of the next
column

k = k + 1

Loop
Application.ScreenUpdating = True
End Sub

'**********************************

Thanks, it worked great.
I have one more question, for this Macro we have a set of 3 Columns
grouped, how would I change the Macro to make it a set of 4 Columns
grouped
I appreciate your help..... thanks!

marc,

Here it again with the changes

'**********************************
Sub DoMyRowsColumns()
Dim i, k As Integer
Range("d9").Select
Application.ScreenUpdating = False
k = 0
Do Until k = 80
i = 0

Do Until i = 62

If ActiveCell.Value = ActiveCell.Offset(0, 2).Value Then
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents
Else
If ((ActiveCell.Value < ActiveCell.Offset(0, 2).Value) And _
(ActiveCell.Value <= 1)) Then 'new requirement
ActiveCell.Offset(0, 1) = "OF"
Else
ActiveCell.Offset(0, 0).ClearContents
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents
End If
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop

ActiveCell.Offset(-i, 3).Select '-i sends it back to the top of the next
column

k = k + 1

Loop
Application.ScreenUpdating = True
End Sub

'**********************************

Hi, thanks for your help, it works but we need to refine it a little.

I belive below is the part that enters the "OF" if so then I need to
add a line.

If ActiveCell.Value < ActiveCell.Offset(0, 2).Value Then
ActiveCell.Offset(0, 1) = "of"

I am trying to add a two requirement in order to enter an "OF"
[if (D9) is < to the number in (F9)] and [(D9) is >=1] if both
requirements are ok! then enter "OF" in (E9) ]

And at no point (D9) gets cleared.
And if (D9) has no value in it then (E9 and F9) should be cleared

Also is it possible to enter a short text next to the code so that I
can understand which code does what.

Thanks you so much....

marc,

Try this code:
first copy the worksheet and try it out on the copy.
'**********************************************
Sub DoMyRowsColumns()
Dim i, k As Integer
Range("d9").Select
Application.ScreenUpdating = False
k = 0
Do Until k = 80
i = 0

Do Until i = 62

If ActiveCell.Value = ActiveCell.Offset(0, 2).Value Then
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents
Else
If ActiveCell.Value < ActiveCell.Offset(0, 2).Value Then
ActiveCell.Offset(0, 1) = "OF"
Else
ActiveCell.Offset(0, 0).ClearContents
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents
End If
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop

ActiveCell.Offset(-i, 3).Select '-i sends it back to the top of the next
column

k = k + 1

Loop
Application.ScreenUpdating = True
End Sub

:
Hi,
No empty cloumns between the sets of three.

Marc,
Are there any empty (no data) cloumns between the sets of three?

"(e-mail address removed)" wrote:

Hi,
I have a worksheet that very 3 columns are grouped, they are not
merged but every 3 columns are related. example it starts from column
(D,E,F) (G,H,I) (J,K,L) and so on. I am trying to make a macro that
will look into the first column example (D9) and see;
if (D9) is = to the number in (F9) then Delete the Values in (E9) and
(F9)
if (D9) is < to the number in (F9) then enter "of" on (E9)
and if (D9) is (empty, no value) then Delete the Values in (E9) and
(F9)
and then move to the next row in that columns all the way from
(D971)
after it is done then next group (G,H,I) (G9)

the Sheet is (Active Sheet)
the Cells are from D9:IL71

I would appreciate if anyone can help me with this.
Thank You- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -