Get Value Of Cell Adjacent To Active Cell
I'm trying to compare two lists of foods, one input by a user (together with number of servings consumed), the other a list of foods with their calorie content. Then I'm trying to get the number of servings associated with the user's input (in a cell adjacent to the one I'm in for the comparison/search), and get the calorie content of that food from the list (also in an adjacent cell), so I can multiply the two to get total calories consumed. However, I'm getting, variously, "Wrong number of args," "Type mismatch," "Application/Object-defined error," and so on.
The code below shows what I've tried, and I think I'm doing something braindead basic wrong, but I can't find what my error is. Everything works in the snippet (that is, I've correctly Dimmed the variables, I get my food match, and the row indexes keep up correctly), except the attempt to write, for instance, the number of servings to the variable 'o'. That cell ref fails.
m = 7 'initializes the row index of Daily Data block of the Inputs sheet for the food items
n = 2 'initializes the row index of the Data sheet for the food items
r = 0 'initializes value total carorie content of food item across number of times eaten in a day
For Each Food In FoodName 'the User-input food
For Each datum In DataList 'table containing food's calorie content
If Food.Value = datum.Value Then
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Copy Active Cell Down Adjacent Range To Right
Split from: Clear Range To Right Of Active Cell Down. I was working through this the first step was to clear the contents of the cells. After they are cleared, I have another sub that then goes and retreives a unquie number and places it back at the top of the column that I just cleared. Would this same code that cleared the cells contents work to copy the contents of the active cell down that same column, until the cell to its right is blank? -R-
View Replies!
View Related
Find Cell Value In Column & Return Text In Adjacent Cell
I have a drop down list in a merged cell B12-F12 and B13-F13 and B14-F14 . . . B30-F30. I need the adjacent merged cell to populate a reason (text) based on the text answer in the drop down list or the entered text in the first merged cell. For example in the cell B12-F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12-J12to automatically fill with "Heart Rhythm." I also want to be able to set up multiple if - them statements like if Amiodarone is entered then fill adjacent cell with Heart Rhythm and if Toprol XL then fill adjacent cell with Heart / Blood pressure and if simvastatin then fill adjacent cell with Cholesterol, etc. I have about 30 different options for cell 1 that I want to have auto fill in cell 2 based on the contents of cell 1. I've attached my file. I want the user to be able to choose from the list or type the drug name in.
View Replies!
View Related
Delete Rows Where Cell Doesn't Equal Adjacent Cell
I have a sheet in Excel 2004 for Mac that is a patient list with over 2000 patients. I need a rule that will compare values (patient IDs and responsible party ID) in two adjacent cells, if the values are different, to delete the entire row. The goal is to get rid of patients (rows) that are not the responsible party (responsible party ID).
View Replies!
View Related
Incorporate Adjacent Cell In Formula W/o Naming Cell Address?
I searched and I keep finding something that's not quite what I'm looking to do so I think I'm missing something really simple. Let's say that I want each cell in col b to be the sum of 5 plus whatever value is in the cell adjacent to it on the left (col). I know that you can just write a formula in each cell like... b1 will be =sum(a1+5) b2 will be =sum(a2+5) ... but is there a shortcut so that you don't have to write out the actual cell address for each one? Meaning - is there a predefined name or something that represents the cell to the left or right so you can just use that instead, allowing you to just copy paste the same formula all the way down the column? Something like... b1 will be =sum(left+5) b2 will be =sum(left+5) ... where "left" represents whatever value is in the cell to the left of that particular cell?
View Replies!
View Related
Userform Find Cell From ComboBox Value And Write To Adjacent Cell
I am having a problem using data from my combobox to find the cell on the worksheet and write to the adjacent cell. Worksheet name is Fleet ComboBox Name is ComboBox7 TextBox name is TextBox3 CommandButton1 I would like to be able to take the text from ComboBox7, find this text in Col A of worksheet Fleet and write the Value of TextBox3 in the adjacent cell in Col B. when the CommandButton1 is pressed.
View Replies!
View Related
Find Cell Value In Another Workbook & Return Adjacent Cell
I am trying to complete these steps: Take a value from AK59 from the "EQF" tab in a file called "QuoteMaster.xls" Use that value to find match in column "A" in a Seperate workbook "ITMSTR.xls" on the "Sheet1" tab Offset to the right 1 cell of the found value, take back to the "QuoteMaster.xls" and put in cell "AN59" My code comes up with no errors, it opens the file and closes is as shown, but it doesn't copy the value over for whatever reason...
View Replies!
View Related
Find Cell, Copy Adjacent Cell Or Cells To Clipboard
Here’s what I want to do in VBA 1. Put a value in cell H1 (text and numbers) 2. Find a matching value in column A (starting in row 2), error message if the is not a match. 3. Copy the adjacent cell column B (rows vary) to the clip board. It would copy until it found the first blank row. I have attached a scaled down version of the spreadsheet, the one I use has 100's of codes. I know some VBA but not much. I searched the forum but could not find anything.
View Replies!
View Related
Automatic Time Stamp Cell Based On Adjacent Cell
I am looking to add a function to a current spreadsheet that writes the current date to a cell when another is updated. The sheets function is to have a user record when a particular action has been completed and then remind them after a given amount of time. The user is faced with an option to input a "1" to essentially "start the timer" and then the date that is automatically input by the code will be used to compare with the current date. I have tried the following on the Workbook_SheetChange sub If ActiveCell.Column = 9 And ActiveCell.Value <> 1 Then ActiveCell.Offset(-1, 2).Value = Date End If But cannot figure out how to eliminate user error. Let me explain. If a user enters a value (will only ever be a 1 to indicate "yes") into column 9 then the date appears in the correct place (two cells to the left) If however a user deletes a value then the date will update one cell too high!? I also thought of using a cell based if statement (if j7 = 1 then today() else "" but this only updates with todays value each time you open where I want the date to stay as the day the cell was updated. The date is going to be used as a way of working out when to change the original value of the cell in column 9 to "".
View Replies!
View Related
Merge Cell Values If Common Value In Adjacent Cell
I have data in two columns Column A has values with names like - John, Paul, Hena, Kumar, Paul, Hena, John in different rows Colum B has day values - eg , sun, mon, tue, wed , thu, fri and sat I want to insert a new column C with formula that returns 1. for every John, concatenate of all the day values adjacent to row of John eg "sunmonfri" Similarly for all the values in Column A, Column C should reflect the concatenate values of Column B.
View Replies!
View Related
Cell Colour Change From Result In Adjacent Cell
I am trying to make the colour of cell change depending on the result in an adjacent cell and have found and adapted the following: Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("x4:x202")) Is Nothing Then Select Case Target Case 11 icolor = 43 Case 21 icolor = 43 Case 31 icolor = 27 Case 41 icolor = 27................
View Replies!
View Related
Finding A Cell Adjacent To A Blank Cell
Ok, I've come close to the solution using the Match function, but somehow just can't quite nail it. What I need is a formula that will search down Column B until it finds the first blank cell. Then report what is directly to the left of that cell. So If the first blank cell is in $B$5, it will report what is in $A$5.
View Replies!
View Related
Cell Reference Or Adjacent Cell Return
I have a formula that searches through a couple columns, and based on some criteria on those columns, it returns a value. The criteria looks at the dates in a given week, finds the earliest date, and then the largest value for all entries of the same date. So, I do not know what this vale is going to be in advance. In any case, Let's call this value1. This is all working fine! Now, what I need to do is grab the value in an adjacent column to the left(same row) and add that to value1. Let's call this adjacent column value, value2. I was thinking that I could use the OFFSET function in this way, OFFSET(cell reference of value1, 0, -1) to return value2. But this requires that I can get a cell reference for Value1. I cannot find out how to get a cell reference. I saw some VBA code to search and return cell references, but it assumed that you know what value you are looking for, and I do not know that in advance.
View Replies!
View Related
Identify Active Cell And Use The Column To Add Formula To Another Cell
I have a range of unlocked cells (B5:S10) that users enter data in. This sum of this data is then charted. The formula (sum) in a cell equals zero even when there is no data entered by the user. This zero is then charted. I need to be able to plot the zeros if the user enters zeros but not plot the zero if the cells are blank. What I was attempting to do is to use the worksheet change event to add the formulas to a cell so that the chart does not plot the value until something was added. In my change event I need to know that a cell in the range (B5:S10) was changed and that if it was D7 (for example) that I need a formula enterd in D11 [=SUM(D5:D10)]. If it was I5 then the formula would have to go in I11 [=SUM(I5:I10)].
View Replies!
View Related
Return Maximum Of Active Cell & Arbitrary Value To Cell
I am looking for VBA that will add the value of the current active cell on the sheet to the value in cell F12. The maximum value of F12 cannot exceed 1000. So if the value in F12 = 950 and 100 is the value in the active cell the maximum value in F12 should show 1000, not 1050. It should do this on the click of a button.
View Replies!
View Related
Go To First Cell In List That Matches Contents Of Active Cell
I am just learning to use VBA and this may be the most simple task ever, but I can't figure it out. I've searched for all of the keywords I can think of, but can't find a solution... I have a list of names in a sheet. Other columns in this sheet contain data like amount charged, amount paid, etc. This sheet must be manually updated (because the other program won't export the information I need) periodically to ensure proper billing/payment application in the original software (all transactions are handled by other people that I don't trust). I sort the list so that the all names that are the same (ie John Doe) are together. A short example list looks like this: Jeremy Apple John Doe John Doe John Doe Jimmy Kravitz Jimmy Kravitz In updating my sheet, I set up a macro that will input todays date in one of the columns for all occurences of that name (so, every row that contains John Doe in column B, column V will have todays date in it). Currently, in order for my macro to work properly, I have to manually make the activecell the first occurrence of 'John Doe'. When I'm ready to update 'Jimmy Kravitz', I have to select the first occurrence of 'Jimmy Kravitz' and so on. Here's my question - Is there a way to use a VBA macro to find the first occurrence of 'John Doe' (and automatically 'know' which name I am updating)? Basically, I need a macro that will take the information that is in the cell in column B in the active row, find the first row that has that same name, and make that cell (column B) the active cell...
View Replies!
View Related
Add Active Cell Data To Cell By Clicking
I want to automatically put information from several cells into one cell, when these cells are clicked. Let's say that A1:J8 is the range were a cell can be clicked and if a cell is clicked in that range the value of these cells should be put in cell A9. If possible I want to avoid private subs in a worksheet. I preffer to have a macro running, so I can decide when to stop this routine.
View Replies!
View Related
Copy/Paste Cell To Named Range Named In Adjacent Cell
I have read post re this question but have not been able to answer my problem. I get the error message 'Application defined or object defined error' when running the code below. I should indicate the range counter currently indicated about 6,200 rows that this code will work on and the individual range names in the list of 6,200 rows are spread over at least 20 worksheets. The code appears to be running but after some time it stops on the line of code 'Range(Cells(i, 1).Value) = Cells(i, 2)'. Sub PopulateWithImportData() Dim counter As Integer counter = Sheets("Imported Data").Range("Counter") Application. ScreenUpdating = False Application.Calculation = xlCalculationManual Worksheets("imported data").Select Range("a1").Select i = 1 Do Until i = counter Range(Cells(i, 1).Value) = Cells(i, 2) i = i + 1 Loop
View Replies!
View Related
Name A Active Cell Which Ever Cell Is Activecell
I need to name a cell that is the active cell. The active cell can be any cell that is selected at the time I need to name it. I can get it to name the cell using the cell address, but I don't know how to change it to the active cell. Below is the code that I tried, but the syntax that I tried to say what cell is to be named simply isn't correct.
View Replies!
View Related
Adjacent Cell After Find
I am trying to calculate an adjacent cell to place an interger value after a find result. The following example, lets say location=$L$5, I need to calculate Cells(13,5).Value =n. Its for a page number and the cell I need is always to the right of the found cell.
View Replies!
View Related
Getting Data To Display From An Adjacent Cell
I am trying to analyse a whole heap of data generated by an image analysis package. The data is exported out in to excel, I then need to be able ot rearange it a bit and export it out to another program. I have attached a smaller version of the data as an example. The example data set only contains 3 image planes with ~100 entries for each. A full data set contains 96 planes and ~2000 entries each. So what i want to do. I want to setup a workbook i can dump this data into and have it spread everything out over 96 different sheets (1 for each plane). I can get it to do it for the first lot of data using a IF command like =IF('Object Data'!B:B=1,'Object Data'!D3,) But that only really works for the first plane, as the starting cell for plane 2 is always going to be different. So what i need is a formula that will say: look at the plane column, if it equals a certain value print out the value for the other cells in that row. Is that possible?
View Replies!
View Related
Clear Content Of Adjacent Cell
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 Replies!
View Related
How To Return Value Adjacent To Cell From Another Sheet?
I have one column of about 10 names (each of which is unique). The cell adjacent to these names is empty and I want to insert a value into this cell. The value I'd like to insert into this cell is located in another sheet attached to the same file. This sheet has the same layout, except the unique name has the value next to it and the order of the names is different. I'd like the formula to determine where the name from the first sheet matches the name from the second sheet. I'd like it to then display the data associated with that unique name in the first sheet, adjacent to the name in the blank cell.
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
Formula To Check Adjacent Cell Value, And Report.
Ok. I am creating item lists in excel for every product that my company sells. I am in need of a formula that does the following: I want the cell to check to see if there is a value in another cell, and if there is anything at all, i want to display the word Yes as the value, or leave the cell empty. So I want the cells in Column A to Check if the adjacent cells in Column B have a value. If the column b cells have a value I want the cells in Column A to display the word "Inventory Part". If there is no value I want the cell left blank.
View Replies!
View Related
Double Lookup And Pull Adjacent Cell
I need is spread across various rows. In a separate sheet I want to be able to lookup two identifiers and pull the number needed in the adjacent row. The first identifier is a 3 letter character, the second is CURREVO and I need the number to the right of CURREVO. My problem is CURREVO is not always in the same column, but in relation to the 3 letter character is always in the same row. A B C D E F GDMGCURREVO52011.25YTDREVO243085.00DTYCURREVO11892.50YTDREVO59783.50ECUCURREVI1943.00YTDREVI 5,541.25CURFRQI4.00EEGCURREVO32864.75YTDREVO205426.75EICCURREVO658761.26YTDREVO3507022.68EILCURREVO335741.70YTDREVO1720830.72ENTCURREVI161242.39YTDREVI638681.84CVLCURREVO796266.21YTDREVO4816890.98ENDCURREVI34479.19YTDREVI44074.54EYECURREVI11880.12YTDREVI 108,007.02(Null)MICCURREVO1098694.15YTDREVO5766072.54NEUCURREVI25251.90YTDREVI 158,236.60CURREVO207.00 i.e. in a seperate sheet, I want to pull NEU, in that row I want to find CURREVO and bring back the adjacent number = 207.00
View Replies!
View Related
Sumproduct Based On Adjacent Cell ID Code
I am trying to get the sumproduct of a column of amounts based on the adjacent cells ID code. For example column B will have codes 1FHD, 2FHS, 3FHG etc. these codes will be mixed otherwise I would have just did a sum of the cells. Column C would have dollar amounts. Sumproduct based on adjacent cell ID Code
View Replies!
View Related
Auto-Fill Formula When Adjacent Cell Changes
what is prompting this formula to auto fill down when a value in an adjacent cell is entered. In column A I enter an unformatted fax number. In column B I use the following Text Formula: "="("&LEFT(A20,3)&")"&MID(A20,4,3)&"-"&RIGHT(A20,4) Someone in my office copied the workbook and now any time a value is entered into the next available Column A cell. The cell in column B will automatically apply the above formula and format the fax number. The person who copied the workbook does not know how this is working nor does anyone else in the office. There are no macros embedded in this workbook so no event is firing to cause this. I have attached a small copy of the workbook called "Auto format1.xls" How to use: -Select cell B20 and not nothing is in it. -Enter any 9 digits in A20 and B20 somehow copies down the formula from B19. how to duplicate this.
View Replies!
View Related
Move Adjacent Cell Into Rows, Same Column
I need to create a repeatable macro/module to help me prepare a spreadsheet for import into Microsoft Access. In each row of the spreadsheet is various information including Order Number, Model, and Serial Number. The problem lies in the Serial Number column, which often contains several Serial Numbers delimited by commas. In order to serve my needs in Access, each Serial Number instance needs to become a new row ( record) wherein all of the information (except for the Serial Number) is the same. It is crucially important that all of the serial numbers are in the same column. (I tried using text to columns as a starting-off point, but seemed to dead end). Example: Order#1; Model1; Serial1, Serial2, Serial3 Needs to become: Order#1; Model1; Serial1 Order#1; Model1; Serial2 Order#1; Model1; Serial3
View Replies!
View Related
Combining Cells Based On Value Of Adjacent Cell.
I have been reading through trying to find a similar post but the nearest i could find was one dealing with numbers rather than text. This is how the workbook looks. Column A contains a list of numbers based on vehicle types. Column B contains a list of vehicle names. A B 1 Car, 1 Taxi, 2 Bus, 2 Lorry, 3 Skateboard, 3 Bike, 1 Motorcycle, What I am looking for is the best way to combine the text from column B depending on the value of column a. the finished results would then end up as: "Car,Taxi,Motorcycle,", or "Bus,Lorry," etc. This is just a simple list.. The one I am working with has around 2500 entries. I have been trying to do this using the CONCATENATE function and IF statement but it's just not working..... My head is ready to explode
View Replies!
View Related
Define Names As Adjacent Cell Values
I would Like to define each row in a spreadsheet to the value of the first cell in each row and I have no idea how to go about this. Code Customer _0888 Williams _0435 Smith So basiclally I would like to name the first row "_0888" being 2:2. But without doing each row manually via Insert --> Names --> Define..
View Replies!
View Related
Calculation Value Based On Month Name In Adjacent Cell
I have a spreadsheet spread over with dates in column A from September to December and I have a fuel rebate column as well. The fuel rebate is different for each month and is calculated as a certain amount of cents per dollar added to the total amount in Column B September: 26c October: 13.5c November: 43c December: 66c e.g. If there is an invoice for 10/9/07 (september) for $100, then the fuel rebate would be $100*.26 which equals $26 fuel rebate. I want to calculate column B (this is the column with the dollar amount in it), but I want the calculation to be different depending on what date is in column A, so if in column A the dated month is October, then the calculation would be something like B2*0.135. give me the calculation I am certain you don't need VBA for this right (just nested IF statements?)
View Replies!
View Related
|
TRACKER
