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
ADVERTISEMENT
Feb 19, 2010
My Excel Vba shown below inserts time() into a cell on my sheet. Is it possible to clear this cell if time() is already into the cell. My VBA is onky entering the time() once and not insering the current time() if a change cell values after some time i.e.
View 3 Replies
View Related
Jan 4, 2007
With code I enter text by double clicking in any cell in a range.(column A,B,C are excleded)
What I need is to clear the content of the cell adjacent to the left of the one I choose to dbl click AND the one below that.
Example: I dbl click in E1 and the content of cells D1 and D2 is cleared.
View 9 Replies
View Related
Jan 8, 2007
I am quite a novice with excel and I am trying to get a cell to show blank when I select a drop down list value in another cell.
Cell D2 contains a validated list containing two items (Air, Vacuum).
If I select Vacuum I wish to change another cells (D4) contents to blank, D4 is also a validated list containing three items (Yes, No, BLANK) the blank is actually a blank space and not the text blank.
I can get the cell to operate correctly but if I select Air in Cell D2 and then Yes in cell D4, but then change my mind and reset to Vacuum in cell D2 i need the cell D4 to clear its contents automatically.
View 9 Replies
View Related
Jul 24, 2009
I wrote some code that toggles bewtweeen the word "Yes" and the function clearcontents.
What it does not do, is if you click on cell A1 and change it's contents and you click on the same cell again it does nothing. You need to click on another cell say A2 before you can go and change A1 again.
My code is as follows:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("Documents")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
If IsEmpty(Target) Then
Target.Value = "Yes"
Else
Target.ClearContents
End If
Application.EnableEvents = True
On Error Goto 0
End If
End Sub
View 3 Replies
View Related
Apr 16, 2009
i have a database of the kind:
Model Version Color Store
1 a zz HH
2 b xx QQ
3 c yy OO
4 d ww PP
I need to obtain in the y axis the model, and in the x axis the store.
The data per each model and store should be: the version and color.
I'm using Pivot table to do that, but i'm only able to obtain the "count" (or sum or other formula) but I'm not able to obtain the content of the cell.
Example:
Store HH PP
Model
1 Version a d
Color zz ww
2 Version b c
Color xx yy
View 9 Replies
View Related
Apr 18, 2014
Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.
Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"
I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).
View 4 Replies
View Related
May 25, 2014
I have a column with various values in, string and numeric. The strings I want to keep have "," in and letters one side and "space" on the other. All the cells I want cleared do not have ","
The rest of what I'm using works fine accept this line which forces an error.
[Code]....
View 5 Replies
View Related
Jul 1, 2014
I am working on developing QR Codes using some MATLAB code and it would be really convenient if I could create an excel program which changed the background color of a cell containing a 1 to black and a cell containing a 0 to white.
View 2 Replies
View Related
Jul 7, 2014
I am currently trying to create a spreadsheet whereby if I enter certain text in a cell in Column A on worksheet 1 that correlates with text in a cell in Column A on Worksheet 2, then the description in Column B in Worksheet 2 is entered into Column B on worksheet 1.
For example, if worksheet 2 has the following:
Column A Column B
XXXX PRODUCT 1
YYYY PRODUCT 2
and I enter XXXX in column A on worksheet 1, I want Column B on worksheet 1 to automatically enter PRODUCT 1.
View 5 Replies
View Related
Mar 25, 2014
multiple search match and replace content in a different column so for example
new workbook (look up table)
sku
search1
search2
[Code]....
so something like where you compare two tables and find and replace based on another cell that matches in my sku.. more details would be if the table column aren't exactly matching but the column header and the row header would match and fill or replace in the correct/corresponding cell is there a macro or vba to do this job in excel?
View 6 Replies
View Related
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
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
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
Mar 22, 2014
I have a sheet that I fill out with customer data then print and start over with the next customer. This requires me to tab and delete through the sheet before starting the next entry and I am wondering if there is some way to auto clear the unlocked cells based on a single entry IE when we entered new data in the 1st field this would clear the unlocked cells and make them ready for new data?
View 14 Replies
View Related
Feb 12, 2010
I have a chart full of different names which I need to separate them by color when a specific entry is found in a Cell. For example the list has the store name "ZELLERS" + its branch number. (ex. ZELLERS #276 PL or ZELLERS #295 SL and so on). I would like to modify the below code so that It only check for the name ZELLERS and not the branch number. If it finds ZELLERS in any cell In the Range of C1:C500 then color it Red.
Below Code does that but I have to write the exact name as it appears in the cell otherwise it wont find it for me. Is there any way to resolve the issue? I am not an Excel Programmer but Only an Excel user.
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("C1:C500")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
On Error GoTo Endit
Application.EnableEvents = False
vals = Array("ZELLERS #276 PL", "ZELLERS #295 SL", "Gopher", "Hyena", "Ibex", "Lynx")
nums = Array(3, 46, 6, 3, 7, 4, 20, 10, 23, 15)
For Each rr In r..........................
View 9 Replies
View Related
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
Sep 3, 2007
I am trying to create a formula that will compute a variance between the numeric value in a given cell with the value in one of 3 other cells depending on the condition of the 1st cell. Example: If the Value in cell F4 has a condition defined as "PMR" in cell C4, subtract the value in cell F19 from F4. If the condition is defined as "DBR" in cell C4, subtract the value in F20 from F4. And if the condition is "OTH", subtarct the value in F21 from F4.
View 4 Replies
View Related
Mar 18, 2009
I have a named range (D:16 F:800) called “NameRange” which contains names.
I’d like to create a macro which offsets a letter “x”, 10 columns to the right everywhere that name appears within the range. As an example: If I’m trying to find the name Tom, A dropdown menu shows the name “Tom” ( which I’ve created) whereby it calls upon a script to search within the named range from this:
Bob Kim Tom
Tom Sue
Mary Bill Ted
Tom Sue Bob
Fred Bill Sue
Bob Tom Kim
and offset “x” to look like this:.......
View 4 Replies
View Related
May 8, 2014
I'm trying to obtain a result based on the number of characters in a cell. If the cell content is blank or contains 9 digits then leave blank.
Any other result must provide the outcome of "incorrect number supplied".
I've tried the following formula but it doesn't work.
View 3 Replies
View Related
Jul 9, 2014
I am trying to search the cells in Column A around rows 54 to 77. And if the cell says "Yes" it should hide the row. I think I'm on the right track, but can't seem to get it work.
[Code] .....
View 6 Replies
View Related
Mar 18, 2012
I am working on a spread sheet for work were we have to test certain items on certain days, e.g. one item needs to be tested every Tuesday & Thursday, when the test is completed you click on a drop down box and click on completed or in progress.
What I would like are a couple of things to happen as follows:
A message box would pop up when you open the file to inform the employee that the test is due today only on Tuesday & Thursday if not completed or in progress from the drop down box. Once you select completed or in progress from the drop down box the message box no longer appears. Every Tuesday & Thursday when it's first open it deletes the cell content, thus triggering the message box test is due today, and once completed or in progress it no longer appears until next Tuesday or Thursday.
The spread sheet is on shared drive and used by several users, let's just say cell A1 is the cell I have the drop down box
View 1 Replies
View Related
Aug 7, 2006
I have a drop down list made from the Forms toolbar with a list of items: 1, 2, 3, 4, 5. I have set it with a linked cell, so when you choose, say 3, from the dropdown, 3 appears in B10.
I was hoping to add a 'Go' button, which would run another macro depending on what is in B10, i.e. so if 3 was chosen, then you press 'Go', Macro3 would be run.
So I have 5 macros called Macro1 - Macro5, and I need some code for the 'Go' button which will run the right macro depending on what number is in cell B10.
View 9 Replies
View Related
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
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
Jan 22, 2013
I have standardized each existing worksheets and any new worksheet to have certain parameters. The purpose of the Macro requested is ensure these worksheets are sorted alphabetically (and only these worksheets). The code I have been working with is contained below. It is working as expected but now I need to incorporate a statement which places the following condition:
Sort worksheets only if A3 has cell value of eCRF NameIgnore worksheet name: TOC & ENTER ECRF NAME
VB:
Sub Sort_Tabs()
'declare our variables
Dim i, j As Integer
[Code].....
View 5 Replies
View Related
Feb 16, 2010
I want to format a range of cells based on the name listed in a cell in column A (starting @A6). I currently do this manually. It is to help me visually see the line I am working with and for what person.
I am almost certain that is going to take a VBA, but I know little to nothing about setting one up.
I have attached a sample. I am using Excel 2003.
View 7 Replies
View Related
Sep 30, 2007
I just ordered vba for dummies, in the meantime I will have to get my info from the experts on this board. I want to use a name I have in a cell in a msgbox routine.
Currently I have
View 10 Replies
View Related
Oct 14, 2008
I have created two buttons in a worksheet to navigate to two other worksheets. I would like a cell to display 'YES' automatically if one of the two buttons is clicked and or to display 'NO'. I also would like to change the colors of these cells change automatically depending on which button is pressed.
View 2 Replies
View Related
Oct 24, 2005
I have a 3 column table with 20 rows. The first two columns contain travel
expense data. The cells in the third column are blank exept for whichever
cell I enter an "x" in. Please let me what formula or worksheet function do
I use to calculate the travel expenses based on the data in the first two
columns that correspond to the row of the cell with an "x" in the third
column?
View 9 Replies
View Related
