Need help

  • Thread starter Thread starter JLeCoure
  • Start date Start date
J

JLeCoure

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").ClearContents
 
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
 
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
 
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").ClearContents
Click to expand...


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.
 
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
 
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.
 
I'm sure x is a variable as you say.

Gord

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.
Click to expand...
 
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
 
Back
Top