Vba Cell Control Based On The Cells Contents
Is it possible to place formulas in vba to control cells based on the cells contents.
A2 will be the the price of an item excluding vat and b2 will be the price of an item including vat
What i want to achieve is if I put a figure into a2 b2 then multiplies a2 by 1.15 to reflect the price including vat. If this is achievable I then want to reverse the proceedure so if I put the price including VAT into b2 a2 divides b2 by 1.15 to reflect the price exc. VAT.#
if all of the above is clear and possible I would like to know if it is easily possible to copy the workings for a3, 4 ,5 ,6 etc and b3, 4, 5, 6 etc.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Give Cell A Unique ID Based On The Contents Of 2 Other Cells
I am trying to give a cell a unique ID based on the contents of 2 other cells. Basically in cell A1 a value is entered between 1 and 13 and in cell A2 another value is entered between 1 and 4 to find the unique ID that matches these I need to look in DF4:FE4 to find the number that matches A1 ( there will be 4 matches) then look in thoses 4 matches in DF5:FE5 to find the match to A2 ( there will only be 1 match ) then return the id from the relvant cell in DF3:FE3.
View Replies!
View Related
Add Cells Based On Contents Of Cells In Another Column
I have a spreadsheet that has columns stating both status and then further to the right in the row, currency totals. There are only two status options, Stocked and On Order. I need to add the total currency amounts based on this other column's listing and it must change if status changes. (Put simpler: Column A lists Stocked or On Order and Column B is the currency tied up in that row. I need to make totals for the cells in column B based on the status listed in Column A and it must change when Column A is changed. This should result in two totals, one for Stock Status Currency and one for On Order Curreny)
View Replies!
View Related
Go To Range Based On Cells Contents/Text
I am just learning VBA. I need to move to a cell location that is named in a range. As follows: The cell "Sheet2!A1" has a formula that results in "Sheet1!B3". I would like my VBA code to read that cell and select Sheet1!B3. When using something like: Application.GoTo Range("Sheet2!A1") I end up of course at Sheet!2A1. I want to end up at Sheet1!B3 or wherever the cell reads at the time the code is read and operated on.
View Replies!
View Related
Copy Row Based On Contents Of 2 Different Cells.
I would like to do now would be to only copy the rows that also contain the word sold in column C. I guess that I could do this in two stages, first copying the rows based on the year then copy again based on the word sold in column C. It would be nicer to make a slight modification to my code and do it all in one step.
View Replies!
View Related
Conditional Formatting - Based On Another Cells Contents
I have a worksheet that I've applied conditional formatting to which works very nicely apart from when I use the formatting and apply it to a cell based on the contents of another. What I don't understand is when I put a formula in cell b1 to read =$a1="yes" and format accordingly it works. So when I type in "YES" in to cell a1 ,cell b1 is highlighted. When I type in "NO" the B1 cell isn't highlighted which is what i would expect. IF however I delete what ever is in cell A1 the cell B1 is still highlighted.
View Replies!
View Related
Merging Cells Contents Based On Unique Identifiers
I’m having some problems writing a macro, I have a spreadsheet with 2 columns. The first column is the index number (unique identifier) of the second column’s data that will repeat itself as many rows as needed. Let me try to exemplify it: Column A (index)Column B (text) 1 AAA 1 BBB 1 CCC 2 DDD 2 EEE 3 FFF 3 GGG 3 HHH 3 III I’m trying to write a macro that will merge the text of each identifier in the first row it appears. The result I’m looking for is: Column A (index)Column B (text) 1AAA BBB CCC 2DDD EEE 3FFF GGG HHH III I already know how to make the merge, but I can’t get the logic to make it go through each row and merge the contents, since the number or rows for each identifier varies.
View Replies!
View Related
Filter Out Duplicate Based On Contents In 2 Cells On Same Row
I have data contained in 4 columns. Column A is name, Column B is ID, Column C is Company, Column D is amount. There are duplicate names in column A and duplicate ID's in column B (ID's have correspending Company Names in Column C). I want to have a list so that this combination shows up only once, and then the amount next to it will be the sum of all. Example: DataABC DTom9730-003Company B100 Joe10242-001Company A200 Tom9730-003Company B300 Joe10242-001Company A400 Sam9730-003Company B100 Desired ResultTom9730-003Company B400 Joe10242-001Company A600 Sam9730-003Company B100
View Replies!
View Related
VBA Code To Go Through Column Cells & Return Value Based On Cell Value
I have been stuck for about a week on trying to create a Case Select macro. I am a novice in using VBA, so I am not sure what I am doing right OR wrong. What I need the macro to do is go through all of the text in a column and if the text is one of the 50 states, I need it to return one specific value, and if it is anything other than one of the 50 states, it needs to return a different value in a column 5 over from the column with the states in it. I have changed the code MANY times and so far I haven't been able to get anything to work. Here's the gist of what I have been trying: Sub usstates() Dim R As Range, s As String Set R = Sheets(1).Range("J1:J50000") For i = 5 To R.Rows.Count X = UCase(R.Cells(10, 1)) If X = "AL" Or X = "AZ" Or X = "AK" Or X = "AR" Or X = "CA" Then R.Cells(i, 2) = "SC" End If Next i End Sub.................
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
Sound Based On A Cell Contents
I have thousands of items each with a unique barcode(column A) and each is catorgorized either KEEP or DISCARD (column B). What I want to do is scan the barcode (column A), Vlookup and return KEEP or DISCARD (column B). Now based on what the vlookup returns can I have a sound file play for KEEP and a different one for DISCARD.
View Replies!
View Related
Print Based On Cell Contents
I have put together a spreadsheet to help with the creation of work orders. I have a summary page that the user can define which category the work order falls under along with this I have a couple of macros set up that will print a specific work order pertaining to the category they selected. I have run in to a few problems (mostly training users) that I would like to eliminate up front. I am looking for a macro that I can use to look at the contents of a cell (contains a word that corresponds directly to the name of one of the sheets in the work book) and print the worksheet that has the same name as the contents in the referenced cell.
View Replies!
View Related
Get A Cell To Look At A Row Based On Contents
I want to have a cell that looks at another cell based on a number that is in another cell. So imagine if you will: A1 contains the number 25. A2 contains the formula I can't work out. I want it to look at the row that is the number contained in cell A1, so in this case it would look at row 25. Now I want to narrow it down further to a cell in that row but this will be a constant so lets say column F. So if A1=25 then A2 would display what ever is in F25. If cell A1 were 16 it would display the contents of F16 etc. The best I got was something like =IF(A1=>1,(F(A1))) but obviously that is wrong as it doesn't work.
View Replies!
View Related
Workaround For Public Variable Bug: Creates Different Cell Colors Based Upon The Cell Contents
I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug. Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
View Replies!
View Related
Clear Contents Of Cell Based On Comparsion Of Cell Values
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 Replies!
View Related
VBA To SHADE Cells Based On: Step-size & Starting Cell
I have a spreadsheet that will monitor payment schedules, in which both payment frequency and the payment start date are inputted by the user. As such, to make filling out the column(s) fool-proof, I want to grey out cells in which data should not be entered. For example, if the payment frequency is every 6th day, and the payments are to begin on day 0, then days 0, 6, 12 (etc) should be left white, whereas the remainder of the cells should be shaded. I can achieve this using multiple conditional formatting rules in excel2007 with iterations of formulae of the type:
View Replies!
View Related
Lock Row Based On Cell Contents
I wish to be able to lock an entire row after an entry has been made in a certain cell. e.g. let's say i want to lock row 9 in its' entirety following entry of "Yes" in cell G9. If there is no entry in G9 the the entire row should be still unlocked. Likewise with any row with a corresponding entry in column G.
View Replies!
View Related
Calculating Total Based On Cell Contents
I'd like excel to calculate 3 totals for me based on the colour and value on a worksheet. Basically, I work for various people and they pay at different rates per hour. I currently have a spreadsheet with their names, times, and rates (see attached for example), but I calculate the amounts paid and due manually. If possible I would now like excel to do it. To explain further, 'J' gives me $10 per hour, and 'V' gives me $5 per hour. Cells shown in red show work done but not paid for. Cells shown in green show work done and paid for. I'd like excel to automatically create totals as shown on the spreadsheet, namely: Total due: xxxx Total paid: xxxx Total outstanding: xxxx At any time during the month I can be asked to take on more work - I would then enter the code into the spreadsheet for the hours requested...and I'd like the totals to be update automatically.
View Replies!
View Related
Clear Cell Contents Based On Defined Name
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 Replies!
View Related
Clear Contents Based On A Selection In Another Cell.
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 Replies!
View Related
Take Action Based On Change In Cell Contents
I have a range of cells that I want to watch for a change in contents. If any cell in the range has a change in contents I want to launch a MessageBox that asks if they wish the new value to be entered automatically in all of the other cells in the range. Example: Range = B8:M9 User changes F8 from "empty" to 38. MessageBox should launch asking if they want 38 to be entered into all of the cells in the range. Upon answering yes new contents of F8 should be inserted into the rest of the range.
View Replies!
View Related
Delete Rows Based On Cell Contents
I have merged two workbooks into one. What I need to do at this point is to delete all rows that have a duplicate entry, basically anytime the cell content in one cell matches the cell content in the cell right below or above it, BOTH rows should be deleted. At this point, this is above my VBA skills so I'm asking for help in how to do this. The stripped version of the workbook is attached (only 100 rows) but in reality this is a huge workbook with almost 22,000 rows. You will notice in the attached workbook, that cell contents for A2 and A3 match. For what I need to do, I need both rows (2 and 3) to be deleted. If you go down a bit, starting in row 89 all the contents in column A are unique so those need to remain.
View Replies!
View Related
Cell Contents Based On Drop Down List
Can the information in say cell b:1 be based on the information in A:1? Example: If A:1 has the name Fred Smith then I want cell B:1 to say Nurse. Column A will have a drop down list of names and column B will have a list of Nurse, EMT or Paramedic. When someone pick a name from the list the information in column B will automatically be filled in based on the information in column A.
View Replies!
View Related
LOOKUP Range Based On Contents Of A Cell
I want to be able to send out one “master” workbook with the fourteen ranges, and use a pull-down list (or anything else) so when the user selects a state, the proper lookup range will be used. In the attached abbreviated example, I want the lookups in Column B to refer to the state designated in B2. In this case, the VLOOKUP works correctly, since Colorado zip codes are in the ColZip range. The people in Idaho or Oregon using this will only be working with their respective zip codes in ranges IdaZip or OreZip, so if they enter their state in B2, I would like the VLOOKUP to look in a different range. My whole idea is to only have one master workbook so I don’t have to create one for each district office. Maybe I’m thinking in a wrong direction altogether.
View Replies!
View Related
VBA With Range() To TRIM() Cell Contents
I have column headings that come in through a file upload and for some reason some of the column headings(which are in cells in row1) have extra spaces in the middle of them and at the end of the word. This messes up some of my later coding when trying to find specific header names. Example: "Location " - has extra 2 spaces at end, should be "Location" "Read (Only)" - has 3 spaces in middle, should be "Read (Only)" I came up with this piece of code to quickly correct the bad headers by Trim()'g the contents of each cell, but it's error'g out & not sure how to fix it: With Range("A1:A24") .Value = Trim(.Value) End With It errors of course on the '.Value' line.
View Replies!
View Related
Rename Tabs (Sheets) Based On Cell Contents
I have 5 sheets. In the first sheet I have set up 4 cells where I want the contents of them to automatically rename the other 4 corresponing sheets. Eg. In Sheet 1, Cell B6, I want the contents (which will be text) to be the name of sheet 2 automatically. In Sheet 1, Cell B7, I want the contents (which will be text) to be the name of sheet 3 automatically.
View Replies!
View Related
Copying Cell Contents From One Sheet To Another Based On Colour
I have a workbook called "Staff Planner". I need to check Columns C to I in Sheet "Plan" to firstly find text in a red font, and then copy this value to Sheet "Useage" in Column K. (So, search C1:I1, find red font, copy contents of cell, paste value to K1). I then need to repeat this with blue font/value to H1, green font/value to E1, and finally pink font/value to B1
View Replies!
View Related
Delete Contents Of A Cell Based On Char Count
I have this forumala. =RIGHT(A1,MIN(500,LEN(A1))) I would like to apply this formula to VBA and call to existing macro so that this is applied to all the cells in Col. A. How can I do that? Or, is there a better way to do it? Buttom line, I'd like to see only the last 500 characters per cells in Col. A after I run the macro.
View Replies!
View Related
Macro - Format Rows Based On Cell Contents
I have a spreadsheet that has a subtotal row after each data grouping. I need help with a macro that will go to column C, look for any cells with the word "Subtotals" in it, and format that row based on another macro I already have set up. I've tried about 50 different types of macros that I found out on the web, but none have worked.
View Replies!
View Related
A Formula That References A Table Based On Contents Of Another Cell
Here is what I am trying to achieve: I have a workbook with two tabs, on the first one there is one column and the top of the column(A1) is a dropdown containing the options 'Boy' or 'Girl'. One the second worksheet I have two named tables (Boy and Girl) - these tables each contain a single column array with 5 names in each (so we have a table of 5 boys names and a table of 5 girls names) What I want to happen is when a user selects an option from the dropdown in A1 the cell below(A2) is populated randomly with a name from the corresponding list. I have achieved this initially using the following formula: =IF(A1="Boy", INDEX(Boy,RandInt(1,10)), INDEX(Girl,RandInt(1,10))) This works fine, but I will need to expand this for several dropdown options, so my new workbook will have a dropdown of car makes, and each make will have a list of the models associatied with the car make (e.g. Ford: Fiesta, Escort, Mondeo...) But I do not want to have a ridiculously long nested IF statement for every car Make, so what I want to do is something along the lines of the following: =INDEX([CONTENTS OF A1],RandInt(1,10)) So I use the text selected from the dropdown in A1 as the TableName in my formula - however, I cannot retrieve that to use in the formula without it coming back as a text with the "" included and my formula errors!
View Replies!
View Related
Find Text Based On Active Cell Contents
I have a workbook with 2 worksheets, A and B. Sheet A contains source data and sheet B a pivot table of this data. I want my user to click on a cell within the pivot table,click a button that runs a macro to find the instance of this value within Sheet A. I did record a macro but it did not work.
View Replies!
View Related
Showing The Contents Of A Cell Based On Another Cell
one cell gives me the MAX value of a range of cells I want to then use that value to refer back to the range of cells and return the value one column to the left This formula returns "A4" which is the cell number for this result I want to show but it just enters A4 not the contents of cell A4 ="A"&(MATCH(B10,B2:B5,1)+1)
View Replies!
View Related
Display Named Range Contents Based On Text In Cell
I have some dynamic named ranges in sheet1, and in sheet2 I have data validation dropdown list which has the names of all the DNR's in it. What I want it to do (in sheet3, no less) is for the user to be able to pick a named range from the dropdown list, and have a particular column in sheet3 then display that entire named range. I have made a 'data' worksheet which is the source for the data validation in sheet2. Each item in the list identically matches the name of each of the named ranges. I was hoping to be able to use some form of =INDIRECT but alas, no such luck. So it's almost like a copy and paste function I'm after, where: If you pick "Schedule_From" out of the DD-list, then DNR 'Schedule_From' is what is pasted in Column B in Sheet3. DON'T want to use a PivotTable.
View Replies!
View Related
Paste Data To Next Empty Row Based On Cell Contents
I am trying to write a macro to do the following: Loop through a range of cells on a worksheet Sheet1 M17:M46 and if there is text in the cell then Copy all the data to the left Of Coumun M in the same row and paste the values to the next open row of a range A17:L46 on another worksheet (sheet2.) Basically the text in column M is an idicator to add the data to an order sheet. If there is no text don't add the data , look in the next row etc. I'm not sure if this is the way to go Sub CopyRows() Worksheets("sheet1").Select FinalRow = Range("M65536").End(xlUp).Row ' Find the last row of data ' Loop through each row For x = 2 To FinalRow ' Decide if to copy based on column M ThisValue = Range("M" & x).Value ' could be any value If ThisValue = "yes" Then Worksheets("sheet2").Range("A" & x & ":L" & x).Copy Worksheets("sheet1").Select NextRow = Range("A65536").End(xlUp).Row + 1 Range("A" & NextRow).Select ActiveSheet.PasteSpecial = xlValues Worksheets("sheet1").Select End If Next x End Sub
View Replies!
View Related
Conditionally Lock Row Based On One Cell's Contents
I'm trying to figure out how to lock a range of cells (all in a row) when some puts a specific character in one of the cells. I have a sheet where I want people to enter data. Once they put a 'C' in the last column to indicate that the entry is complete, the whole row should be locked so they can't change any of the cells for that entry. There are some good pointers already on the forum to help me get the basics: Conditional Cell Lock Unlock http://www.ozgrid.com/forum/showthread.php?t=66397 http://www.ozgrid.com/forum/showthread.php?t=76379 However, all of these point to specific cells rather than a range of cells that can be located close to the target of the change.
View Replies!
View Related
Name Cells From Adjacent Cell Contents
I want to create a macro that will run through a column and assign a name to each cell in that column that reflects the contents of another cell within each row. for example, cell A1 = "Gerry", A2 = "Rob", A3 = "Ryan" I would then like the Name of B1 to be "Gerry", B2 to be "Rob", etc (but to retain original contents). Is this possible using VBA?
View Replies!
View Related
Adding Multiple Row Contents Based On The Content Of A Single Cell
Been trying to work this one out and have ended up banging my head against the wall. Basically I have a spreadsheet, in the first column is the person's name, in the secon which team they are in and following this a cell for each day of the month with their availability. The team name is a selection from a list, the availability is 1-6 (different levels of availability) I'm trying to do subtotals at the bottom for each separate team, so the very last row each of the cells for each is added up: Name Team 1st 2nd 3rd 4th 5th 6th 7th etc. Mr X ___1st___1______2__1 Mr Y ___2nd_______2________5__5__5 Mr Z ___1st___1____________3__3__3 Total 1st: 2 2 1 3 3 3 Total 2nd: 2 5 5 5 So basically the formula in the last row goes along the lines of: Add each cell in the column IF the team name = 1st
View Replies!
View Related
Copy Paste Special As Values Based On Validation Cell Contents
Need VBA macro that will copy & paste (Special > AS VALUES) from one of two (Data A & B) sheets based on the contents of a validation cell ($D$4) in a third (Report) sheet? The destination starting cell would be $F$11. ALSO - I'd like to have the Named Ranges "DataAExtract" & "DataBExtract" used in the code (for the COPY region) so I can see an example of how to reflect my actual named ranges in my working file. The reason for doing this is that the "c.Characters...." lines in my conditional formatting macros (attached) are not working on cells containing formula output (in my working file the Report page is all populated by VLOOKUP results), but the macros run fine on hard-coded values. In my attached workbook, I'd like to have the "NEW" macro for the copy & paste step fire first in the sequence of macros running after the FORM button-click (control located in cell $D$5 of the Report sheet), whether that's by writing a new macro and calling mine before the new one ends, OR by consolidating all of my macros plus the new one into one smooth progression. With this low-tech approach I can get updated VALUES into the report area once the user selects a data source and a customer on the report sheet. The COPY ranges in my working spreadsheet will update based on the selections made in the report page. I tried recording a macro and then modifying the recorded code to add the "If > Then" functionality I'm looking for, but I'm pretty green when it comes to VBA code and syntax.
View Replies!
View Related
Change A Cell's Contents By Selecting Another Cells
what i need is to affect the contents of cell c3. if i select cell e15 i would like cell c3 to read 123 if i select cell e17 i would like cell c3 to read 234 i believe this could be done using an cell formula if statment but due to teh number of cells i wish to have affect c3 a vba based solution would be required. i've built an if satement to do this under worksheet activate but not sure how to use the worksheet change way
View Replies!
View Related
VBA Calendar Control Without Control
Has anyone out their ever seen an Calendar type of control totally built in an Excel vba UserForm? My problem that I’ve tried to resolve for some time is utilizing some type of pop-up calendar to eliminate format issues in my published Excel forms. I have tried a number of calendar controls but all have to be registered on the local machine and this cannot be guarantied for every machine. If someone could direct me to a vba UserForm that has this built in that might do the trick. Or is their another way to deal with this?
View Replies!
View Related
Name 1 Cell Based On Contents Of Another Cell
Step 1 - Find the column named "Date Closed" (it will always be in row 1) Step 2 - Find the column named "Status" (it will always be in row 1) Step 3 - If the "Date Closed" column has a date in it, add text to the cell in the "status" column in the same row that says "Closed". Step 4 - If the "Date Closed" column does NOT have a date in it and is blank, add text to the cell in the "status" column in the same row says "Pending". NOTE: Cell format in the "Date Closed" column is set this way... Number tab; Category is "Date", Type is *6/25/2009 Example 1 below, shows how data currently is...
View Replies!
View Related
|
TRACKER
