Clear Cell Based On Criteria

Apr 18, 2005

I'm trying to write a macro that clears the cell in the range V10:X14 if the value of that cell equals 99.

Sub MyDeleteCell()
For i = 10 To 14 Step 1
For j = 22 To 25 Step 1
If Cells(i, j) = 99 Then Cells(i, j).Clear
Next i
Next j
End Sub

It's giving me a "Invalid Next Control Variable reference" I've tried defining i and j as Integers but that didn't work.

View 3 Replies


ADVERTISEMENT

Automatically Clear Range Based On Cell Criteria

Jan 9, 2007

I have the following Worksheet Change Event in my worksheet. The contents of range rng are not cleared.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

rng = Range("F" & Target.Row & ":M" & Target.Row)

If Not Intersect(Target, Range("N5:N1000")) Is Nothing Then

If Target.Cells.Count = 1 Then

If Target.Value = "Yes" Then

Application.EnableEvents = False

rng.ClearContents

Application.EnableEvents = True

End If

End If

End If
End Sub

View 9 Replies View Related

Clear Contents Based On Criteria

Aug 28, 2008

I need some code that will clear the contents of a Row Range(C:T) if either of the contents of cells X and Y = "E"

The code needs to look at cells X17 and Y17 and continue looking until it gets to X216 and Y216

So - If X = E but Y does not, then the row range C:T must be cleared.
If Y = E but X does not, then the row range C:T must be cleared
If both X and Y = E, then the row range C:T must be cleared

If the letter E does not exist in either X or Y, then the row must be left alone

View 13 Replies View Related

VBA Clear Cell Contents If Criteria Met?

Feb 7, 2013

I have a spreadsheet with data to row 5000. I have column Y that has an "X" in it. I would like some VBA code to look at each row up to 5000, in column Y for the "X". If it is there, clear the cell contents on the current row in columns T, U, and V.

I've tried modifying some existing code (excluding the Y column range of 5000) but keep getting a "Compile error: Wrong number of arguments or invalid property assignments". How do I set the 5000 limit and get this code back on track?

Sub RemoveBankDelay()
n = WorksheetFunction.CountIf(Range("Y:Y"), "X")
For i = 1 To n

[Code]....

View 9 Replies View Related

If Cell String Meets Criteria Then Clear Contents From Array On Same Row

Jul 17, 2013

I'm attempting to clear the contents from a range of cells on rows where a cell string may equal R, X, XX, Y, Z, ZX, #N/A.
The macro runs fine until it gets to a cell that contains #N/A. How to get this to work?

Sub Recalculate()
Dim r As Integer
r = ActiveSheet.UsedRange.Rows.Count

Application.ScreenUpdating = False

For Row = 13 To r

[Code] ........

View 2 Replies View Related

Clear Cell When Another Cell Is Certain Criteria

Jul 19, 2014

I have some code in my worksheet that works fine but i cant figure out how to clear a cell when another cell changes, Basically if BC1 = Yes i want to clear cell W1?

View 6 Replies View Related

Macro That Will Clear Contents Of Cell Based On Format Of Text In Adjacent Cell

Feb 18, 2009

Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.

View 2 Replies View Related

VBA To Clear Contents / Change Value Of A Cell Based On If Another Cell Has A Value And Continue L To R

Mar 9, 2013

I am relatively new to VBA. I am creating an attendance calendar that tracks employees calling sick, late etc.. It is a point based system. What I am looking for is, a way to clear the point value that was manually entered in a specific cell (I3), if there is a Value manually entered in (CU3). Each column in my worksheet is for a specific date ie; I3 is the cell where I enter the points (1.00) for that employee by calling in sick on 3/1/2013, (Column "I" is for 3/1/2013). After 90 days, this point accumulated by the employee does not count against them, so I need that point entered in (I3) to either = 0 or the cell contents to be cleared if there is a value entered in cell (CU3) which is 91 days after, so my (A3) cell does not add that point acquired on 3/1/2013.

I need this to run in a range (I3:I450) so if any value is entered into (CU3:CU450) it has the same result and continue to for (J3:J450) so if any value is entered into (CV3:CV450) and so on..

View 9 Replies View Related

Clear Contents Of Cell Based On Comparsion Of Cell Values

Mar 7, 2007

