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