I am trying to create a macro that looks a cell and if that cell is "true"
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

Here is the code I have trying to get variables to work in it

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

This should do it:

Sub stantiate()
Dim lr As Long, sh As Worksheet, rng As Range
Set sh = Sheets("Adjustment")
lr = sh.Cells(Rows.Count, 9).End(xlUp).Row
Set rng = sh.Range("I7:I" & lr)
For Each c In rng
If c = x Then
sh.Range("A" & c.Row & ":H" & c.Row).ClearContents
End If
Next
End Sub




"JLeCoure" wrote in message
...
I am trying to create a macro that looks a cell and if that cell is "true"
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

Here is the code I have trying to get variables to work in it

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

Thanks for the Help

I am getting the following error when I run this

Runtime err 13 Type mismatch see below where the error happens

"JLGWhiz" wrote:

This should do it:

Sub stantiate()
Dim lr As Long, sh As Worksheet, rng As Range
Set sh = Sheets("Adjustment")
lr = sh.Cells(Rows.Count, 9).End(xlUp).Row
Set rng = sh.Range("I7:I" & lr)
For Each c In rng
If c = x Then <--------Here
sh.Range("A" & c.Row & ":H" & c.Row).ClearContents
End If
Next
End Sub




"JLeCoure" wrote in message
...
I am trying to create a macro that looks a cell and if that cell is "true"
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

Here is the code I have trying to get variables to work in it

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



.

On Feb 12, 6:17*pm, JLeCoure
wrote:
I am trying to create a macro that looks a cell and if that cell is "true"
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

Here is the code I have trying to get variables to work in it

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


Sub test()
For x = 2 To Range("I65536").End(xlUp).Row
If Range("I" & x).Value = 7 Then
myrange = "A" & x & ":H" & x
Range(myrange).ClearContents
End If
Next
End Sub