I am a financial administrator & every month I have down load 4 bank accts as CSV, import into xl & code (CACode) the amounts for our accountant. I have VBA that formats, adds headings & formula etc but I have a problem/s. In H col I place CAcode & I use a sumif formula in I col to sum all the amounts with that have the same CAcode. For simplicity sake I copy the sumif down & then sort H col ascendindingly (this is done by VBA). Now I am trying write a macro to clear the contents of the cell in I col

if eg h60 = h59
then I60 clearContents, Select h59
Else select H59
Do until H3 is selected

Psuedocode
Select table (A2:I Xldown)
Sort Table by CAcode (H Col)
Set Range as H3:Xldown
Select Last cell with CAcode (Xldown) in H col
For every cell in Range (H3:Xldown)
Use If/thenIf Last cell = 2nd Last cell (H Col) then
Clear contents of I col (last row)
select 2nd Last cell (H Col)
Else 2nd Last cell (H Col)Next Cell

I have to use Xldown to select range as the range will be variable each month & for each bank acct. I need to clear contents of cell to verify that all the sub-totals of unique CAcodes of the CAcoded amounts = the totals Because I may have up to 120 rows X 3 bank accts I am slowly using vba to do my work.

Sub sort_And_delete_Sumif_amounts()
Dim r As Range
'Select range to sort
Range("A2:I98").Select
'Sort CAcode in H col ascendingly
Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'select range for comparisomn of CAcode
Set r = Range("H3", Selection.End(xlDown))................

View 4 Replies View Related

Clear Cell Contents Based On Defined Name

Jan 13, 2010

I'm trying to clear cell contents based on a defined name given to a set of various cells in my worksheet. The cells are not continuous, but since they're given that defined name, I don't think it matters.

The defined name is listed as "CommentsFields".
The worksheet name is listed as "QPRForm_V6"

I found a similar thread, but cannot get it to work with what I'm looking for. I'm relatively new to writing VBA/macros, so I'm not sure what to do.

View 6 Replies View Related

Clear Contents Based On A Selection In Another Cell.

Feb 21, 2007

If I have a pick list, or drop-down list, in cell B21 and one of the options in that list is "Clear", how can I get the contents of cell C21 to be cleared when I select the "Clear" option in B21?

Constraint... Don't want to use VBA.

I am thinking of hiding a formula somewhere other than in C21 that evaluates B21 and does an If Then kind of deal to clear C21.

View 9 Replies View Related

Clear Cell Content Based On Another Cell Calculation

Dec 21, 2012

I have a cell A1 in sheet2 linked cell A1 in sheet1 (simply A1='sheet1'!A1). A1 in sheet1 is a data validation drop down menu.

I want to clear the content of A2 in sheet 2 everytime the content of A1 in sheet2 changes/is updated. That is everytime the value of A1 in sheet1 is changed using the drop down menu.

I tried using a Worksheet_Change event macro (which I do not fully understand) but it won't work with a cell that updates from a calculation. It also doesn't work if triggered from a cell from another worksheet (I tried linking it to cell A1 on sheet1 in this case).

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Range("A2").ClearContents
End Sub

Any simple solution to clear the content of cell A2 in sheet2 when A1 in sheet2 updates?

View 3 Replies View Related

VBA To Clear And Lock / Unlock Cell Based On Another Cell

Jan 14, 2013

I'm trying to write a vba code that does the following....

There is a question in column A to which the user chooses yes or no from column B. Based on the response in column B, I'd like the same row column D to be formatted so that if the anser is yes, the cell is white, and unlocked. If the response is no, the cell is cleared, locked and the greyed out. (e.g. if B4 is "Yes", the format in D4 will change) So far I've come up with the following which formats the colour:

Dim response As Range
For Each response In Range("$C$10:$C$73")
If response.Value = "Yes" Then

response.Select
ActiveCell.Offset(0, 2).Range("A1").Select
With Selection.Interior

[Code] .......

How to add in a .clearcontents function, so that the contents are cleared if the response is not "yes", and also what I would need to add to unlock the cell in column D?

View 1 Replies View Related

Clear Cells In A Range Based On Other Cell Values

Jun 6, 2012

I have two rows of numbers, say D1 to F1 and D2 to F2. Row D2 to F2 will always have lower values.

