Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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","")) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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","")) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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","")) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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","")) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|