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


ADVERTISEMENT

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 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 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 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 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

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 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

Move Down Column And For Every Blank Cell Clear Contents Of Cell To The Left?

Jun 1, 2014

i need a code that moves down a column and for every empty cell in the column the cell to the left is cleared and then it moves on to the next cell down. the column is not always the same and will start from a selected cell, and the column will contain no more than 5 rows

View 3 Replies View Related

Clear Cell Contents If Another Cell Equals String

Sep 23, 2013

I have a 700 line spreadsheet and would like to delete the cell next to another cell if it contains a certain string. I.e if A1 to A3 equals "Test" then i would like to delete the contents of B1 to B3, But if A2 equals "Completed" then only B1 and B3 should be cleared.

View 4 Replies View Related

Input Data Then Clear Cell But Save To Another Cell?

Jan 29, 2014

I want to be able to see the average rating for each employee.. Is there a way to put in the rating from one survey and have it automatically add it another cell and then have it clear the first cell?

Here is an example of what I would like to do: Say we get 2 surveys with one rating of 5 and one rating of 10... If I input the 5 into cell Q4 and have it automatically put into cell R4 and then it gets deleted from Q4 but saved to R4... and then I can add the rating of 10 to Q4 and it automatically ads it to R4 and clears Q4 and totals R4 to an average of 7.5.

This way, with each new survey I can just input each rating to Q4 and have it cleared for the next rating and then have an average rating of all the surveys in R4... we do not need to keep track of how many ratings we are getting, just the average rating.

I have attached the excel sheet if you would like to take a look at what I am trying to do..

View 1 Replies View Related

Excel 2010 :: Clear Cell If Certain Word Does NOT Appear In Another Cell

Jan 31, 2014

I'm having issues with some coding to work in excel 2003, 2007 & 2010 which will ensure that if cell E3 is changed to anything except "pool_car", then cell D12 is changed to 0.00.

So if a user originally selects "pool_car" in E3 and then enters a figure in D12 (which they are allowed to do using data validation), if they then choose to change the car type in E3, that D12 is then cleared of the figure they entered for the "pool_car".

I'm already using a similar code to clear other cells if another changes but am uncertain of who to change this coding or write other coding to suit.

View 5 Replies View Related

If Data Changes In Cell Clear Contents Of Another Cell

Feb 20, 2009

I'm sure this is very easy but I just can't seem to get my head around it

I have data in cells j3 and j4 but when the data changes in j3 I'd like j4 to be cleared of it's contents.

View 12 Replies View Related

Clear Contents Of A Cell Of Row A, If Cell In Row I Is Blank

May 6, 2009

I need to clear the contents of the cell in Row "A", if the cell in Row "I" is blank.

The issue, is there are roughly 1200 excel documents in a folder and subfolders. That is a painful amount of opening workbooks, sort ascending and deleting. Not on my top 10 list of things to do.

If someone could create a code to do this, and loop it through a folder (I can move all the files to a single folder manually, not an issue)

View 6 Replies View Related

Automatically Clear Cell When Corresponding Cell Is Populated

Aug 14, 2009

I have a 7 (columns) by 2 (rows) grid of cells which I need to perform the following,

If cell a1 is populated, then it will clear the contents of a2, or if cell e2 then it will clear the contents of e1. Basically if any cell is populated it will clear the other corresponding column entry.

I came across this code which works well for one cell

...

View 9 Replies View Related

Clear Cell When Corresonding Cell Is Changed

Aug 6, 2008

I know next to nothing about VBA. I have been reading up on it but can't figure out how to do this. A2 has a dropdown list. D2 has options based on the selection in A2. I need to empty the contents of D2 when the selection has changed in A2. I have the corresponding lists linked up. But still need the cell the void the previous info.

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

Clear Value From Cell

May 14, 2006

I have written a vb code to import data from notepad to excel.
I have created a command button to clear those data after excecuting certain function.
For eg. if i have data in column A,B & C of excel i want a code which will clear the value in B & C only starting from a specific row of that column.

View 4 Replies View Related

Clear Cell Value Without Format

Jan 17, 2010

I need help with the vba function that will delete the cell value but leave colors if any ...

View 6 Replies View Related

How To Clear Cell Contents

Jan 17, 2012

I have the following code where I want to clear the cells.

Code:

Workbooks(strOutputFileName).Sheets(2).Activate
Range("F9:F25,C27:E57,F59:F60,J9:J25,G27:I57,J59:J60,N9:N25,K27:M57,N59:N60,R9:R25,O27:Q57,R59:R60").Select
Selection.ClearContents

When I run this code, where it should select G27:I57, it instead selects G27:N57!!!!

When I manually try to select the G27:I57, the selection jumps to the right to N57.

When I use this piece of code on a different worksheet, it works OK!

View 6 Replies View Related

Clear A Formatted Cell

Oct 24, 2006

I have a cell that I've formatted to dd/mmm/yy, It used to work, but now it doesn't. When I type in 1/2/03, the formatted cell shows 0-Jan-00, The formula bar shows =1/1/2. I seem to have a formula in the cell, but I can't get rid of it. I know it's most probably somethinmg I've done , but I don't know what. I've tried everything I know. I can't seem to re format the cell , and I can't delete the formatting in it. I'm stuffed.

View 9 Replies View Related

Clear A Cell's Contents

Mar 7, 2007

Is it possible using vba code to clear a specific cell's contents? I have a workbook that has text boxes that need the linked cell cleared when the workbook opens so the user can start fresh with empty text box's.

View 6 Replies View Related

End Of Month, End Of Year Clear Cell

Feb 14, 2010

I have a worksheet that keeps a year to date and month to date total of overages or underages (end of day cash reconciliation). I need to have a formula that clears the month or year to date total cell at the appropriate time and I want to do this independant of the year as I generate a new sheet every day and enter yesterday's numbers.

I think I have the year figured out by picking the month and day and comparing but the only thing I have come up with for the month is comparing to a list of 13 dates (leap year).

View 2 Replies View Related

Cell Protection & Clear Macro

Oct 14, 2008

when i password protect my sheet it doesnt let my clear cells macro work on the cells i need it to clear why is this and what way is there around this completes my complex sheet.

View 2 Replies View Related







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