The range below it is D3 to F100. I want the range to clear the contents of the cells in the range where:

a) the cell is > the x1 cell above it

OR b) the cell is < the x2 cell above it

If it is = or between the two cells, the value stays.

Example:

If D1 thru F1 is 4, 5, 7 and D2 thru F2 is 2, 4, 3, then:
D3 is 5, it is cleared
E3 is 4, it stays
F3 is 5, it stays
D4 is 2, it stays
E4 is 1, it is cleared
F4 is 9, it is cleared
Etc

Basically, the cell in the range looks up its own column, compares itself to x1 and x2 in that column. If it's greater than or less than, then it clears the contents, if not, the value in the cell stays.

View 3 Replies View Related

Clear Cells Of Values Based On Cell Selection

Jun 13, 2008

A project I am working on calls for me to implement a macro that will clear the contents of a cell (but not affect the validation list it contains) based on the selection of another cell or cells. So on the simplified attached example, I need to achieve the following;

On the fist row of options, if cell "C3" is selected, then the contents in cell "D3" are cleared. If cell "B3" is selected then the contents in cells "C3" AND "D3" are both cleared. Similar is needed for the second row of options;

If Cell "C6" is selected, then the contents in cell "D6" are cleared. If cell "B6" is selected then the contents in cells "C6" AND "D6" are both cleared. Finally, if cell "A3" is selected, then ALL of the above cells' (B3-D3 & B6-D6) contents must be cleared.

View 7 Replies View Related

Hide Column And Clear Contents Based On A Cell Value In Another Sheet

Mar 19, 2009

I have a workbook with two sheets of data. I want to hide column B of Sheet2 and clear contents of range B2:B50 if the value in A1 of Sheet1 is "a".

View 2 Replies View Related

Hide Unwanted Columns & Clear Ranges Based On Cell Value

Sep 17, 2008

I have written a VBA procedure which is supposed to hide unneeded columns (based on the a number entered in a certain cell. This is working so far. Since I need to sum only the visible cells in a row i need to clear the contents of a range of cells if they will be hidden. Unfortunately the code i have written runs only to the ClearContents and then starts over from the beginning. If i take out the .clearcontents and put .Select for example instead, the code runs perfectly.
Here is the

Sub worksheet_calculate() 'Hide columns that are not needed

Sheet1. Unprotect

Application. ScreenUpdating = False

Dim i As Integer
Dim r As Integer
Dim s As Integer
Dim rngRange As Range

i = 2

View 8 Replies View Related

Add Cell Value Or Clear Based On Another Cell

Jan 11, 2008

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("AQ19")) Is Nothing Then
Application.EnableEvents = False
If Target.Value = "" Then
Range("AW38").Value = "X"
Else
Range("AW38").Value = ""
End If
End If
Application.EnableEvents = True
End Sub

Cell AQ19 will either be blank or an have an O in it.
If it has an O in it then then AW38 will be blank but allow for a manual user input. If AQ19 is blank then AW38 will simply have an X in it. It simply is not working as planned. I have posted this question in this forum as well: http://excelforum.com/showthread.php?t=623871

View 5 Replies View Related

Clear Data If Criteria Not Met?

Aug 4, 2012

I have a workbook that has lots of data that I dont need. In column B if the data dont start with the letter M/T/W/F/S I need the contents clearing.

View 1 Replies View Related

Clear All Criteria In All Autofilters On A Page

May 22, 2009

I have searched these boards (and found wonderful ideas), but can't find a better solution.

Is there a quick way to clear all the autofilters on a page (or all of them in 1 row)?

I am using this code, but it is very slow.

For i = 1 To 21
Selection.autofilter Field:=i
Next i

View 9 Replies View Related

Clear Cells Meeting Criteria

Nov 1, 2006

I would like to seek help on how to delete a cell that has data in it and that has colourfill. E.g. Cell A1 shows "Occupied" and Cell A1 has a blue colour fill.

Need help on how to delete that cell's data and remove the blue colour without having to manually do so.

View 4 Replies View Related

Clear Rows Meeting Multiple Criteria

Dec 13, 2006

In my code I am searching a spreadsheet for certain states and deleting rows that have states I want to exclude. Is there a way to do this with a list of states, instead of having to make many for loops?