however if you have a lot of rows, consider (based on
http://www.ozgrid.com/VBA/find-method.htm)
Sub test2()
Dim lCount As Long
Dim rFoundCell As Range
Set rFoundCell = Range("I1")
For lCount = 1 To WorksheetFunction.CountIf(Columns("I:I"), 7)
Set rFoundCell = Columns("I:I").Find(What:=7,
After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
Range(Cells(rFoundCell.Row, 1), Cells(rFoundCell.Row,
8)).ClearContents
Next lCount
End Sub

I was not sure what x was. Try this:

Sub stantiate()
Dim lr As Long, sh As Worksheet, rng As Range
Set sh = Sheets("Adjustment")
lr = sh.Cells(Rows.Count, 9).End(xlUp).Row
Set rng = sh.Range("I7:I" & lr)
For Each c In rng
If c.value = x Then
sh.Range("A" & c.Row & ":H" & c.Row).ClearContents
End If
Next
End Sub


This modification assumes that the x variable is a value and not a range
object.







"JLeCoure" wrote in message
...
Thanks for the Help

I am getting the following error when I run this

Runtime err 13 Type mismatch see below where the error happens

"JLGWhiz" wrote:

This should do it:

Sub stantiate()
Dim lr As Long, sh As Worksheet, rng As Range
Set sh = Sheets("Adjustment")
lr = sh.Cells(Rows.Count, 9).End(xlUp).Row
Set rng = sh.Range("I7:I" & lr)
For Each c In rng
If c = x Then <--------Here
sh.Range("A" & c.Row & ":H" & c.Row).ClearContents
End If
Next
End Sub




"JLeCoure" wrote in message
...
I am trying to create a macro that looks a cell and if that cell is
"true"
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

Here is the code I have trying to get variables to work in it

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



.

I don't see where x have been defined.

You have to define the variable x or double-quote it if you really meant "x"


Gord Dibben MS Excel MVP

On Fri, 12 Feb 2010 16:44:01 -0800, JLeCoure
wrote:

Thanks for the Help

I am getting the following error when I run this

Runtime err 13 Type mismatch see below where the error happens

"JLGWhiz" wrote:

This should do it:

Sub stantiate()
Dim lr As Long, sh As Worksheet, rng As Range
Set sh = Sheets("Adjustment")
lr = sh.Cells(Rows.Count, 9).End(xlUp).Row
Set rng = sh.Range("I7:I" & lr)
For Each c In rng
If c = x Then <--------Here
sh.Range("A" & c.Row & ":H" & c.Row).ClearContents
End If
Next
End Sub




"JLeCoure" wrote in message
...
I am trying to create a macro that looks a cell and if that cell is "true"
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

Here is the code I have trying to get variables to work in it

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



.

Hi Gord, I assomed that since the OP said his illustration code worked that
x was either a object variable or a data variable. But without a
declaration, one never knows.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
I don't see where x have been defined.

You have to define the variable x or double-quote it if you really meant
"x"


Gord Dibben MS Excel MVP

On Fri, 12 Feb 2010 16:44:01 -0800, JLeCoure
wrote:

Thanks for the Help

I am getting the following error when I run this

Runtime err 13 Type mismatch see below where the error happens

"JLGWhiz" wrote:

This should do it:

Sub stantiate()
Dim lr As Long, sh As Worksheet, rng As Range
Set sh = Sheets("Adjustment")
lr = sh.Cells(Rows.Count, 9).End(xlUp).Row
Set rng = sh.Range("I7:I" & lr)
For Each c In rng
If c = x Then <--------Here
sh.Range("A" & c.Row & ":H" & c.Row).ClearContents
End If
Next
End Sub




"JLeCoure" wrote in message
...
I am trying to create a macro that looks a cell and if that cell is
"true"
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

Here is the code I have trying to get variables to work in it

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



.

I'm sure x is a variable as you say.

Gord

On Fri, 12 Feb 2010 22:00:05 -0500, "JLGWhiz" wrote:

Hi Gord, I assomed that since the OP said his illustration code worked that
x was either a object variable or a data variable. But without a
declaration, one never knows.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
I don't see where x have been defined.

You have to define the variable x or double-quote it if you really meant
"x"


Gord Dibben MS Excel MVP

On Fri, 12 Feb 2010 16:44:01 -0800, JLeCoure
wrote:

Thanks for the Help

I am getting the following error when I run this

Runtime err 13 Type mismatch see below where the error happens

"JLGWhiz" wrote:

This should do it:

Sub stantiate()
Dim lr As Long, sh As Worksheet, rng As Range
Set sh = Sheets("Adjustment")
lr = sh.Cells(Rows.Count, 9).End(xlUp).Row
Set rng = sh.Range("I7:I" & lr)
For Each c In rng
If c = x Then <--------Here
sh.Range("A" & c.Row & ":H" & c.Row).ClearContents
End If
Next
End Sub




"JLeCoure" wrote in message
...
I am trying to create a macro that looks a cell and if that cell is
"true"
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

Here is the code I have trying to get variables to work in it

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



.

Hi Thanks again.

I have not had a chance to get back to this project till today. Still
getting an mismatch error.

I have a function in I column
=IF((H7=""),"",IF(TODAY()(30+H7),"X","")) that marks an X in the I column
if it is past 30 days in column H. If this is true I want the macro to
delete Columns A-h on that row

"JLGWhiz" wrote:

I was not sure what x was. Try this:

Sub stantiate()
Dim lr As Long, sh As Worksheet, rng As Range
Set sh = Sheets("Adjustment")
lr = sh.Cells(Rows.Count, 9).End(xlUp).Row
Set rng = sh.Range("I7:I" & lr)
For Each c In rng
If c.value = x Then
sh.Range("A" & c.Row & ":H" & c.Row).ClearContents
End If
Next
End Sub


This modification assumes that the x variable is a value and not a range
object.







"JLeCoure" wrote in message
...
Thanks for the Help

I am getting the following error when I run this

Runtime err 13 Type mismatch see below where the error happens

"JLGWhiz" wrote:

This should do it:

Sub stantiate()
Dim lr As Long, sh As Worksheet, rng As Range
Set sh = Sheets("Adjustment")
lr = sh.Cells(Rows.Count, 9).End(xlUp).Row
Set rng = sh.Range("I7:I" & lr)
For Each c In rng
If c = x Then <--------Here
sh.Range("A" & c.Row & ":H" & c.Row).ClearContents
End If
Next
End Sub




"JLeCoure" wrote in message
...
I am trying to create a macro that looks a cell and if that cell is
"true"
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

Here is the code I have trying to get variables to work in it

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



.



.