Need help
 

I posted last week in regards to this, however I do not think I was clear
enough in what I am looking for. I am trying to create a macro that looks a
cell and if that cell is has a "x"then I want the macro to clear the fields
to the left of that cell. I have
got it to work on a line by line basis but I wan to be able to set this macro
up to work on any line

If i7 = x Then Worksheets("Adjustment").Range("A7:h7").ClearConte nts

The code in the I column looks like this
=IF((H7=""),"",IF(TODAY()(30+H7),"X",""))

Need help
 

Hi,

Is this what you mean

Sub sonic()
Dim LastRow As Long
Dim MyRange As Range
Set Sht = Sheets("Adjustments")
LastRow = Sht.Cells(Cells.Rows.Count, "I").End(xlUp).Row
Set MyRange = Sht.Range("I1:I" & LastRow)
For Each c In MyRange
If UCase(c.Value) = "X" Then
c.Offset(, -8).Resize(, 7).ClearContents
End If
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"JLeCoure" wrote:

I posted last week in regards to this, however I do not think I was clear
enough in what I am looking for. I am trying to create a macro that looks a
cell and if that cell is has a "x"then I want the macro to clear the fields
to the left of that cell. I have
got it to work on a line by line basis but I wan to be able to set this macro
up to work on any line

If i7 = x Then Worksheets("Adjustment").Range("A7:h7").ClearConte nts

The code in the I column looks like this
=IF((H7=""),"",IF(TODAY()(30+H7),"X",""))

Need help
 

oops,

Note the typo

Set Sht = Sheets("Adjustments")

should be

Set Sht = Sheets("Adjustment")





--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

Is this what you mean

Sub sonic()
Dim LastRow As Long
Dim MyRange As Range
Set Sht = Sheets("Adjustments")
LastRow = Sht.Cells(Cells.Rows.Count, "I").End(xlUp).Row
Set MyRange = Sht.Range("I1:I" & LastRow)
For Each c In MyRange
If UCase(c.Value) = "X" Then
c.Offset(, -8).Resize(, 7).ClearContents
End If
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"JLeCoure" wrote:

I posted last week in regards to this, however I do not think I was clear
enough in what I am looking for. I am trying to create a macro that looks a
cell and if that cell is has a "x"then I want the macro to clear the fields
to the left of that cell. I have
got it to work on a line by line basis but I wan to be able to set this macro
up to work on any line

If i7 = x Then Worksheets("Adjustment").Range("A7:h7").ClearConte nts

The code in the I column looks like this
=IF((H7=""),"",IF(TODAY()(30+H7),"X",""))

Need help
 

Does this do the trick?

Sub clear()
Dim c As Range
Dim lastrow As Long
Dim rng As Range
Set rng = Range(Cells(1, 9), Cells(Rows.Count, 9).End(xlUp))
For Each c In rng
If c = "x" Then
c.Offset(0, -8).Resize(1, 8).ClearContents
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Fri, 19 Feb 2010 14:13:01 -0800, JLeCoure
wrote:

I posted last week in regards to this, however I do not think I was clear
enough in what I am looking for. I am trying to create a macro that looks a
cell and if that cell is has a "x"then I want the macro to clear the fields
to the left of that cell. I have
got it to work on a line by line basis but I wan to be able to set this macro
up to work on any line

If i7 = x Then Worksheets("Adjustment").Range("A7:h7").ClearConte nts

The code in the I column looks like this
=IF((H7=""),"",IF(TODAY()(30+H7),"X",""))