For i = lastrow To 2 Step -1
Cells(i, 4).Select
If Cells(i, 4).Value = "PA" Then
Rows(i).Select
Selection.ClearContents
End If
On Error Resume Next
Next
For i = lastrow To 2 Step -1
Cells(i, 4).Select
If Cells(i, 4).Value = "TX" Then
Rows(i).Select
Selection.ClearContents
End If
On Error Resume Next
Next

View 5 Replies View Related

Clear/Delete Cells Meeting A Date Criteria

Oct 1, 2006

i created a work sheet with the help of the wonderful people on this forum. my boss loved it! one problem they now want it to automaticly take out a date and time of the cells when the date rolls around again.

example:

12/25/05 late 00:20:06 when 12/25/06 rolls around they want the system to automaticly see it and delete it out with the amount of time they were late or sick ect.... or if the sup was out that day and comes in the next day the system will see that the date has past and will up date the info. the other catch is i need it to move everything up one space when it deletes somthing. i want it to read j18 and k18 as one and so through j and k 40 same for l and m n and o ect.... i attached the sheet

View 9 Replies View Related

Copy Cell Value To Cell On Other Sheet Based On Two Criteria

May 8, 2014

I used this vba code before:

[Code] ......

And it worked perfectly, but now i wanted to use it in an other file but just change the sheets and I keep getting an Error 13.

It should copy the data from sheet insertmeasurements c23 till end of data in the column next to it (is dynamic) and based on cell B1 and the matching category code in column B, put that data in the matching cell in column C to the matching cell (based on the criteria in column B and C) on the sheet storedata.

Attached File : Profitibility Database v1.4.xlsm‎

View 1 Replies View Related

Copy Cell To New Location Based On Criteria In Another Cell?

Jun 25, 2014

I've sent out a survey asking people to sign up for one of two groups, A or B. I have a table with a long string of responses, with the name in column A, and in columns B or C there is an X that shows whether they have chosen Group A (column B) or Group B (column C)

I am trying to write a bit of code where it would paste the names into a new table with the headers Group A and Group B. Here is what I have pieced together so far:

Sub Create_Groups()
For i = 2 To Range("B2").End(xlDown).Column()
If Range("B" & i).Value = x Then

[Code].....

View 5 Replies View Related

SUM Based On A Criteria And Another Cell Being Blank

Apr 23, 2009

I need to create a formula that counts up the number of times a specific word is put into a cell lets say 'Carpet' only when another cell is left blank.

Count the number of times between C1:10 the word 'Carpet' is input only when D1:10 is left BLANK?

If looked into DCOUNTA formulas and SUMPRODUCT formulas but seem to be clutching at straws.

My problem seems to be at referring the formula to the BLANK cell?

View 5 Replies View Related

If Statement Based On Various Cell Criteria

Aug 11, 2009

G5 - can contain True or false
G6 - contains text but the if is on the basis of this cell being "Hand Delivered" or not

I have tried the following to illustrate what I after:

View 2 Replies View Related

Populate Cell Based On Two Criteria?

Aug 2, 2013

I have a list of teams, and beside them I need a calculation to be filled.

The criteria is based on a cell reference (a month which can be changed from a drop-down in BM2) and also the name of the team.

dummy 16.xls

View 5 Replies View Related

Penalty Based On Criteria In Another Cell?

Sep 28, 2013

In my country, students are penalized when they fail Mathematics subject. That is when a student gets "F" in Maths. The penalty is only for those scoring Division I and II. The divisions change into Division III for those penalized but points remain the same. For example if a student ie Juma gets Division I of points 17 and he fails Maths, he should be penalized and get Division III of points 17. NB: Those who score Division III,IV and 0 are not penalized even though they fail maths.

View 5 Replies View Related

Add Cell Note Based On Criteria

May 28, 2013

I have a range of cells containing text values in BS15:DS50000.

What I want to do is look at each cell in the range BS15:DS50000 and if the cell contains the "" character, then populate text from column BH (if any) as a cell note.

For example, if cell DB42 contains the "" character somewhere within its text, then populate the text in BH42 as a cell note within DB42.

If BH42 is blank, then no cell note. (Also, if DB42 does NOT contain a "", then no cell note either.)

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved