Need help
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 |
Need help
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 |
Need help
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 . |
Need help
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 |
Need help
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 . |
Need help
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 . |
Need help
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 . |
Need help
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 . |
Need help
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 . . |
All times are GMT +1. The time now is 03:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com