Automatically Enter Cell Value Based On The Values Of Two Other Cells
I am working on blood pressures. I want cell C1 to place either, "Prehypertension","Stage I Hypertension" or, "Stage II Hypertension" depending on the values of cells A1 or B1 ....
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Automatically Format Cells On Enter
I'm trying to write a macro that automatically italicize the a range of cells when you select a cell, and de-italicize it if you click it again. In this example, when select B12, the macro will automatically select B12:H12 and italize them. And if you select B12 again, it will automatically select B12:H12 and de-italize it. This is what I have so far: Private Sub Worksheet_SelectionChange(ByVal Target As Range) MyAddress = ActiveCell.Row If MyAddress > 11 And MyAddress < 159 Then If Not Intersect(Columns(2), Target) Is Nothing Then Intersect(Columns(2), Target).Resize(, 7).Select End If Select Case Target.Font.Italic Case "True" Target.Font.Italic = False Case "False" Target.Font.Italic = True End Select End If End Sub The code above does not work correctly.
View Replies!
View Related
Information To Enter The Tables And Then Automatically Move Into The Next Available Cells Below
I am trying to use visual basic editor in excel. I have all ready set up my user form where information can be entered, but I ave having trouble getting the information that is entered in the user form into the correct cells in excel. I am wanting my information to enter the tables and then automatically move into the next available cells below. Private Sub cmdadd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("details of cars in stock") iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row If Trim(Me.txtreg.Value) = "" Then Me.txtreg.SetFocus MsgBox "please enter a registraion number" Exit Sub End If ws.Cells(iRow, 1).Value = Me.txtreg.Value Me.txtreg.Value = ""
View Replies!
View Related
Automatically Calculate 2 Cells Based On Cell Changed
I have some rather complex formulas that when a value is placed in one of any three cells it solves for the other two, taking values from other places on a worksheet (including the cell itself). Scenario: Cell F10, G10, and H10 all have formulas in them. If a value is placed in F10 it solves for G10 and H10. If a value is place in G10 it solves for F10 and H10. If a value is placed in H10, it solves for F10 and G10. I really do not think the actual formulas matters at this time... Has anyone done this before, using VBA code to place the formulas in cells F10 G10 and H10? Look for a working example of this if possible.
View Replies!
View Related
When A Date Entered On A Specific Cell, Automatically Enter A Text In Other Cell
I have 6 Headings in excel named... "A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1. There are two projects. Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E. My Specification follows... 1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.
View Replies!
View Related
Fill In Cells Based On Other Cell Values
I am looking for a way for Excel to fill in cells in a column that has data sporadically in it. I am importing a text file into excel and saving it as an Excel file. In the example attached, I need the numbers filled in all the way down to the next number. I don't care about the text, just the numbers. I realize I can copy and paste, but these files can be several hundred rows long and it seems there should be a better way.
View Replies!
View Related
Changing Values Of Cells Based On Change Of One Cell Value
I am trying to do is to write a code that will change the values of cells B17:B25 to "false" when the user selects "true" from the drop-down box in cell B16. Here 's my Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "B16" Then If Target.Value = "TRUE" Then Range("B17:B25").FormulaR1C1 = "FALSE" End If End If End Sub This is not working! Nothing happens when I select "TRUE" in cell B16!
View Replies!
View Related
Clear Cells Of Values Based On Cell Selection
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 Replies!
View Related
Conditional Input In A Cell Based On Data In Two Different Cells And Other Values
I have lot of data in three columns like this. The first column is "Name", the second is "Comment" wherein I want the macro to write some comment, the third one is DOB. The problem is that the names in column 1 repeats many times. I want a macro to write in column B "either Old or Older or Oldest" based on the Name and DOB. Thus David with DOB 13 Sep 1982 be marked Oldest in Column B and David with DOB 25 Aug 1988 be marked Older and David with DOB 24 May 1990 be marked Old. Similarly William and Rita should be marked either old or older or oldest. As the data are enormous I do not want to refer the actual name in the programming. I want to call them using a variable in programming. NameComment DOB David 25 August 1988 David 13 September 1982 David 24 May 1990 William 24 March 1980 William 25 July 1987 William 13 August 1989 Rita 17 July 1990 Rita 24 April 1989 Rita 13 June 1988 The example file is attached
View Replies!
View Related
Automatically Sum Cells Based On Condition
I am trying to Excel VBA for doing an AutoSum function in Excel.I am struggling as the recorded macro is giving me fixed ranges whereas the number of rows would be different each time. What I need that the Delivery Qty in Column C be a Total whenever there is a text "Results" in column B?This should happen for every row with Results as a text.In Excel the Autosum feature should take care of this but I am unable to write a VBA code to do this. Before DeliveryAct.goods issue dateDelivery Qty 800205573906/05/20094 Results 800205657806/01/200921 Results 800206322306/02/200910 800206322306/02/200915 800206322306/02/200942 800206322306/02/200940 Results 800206322806/02/200941 800206322806/02/200910 800206322806/02/2009455 800206322806/02/2009410 800206322806/02/2009485 800206322806/02/2009785 800206322806/02/200944 Results.................................
View Replies!
View Related
Automatically Enter Password
I have recently written some programming in a spreadsheet (1) which, on opening, opens up another spreadheet(2), ammends this spreadsheet(2), and then closes it before the user can start using the original one(1). This is done to monitor the usage. The information added into the atomatically opened spreadsheet(2) includes date and time of opening and the users id as well as adding 1 to counter cells. Both .xls files are located on the same server and I dont want people to catch on to the monitoring and if they do I dont want them to then go into this file(2) and remove their information. If I password protect this file(2) it then promts to enter a password when it runs at the startup of the initial spreadsheet(1) which is no good as it would defeat the purpose if the user knew the password. Is it possible to add a few lines into the code (see below) after it initiates the opening of the usage file? Private Sub workbook_open() Application. ScreenUpdating = False Workbooks.Open Filename:= _ "S:Newman CommonCATEGORY MANAGEMENT - RangesEPoS Usage.xls" Dim Counter As Integer Counter = Cells(2, 7) Counter = Counter + 1 Cells(2, 7) = Counter Cells(Rows.Count, "BV").End(xlUp).Offset(1, 0) = Now Cells(Rows.Count, "BW").End(xlUp).Offset(1, 0) = Application.UserName ActiveWorkbook.Save ActiveWindow.Close Application.ScreenUpdating = True End Sub
View Replies!
View Related
Sum The Values In Cells Based On The Values Other Column
I want to sum the values in cells E2:P110 based on the values column D. The values in D are formulas resulting in something that appears to match D112 in some cases. I'm using the following equation: =SUMIF(D2:D110,D112,E2:P110) My problem is that D2 :D10 have a formula in it and it's not matching. If I enter the result of the formula, all is good. How should I deal with this?
View Replies!
View Related
Automatically Format Range Based On Formula Result Of Cells
The following code works perfect but the "change" event is only triggered when working directly on intersect range. Tried using the "calculation" event but could not figure it out. This is what I want: 1) To replace the code provided below using the calculation event 2) To only trigger the event for the row(s) where the new value was generated, not for the whole "For Each" statement 3) To use one single code for all worksheets, instead of copying the code in every working worksheet on the workbook, if feasible 4) And I would like a "second alternative", where the user of the workbook can click on a button and trigger the event on every row on the workbook that has a non empty cell within the intersect range, assuming that the intersect range column is the same for all worksheets Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean Set d = Intersect(Range("I:I"), Target) If d Is Nothing Then Exit Sub For Each c In d Select Case UCase(c) Case "C" fc = 1: fb = True: bc = 4 Case "O" fc = 2: fb = True: bc = 3 Case "D" fc = 2: fb = True: bc = 46 Case "G" fc = 2: fb = True: bc = 5..................
View Replies!
View Related
Automatically Add The Numbers As I Enter Them
How do I get a cell to automatically add the numbers as I enter them,for example. I want to keep track of my gas bills for income tax. So if I go in each day to add that days gas amount I want that cell to auto add each entry for me, rather that constantly add my bills and then re-enter every time I enter new amounts.
View Replies!
View Related
Automatically Update A Protected Cell Based On Contents Of An Unprotected Cell
I am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells. For example: Cell 'A1' starts out blank. Cell 'B1' has a drop down to select specific items. Cell 'C1' is the date of the order and is a locked cell. Cell 'D1' is the date of completion and is a locked cell. The worksheet is protected to prevent direct changes to cells C1 and D1. If a user enters any information in A1, then C1 displays the current date. If a user selects 'Complete' from the drop-down list in B1, then D1 displays the current date. Both of these actions are independant of one another. This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at.
View Replies!
View Related
Automatically Enter To Next Row Once The Typing Has Reach The End
Is there a way that Excel can automatically enter to next row once the typing has reach the end. Example: I have 5 columns (A to E) and I am typing at column A. While typing, the text will go along to cell B, C, D and E. The problem is, if I don't manually go to next row and continue typing, the text will go to column F, G and so on. Is there a way where excel can automatically jump to next row if the text has reach column E? Another problem is, say I have 3 rows full of text (column A to E). If I edit one of the row to exter some new texts, the whole sentence will go along to column F, G and so on. What I can do now is, re-edit all the rows to adjust them back.
View Replies!
View Related
Count Unique Cells Based On Ajacent Cells Having Values
I have the following data in 1 of the tabs. [TABLE] ClassSequenceAB 10SE132422 20SE23212 20SE321 20SE23425 10SE332455 15SE132412 10SE234 [/TABLE] I want a unique count of sequences in a different for that class only if that particular row in 'A' or 'B' is populated. The result set should be as follows: [TABLE] ClassAB 1032 1511 2012 [/TABLE] Can this be achieved through a formula?
View Replies!
View Related
Cell Adds Previous Values Automatically
I have a market data linked to excel through DDE link so prices are updated real time. Cell A5 contains last traded volume which updates whenever a trade is done. I would like to SUM/ADD all last trade volume in cell C5. Is there any function or macro can help me to do this?
View Replies!
View Related
Automatically Subtract Multiple Values In The Same Cell
I wish to have a column with say 100 in it as that will be the initial stock levels I would then like the next column to be blank so that when you enter say 25 (the number of stock taken away) the third column will show the new number of 75. Simple? well here is the tricky part I just want those three columns so the blank one in the middle will remain blank after it has been used so for example on monday we sell 25 pencils and type 25 in the field and the new total is 70 on tuesday we sell 40 pencils and type (in the same field the 25 was put in the day before) 40 and the total will show up as 35.
View Replies!
View Related
Automatically Add Rows To End Of Spreadsheet By Pushing Enter
I am making a template for my company to automatically calculate the amount of sheet metal needed for a specific job. The spreadsheet could get very long depending on how much duct is needed. Is there any way I can automatically insert rows to the end of the sheet by pressing enter after filling the last row with data, which would then move the totals down. Also, the formatting of the rows I wish to add need to be copies of the ones above.
View Replies!
View Related
Highlighting Cells Based On Cells Values In VBA
I need to highlight a cell when its value exceeds parameters based on the production line it comes from. If the line is K11, then i need this cell to highlight when its value is either < 0 or greater than 221. If the line is K21, than it needs to highlight when its value is <0 or greater than 474.
View Replies!
View Related
Automatically Send Email Based On Cell Value
I have created a detention tracking sheet for my school. I need an email to be automatically sent to the teacher when a student does not show up for detention. Here is what I have currently coded, but the macro is not running, and the email is not being sent. I do not understand why. VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes" ....
View Replies!
View Related
Automatically Color Rows Based On Cell Value
I currently use spreadsheets to report on backup processes. I am trying to speed up this process and one thing that woudl help me is a function that will automatically fill the row with a colour depending on the choice in the dropdown box, so if you choose 'successful' from the dropdown list the row colour would fill to green and if you select 'failed', the row would change to red and a choice of 'completed/errors' would result in orange. This process is currently done manually and often results in errors like a succesful result with a red filling. Is there anyway to automate this process? i was trying to whip up a macro, but have been unsuccessful so far.
View Replies!
View Related
Automatically Populate Cell Based On Worksheet Name
I have a macro that copies data from 6 different worksheets into one summary worksheet - based on certain criteria - I need a way to note on each row of the summary sheet which worksheet it was copied from. Each worksheet is specific to a salesperson, so ideally I would like that salespersons last name to populate in column on the summary page for each line of theirs that is copied over....here is my current macro I use to copy the data: (this scrpit is repeated for each sales persons sheet - "Blankenship", "Dew", etc...)
View Replies!
View Related
Name Sheet Automatically Based On Changing Cell
In my sheet called summary I have the names of the rest of the tabs in the book in cells B5 to B34. We want to be able to change the names of the tabs by changing their respective cell on the summary tab. So “sheet1” corresponds to B5, “sheet2” to B6, “sheet3” to B7, etc.. So if I change the name in B5 to say “APPLE” I want the tab for sheet1 to change to APPLE. When I change B10 from “sheet6” to “Lemon” I want sheet6 to be titled “Lemon”.
View Replies!
View Related
Automatically Name All Sheets Based On Cell On Sheet
I have recently used the following code to name a sheet from a cell within that sheet: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("D1") Then ActiveSheet.Name = Target End Sub What do I need to add to this bit of code to allow me to name the sheet from another sheet's cell i.e Sheet1 gets named from say Sheet2 Cell A2
View Replies!
View Related
Automatically Fill Cell Value Based On Another Cell
I have certain values in cell B4 to B2000. There are 25 unique values in those cells. In cells A4 to A2000 I would like to put a identifier (lets say a letter from A to Y) based on the adjacent B4 to B2000 cell values. I can't use a IF function because I am way over 7 allowed in excel. I could use a vlookup but that seems to slow down the file quite a bit. Is there something else better/faster out there?
View Replies!
View Related
Automatically Clear Range Based On Cell Criteria
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 Replies!
View Related
Automatically Hide / Show Rows Based On Cell Value
I wrote a small code to hide some columns if a certain cell is equal to a certain string. The cell is actually a drop down list and when they select a certain one, I want it to hide 2 columns. So I wrote the code with sub name Action, but I want it to be running all the time. I tried to achieve this by writing the following code however it gave me error 438 for my 2nd line. Sub Auto_Open() Range("A1").OnEntry = "Action" End Sub Auto Merged Post Until 24 Hrs Passes;Oh, by the way error 438 states: Object doesn't support this property or method
View Replies!
View Related
Automatically Filter Worksheet Based On Changing Cell Value
Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.AutoFilterMode = False Range("d6:g6"). AutoFilter Range("d6:g6").AutoFilter Field:=2, Criteria1:=Range("e1"), Operator:=xlAnd, _ Criteria2:=Range("e2") I've attached the file also. What i intend to do is that : (1) if i enter a value in E3, the filter should only apply using E3 value (currently its applying E3 value but if E4 is kept blank, it takes that as = " " ) . Unfortunately, i need to have the and condition, so i have to find a way in spite of this condition. Any way out ?? (2) If i enter values in A) E3 & F3 B) E3, F3, G3...then it ahould make multiple filtering possible. But when i try to apply such a condition, the same problem as in point (1) occurs, it takes the and empty criteria range as = " "
View Replies!
View Related
Tally Cell Automatically Based On CheckBox State
In my spreadsheet the user has the option to put in a numerical value into different cells, which will update (add to) another. So if we have an initail value of 20 in "I10" and the user puts in 20 in "W12", then I10 will display 30. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("W12:X24")) Is Nothing Then Select Case Target Case Is = Range("W12") Logbook.Show Range("I10").Value = CInt(Range("I10").Value) + CInt(Target.Value) Case Is = Range("X12") Logbook.Show Range("J10").Value = CInt(Range("J10").Value) + CInt(Target.Value) Case Is = Range("W13") Logbook.Show.................
View Replies!
View Related
Automatically Format Range Based On Specific Cell
I am looking for VB code that will format a range of cells from a number format to a percentage format based on a reference cell. The reference cell is a drop down created from a list validation. For example: If the reference cell (cell A1) =1 then cell range B2:C4 would be a number format. If A1 = 2 then cell range B2:C4 would be a percentage format. I have searched for hours trying to find a relevant thread; I know they are out there, maybe I just wasn't typing in the correct search words.
View Replies!
View Related
Automatically Check Option Button Based On Cell Value
Is there a way to set up a "watch" on a cell so that if you type in a different number on a cell, OptionButton1_Click() gets activated? Example: A9 = 12.0104 OptionButton2 is active. In Cell A9 you type "25.0508". OPtionButton2 becomes inactive and OptionButton1 becomes active. I already have the buttons linked, i just do not know how to make the button get triggered if A9 changes.
View Replies!
View Related
Summing Up Cells Based On Values Of Other Cells
I am working on a sheet which calculates payroll. I have a list of people in column A, and in column B, i have their different professionnal status. Their wages vary according to their professionnal status and are in another column C. How can i sum up the wages of only certain people, based on their common professionnal status in column B?
View Replies!
View Related
Automatically Move & Sort Row Based On Cell Value
I have a simple projectlist on sheet1 formatted like this: Column A=Projectname, B=Description, C=Owner, D=Deadline and E=Status. On sheet2 I have the same columnheaders. What I would like to do, is that if Status changes to "Done", * the entire row should be copied to a free row on sheet2, or just inserted on sheet2.row2 without overwriting anything * the now empty row on sheet1 should be deleted (so there're no empty rows) * the data on sheet2, should be sorted with D as first key and C as second. Then, if Status on sheet2 changes to "Reopened", the same procedure should happen but from sheet2 to sheet1. My first choice was to create a checkbox on each row in the Status column,but I noticed it didn't seem to follow with cut/paste even though I changed the property Locked to false. Anyone knows if it's possible to change this behaviour? I only know small bits of VBA but my idea is this: ...
View Replies!
View Related
Automatically Hide/Unhide Columns Based On Cell Text
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column <> 13 Then Exit Sub If InStr(Target.Value, "Other (specify in next column)") Then Columns("N").Hidden = False ElseIf WorksheetFunction. CountIf(Columns("M"), "Other (specify in next column)") = 0 Then Columns("N").Hidden = True End If End Sub but I have a lot of columns that I need to perform as above and I have put the code together as below Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Column = 13 And InStr(Target.Value, "Other (specify in next column)") Then Columns("N").Hidden = False ElseIf WorksheetFunction.CountIf(Columns("M"), "Other (specify in next column)") = 0 Then................. Using the above code, when I selected more than one cell anywhere in my workbook and pressed delete I was bugging out with a runtime error 13 message. You can see from the above code that I inserted "On Error Resume Next" - this got rid of the runtime error 13 message, but now when I select more than one cell and press delete, hidden columns are incorrectly revealed in my worksheet. how I can extend the working code at the top of this posting so that it works for a number of different columns in my Worksheet i.e. without the runtime error 13 occurring and without columns being incorrectly revealed.
View Replies!
View Related
Concatenate Cells Based On Values
I have a column say column A. It has a pattern of cells where there is a code C10A3(alpha numeric) in say A1 and then in cells A2-A9 there are descriptions (alpha only). This continues uniformally down to cell 300. So every x amt of cells down a code appears and then below this for x cells is a description. What i would like to do is create a macro to concatenate the code waith each of the descriptions and paste it in the adjacent cell to the description. So.... A1 =C10A3 A2 =Global A3 =America A4 =South AMERICA A5 =aFRICA ETC A10 =D05a9 A11 =Global I would like to concatenate A1 and A2 and have the output in B2 (C10A3Global.) And A1 and A3 with the output in B3 etc. This should continues until A10 (where the cell contains alpha numeric characters) where nothing is entered in B10 and then in B11 there should be D05A9Global.... The only difference between the description and the cell is numeric characters...
View Replies!
View Related
Automatically Call Macro Based On Changed Cell Address & Text
I have the following code pasted into the worksheet module which used to work fine but no longer does. I didn't touch the code, it just stopped working. Private Sub WorkSheet_Change(ByVal Target As Range) Select Case Target.Address Case "$A$16" Select Case Target Case "Custom Color 1": Call CustomColorInput1 Case "Custom Color 2": Call CustomColorInput1 Case "Custom Color 3": Call CustomColorInput1 Case "Custom Color 4": Call CustomColorInput1 Case Else: End Select Case "$A$17" Select Case Target Case "Custom Color 1": Call CustomColorInput2 Case "Custom Color 2": Call CustomColorInput2 Case "Custom Color 3": Call CustomColorInput2 Case "Custom Color 4": Call CustomColorInput2 Case Else: End Select Case "$A$18" CustomColorInput 1-6 are the same except they input data into different cells F16-F21. When I attach any of the CustomColorInput macros to a button it works fine. When I try to call it from the worksheet module, the data input box pops up but the data doesn't get entered into the cell. When I try to step through the CustomColorInput macros using F8, the input box pops up, I enter data and press ok. Then I continue with F8 and the debugger jumps to a custom function that I entered (below), which is not called for and is in no way related to the code. Function PullAfterLast(rCell As Range, strLast As String) PullAfterLast = Mid(rCell, InStrRev(rCell, strLast) + 1, 256) End Function When I close VBA, the data that I typed into the data input box is in the correct cell. So basically, I'm getting three different responses from the offending code depending on how I call it up. Can anyone tell me what's going on? More generally, in this and previous occasions, I've had problems with macros that cease to work for no reason that is apparent to me. What are some things that would cause a macro to stop working, without actually changing the code (other than the obvious, like referring to names or worksheets etc. that don't exist anymore)?
View Replies!
View Related
Altering Cells Automatically With One Cell Input
i have on sheet one a cell that requires an order number ( e.g something like j2345a ) what i would like to do on sheet 2 cell c10 i would like to extract the letter after the 4 figures so that what i would see in that cell is j2345. what i should add is that the letter after the four numbers isnt always an "a" so it could be any letter that needs to be removed i also need cell c10 on sheet 2 to remain empty if there is nothing entered in cell e8 of the first sheet
View Replies!
View Related
|
TRACKER
