Place Value In Multiple Cells Based On Cell Variable
I'm trying to place the value of "X" in multiple cells based on a cell variable (i). For example, if (i) is evaluates to 35, the following code ...
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Auto Generate A Comment Box Based On A Cell Value That Has Variable Reference Cells
I have been given the following code and it works great. I now need to adapt it to the following scenario: In the attached sheet, the user has to select either, "Suburban" or "Squad" in row 5. If the user selects, "Suburban" I need this script to compare the values they enter in a given row to the value in column "B". If the user selects, "Squad" I need this script to compare the values they enter in a given row to the value in column "C". Look at row 48, for example. If the user enters, "Suburban" in cell D5 then the value they enter in cell D48 should equal "1". If it does not equal "1" then it should proceed with the adding of a comment. Conversely, if the user enters, "Squad" in cell D5 then the value the enter in cell D48 should equal "2". If it does not equal "2" then it should proceed with the adding of a comment.
View Replies!
View Related
Sum Based On Multiple Conditions & Variable Range
I have a rolling calendar worksheet that I need to create a formula to auto populate the sum of 8 weeks out, 4 and 8 weeks back based on any work week date I may plug in. So on the "summary page" based on a work week date, I need 8 weeks out, 4 and 8 weeks back projections to be auto summed for 10 different part numbers. The "13 week rolling" page will continue to be updated so that everyweek another week is added and the last week will fall off. (this part can be done manually if no function can delete the oldest column) When the weeks are added it must include the workweek in question. i.e. ww25 = (8weeks out) ww25:ww32 / (4weeks back) ww25:ww22 / (8weeks back) ww25:ww18. see example workbook attached. This has been driving me nuts for a few weeks now.
View Replies!
View Related
A Formula That Will Sum The Total Of Different Cells Based On An Initial Variable
Im trying to make a formula that lets you type in your current level, and then calculates how much XP you have remaning untill you reach the max level. The way the forumla would need to work would be something like this: (E10) Enter Current Level: (F10) 37 (which is my current level) F11 - would then add cells from C37 to C70 (calculating the total amount of XP required) and then display in (F11) Being able to say if, then is far to cpmplicated for me in excel. I'm just trying to make this for a bit of fun and am sure one of you talented people can easily do this.
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
Ranking Multiple Columns And Second Place
******** ******************** ************************************************************************>Microsoft Excel - Win_calc.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutQ21= QRSTUVWX21TickerLowestLowest*NameBOALEHBEARSecond*priceSecond*name22AT*US95/BEAR10210095**23ARW*US50/LEH605070**24BSX*US65/LEH666567**25CBS*US53/BEAR/MS555453**HVOL8* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR. This is my general template. I get prices from BOA, LEH AND BEAR on the four companies listed. Then I use the Min function to tell me the best price (I'm buying). Then I use some IF statements to tell me who's the "Lowest Name" (lowest price): =IF($G2=$C2,"/"&$G$1,"")&IF($H2=$C2,"/"&$H$1,"")&IF($I2=$C2,"/"&$I$1,"")&IF($J2=$C2,"/"&$J$1,"")&IF($K2=$C2,"/"&$K$1,"")&IF($L2=C2,"/"&L$1,"")&IF($M2=$C2,"/"&$M$1,"") It checks if a cell is = the lowest cell value, then returns the header. This formula refers to different columns than my displayed sheet because Im hiding so cells and I just copied this to another section of my sheet. There's probably a better way to do this but this is what I have do far. I use this for 50 company names and 5 or six dealer banks (BOA, LEH, etc) I'd like column that gives me the Second Place price and one that gives me the associated dealer (BOA, LEH).
View Replies!
View Related
Color Cells Based On Multiple Conditions Of Different Cells
I’m trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. I’ve searched and found similar forums here, but since I’m ignorant in VBA code, I couldn’t manage to make adjustments. So if you can help me with the code, I would really appreciate it! Condition and Results required would be: IF column C = “S” than on Column B = color cell light blue with white border IF column C = “P” than on Column B = color cell Green with white border IF column C = “A” than on Column B = color cell Yellow with white border IF column C = “L” than on Column B = color cell Red with white border IF column C = “C” than on Column B = color cell Dark Blue with white border IF column C = “ ” than on Column B = color cell white IF column C = “V” and has white background than on Column B = color cell white IF column C = “V” and has a Dark Grey background than on Column B = color cell Dark Grey I don’t know if the last two are feasible.
View Replies!
View Related
Spacing Multiple Variable Paragraphs In A Merged Cell
I am using excel as a base for formatting reports, and collating logical paragraphs, which I want to display in one massive merged cell on another worksheet. The paragraphs are different for each report, and I do not wish to manually enter them in each time. I also want them to display as seperate paragraphs on the page, but in the same cell. To throw a spanner in the works, the paragraphs are all of indeterminate length, otherwise, I could use different cells. If I combine them in the massive merged cell by using =cell1&cell2&cell3 then they will just appear merged as one whole huge paragraph. I want them to appear as separate paragraphs in the same big cell.
View Replies!
View Related
Assign Multiple Cell's Values To A Variable
Need exact VBA code syntax to assign a workbook (to be closed) sheet's cells J4 to J72 values to a variable called "ColJValues" to be assigned to another sheet (to be opened later in the macro). The values are all dates. Once the other workbook is opened later in the macro, need the exact syntax to assign the value in the above variable, "ColJValues", to it's cells J4 to J72.
View Replies!
View Related
Variable Cell Reference Based On Minimum Difference
I have a monitoring system that records a data point with a date/time stamp several times a day at random intervals. For each reading I want to calculate the change compared to the first reading that was more than 24 hours ago, which could be anywhere from 1 to 20 rows above the current one. Hence with the timestamp in col A and the value in col B, the formula in col C, for example cell C20, needs to read something like =B20-Bxyz, where xyz is the row number of the first reading that is more than 24 hours, i.e the first row xyz where A20-Axyz >1.
View Replies!
View Related
Select Range Based On Variable Cell Content
Based on if the value in col A contains the characters "TT" I want to select the range starting with this cell and ending at the end at the end of the row I'm using (.End(xlRight) and then merge these cells, change colors etc. And then looping this through a 'range' so that it only occurs where the values occur. I can amend various cells based on this idea, but am unable to identify the range and then merge the cells.
View Replies!
View Related
Macro To Copy And Place Data To Specific Place
I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.
View Replies!
View Related
VB Macro For Variable Cell Merge Into HTML Code Based On Criteria
Does anyone have a VB macro that will copy information from a cell (this will be variable based on criteria) and paste it into the middle of another cell that contains HTML code? The criteria for the copy/insert/paste is that the information in the cell that is to be copy/inserted must match up with the correct number (part number). I hope this makes sense. I have attached an example spreadsheet. (Pictures are worth 1000 words. ) Hope someone can help. I checked this site and couldn't find anything that was solved that matched the specifics.
View Replies!
View Related
Combing Data From Multiple Cells Based On Value
In column A i have values that may repeat throughout Column A, then next to the value in ColB, there is a sequence number 1,2, 3 for each of the same value in ColA. Then the Text in ColC that i need. What i Need is to be able to search ColA, Then get the correct Sequence number from ColB, and place the contents in ColC into ColD or into a new worksheet if necessary.
View Replies!
View Related
Count Cells Based On Multiple OR Criteria
I need to have multiple ranges count based on several criteria. The hitch is that needs to count if one or more criteria is met not all. I tried using a COUNTIFS however that seems to evaluate based on AND not OR. Is there a nested COUNTIFS or similar function that would work?
View Replies!
View Related
Lookup Function Based On Multiple Cells
in the lookup function listed below, it only produces a result for the first exact macth. well, what if the data i am searching on could result in multiple results? i would like advise on how to modify this formula to bring back the 2nd, 3rd, 4th, etc instance of a match in the lookup array. the original formula , i found on this site, allows you to combine multiple cells to create a unique reference, and then search in another table containing columns of those combined cells, and results in the intersection of a row and column and that is the data you want returned .... kind of like a hlookup, but you can use one or more cells to create the unique lookup, and bring back whatever column you want (where as a hlookup is limited to searching the left most column and bring back a column to the left) here is the formula: {=INDEX(data to retrieve,MATCH(cell1&cell2,table to find cell1 in&table to find cell2 in,0))} "data to retrieve" is an array of one column containing the data you want to retrieve "cell1&cell2" is a combination of cells to make the unique lookup (this could just be one cell if you wanted) "table to find cell1 in&table to find cell2 in" is a table array to look in to find the look up value
View Replies!
View Related
Caption Multiple Labels Based On Cells
I am displaying cells in textboxes depending on the results of a listbox selection. I was wondering if there is code for making the textbox names variable (so I could just loop through the offsets and textbox names) the code below works but I would like to make it more versatile and shorten it. There are other textboxes on the page. Sub detailfill() ....
View Replies!
View Related
Match Value Of 1 Cell To Multiple Cells And Give Multiple Values
Sheet 2 is the problem. I need to match cells e3:h3 whenver the dates change in in cells c5:c9 and then put there result of cells b5:b9 into the corresponding cells of e5:h9. Basically what I'm saying is that I want to keep a track of all previous pay amounts from each pay period. So when the next period changes the date it also copies the new pay amount to the corresponding date of the previous pay section. I have included a sample.
View Replies!
View Related
Code To Find A Certain Range And Place It In Cells
I have 26 ranges defined on my worksheet sheet3. Each range is 10 rows high and 4 columns wide. On my sheet1, I'd like to be able to go automatically to a specific range on sheet3 (I need to provide the choice to go to any of the 26 ranges), selected perhaps by a combobox. The ranges are building occupancy groups like A1, A2, B, E, R1, R2, etc. (26 of them). Then I'd like the user to look at each row in that particular range, select one, and then have the four values in that row placed in corresponding cells on sheet1. I have this set up now using a four column combobox, and it works just like I want, except that only the first column of data is displayed after a row is selected. If I could get those four cells of data as they appear in the combobox inserted on sheet1, I'd be fine. But I can't. So I'm looking for a work-around. Basically, my goal is to get a specific row of data (4 cells wide) from my named ranges copied into cells on sheet1. But I need to be able to get to the correct range automatically.
View Replies!
View Related
Taking The Value Of A Range Of Cells From One Workbook And Place Them In Another
I've been trying to learn how to write code in VBA. I've been learning for about 2 weeks now (trying to learn it for work) and heres my problem. I have a macro that will create a pivot table from raw data that I input. From this pivot table, I want to go to the last row in the pivot table (the row that takes grand totals of each column of data). I want to write a macro that will take those values (located in a workbook called AR age sorting) and place them in cells in another workbook (called AR aging analysis). Basically cells D6-D9 in workbook "AR aging analysis" should equal the values of the last row in my pivot table in workbook "AR age sorting" (last cell with data in columns J-M). I thought this would be easy but I've run into some issues. Heres the code I use: Sub aging_summary() Dim Sheet1 As Worksheet Dim Sheet2 As Worksheet Set Sheet1 = Sheets("AR age sorting") Set Sheet2 = Sheets("AR aging analysis") For n = 10 To 13 For M = 6 To 9 Sheet2.Range(Cells(M, 4), Cells(M, 4)). _ Value = Sheet1.Range(Cells(n, 1), Cells(n, 1)). _ End(x1Down).Value Next M Next n End Sub I don't want it to copy and paste the values since I just need the value, not all the extra formatting and what not that is incorporated in the pivot table. When I try to run this I get an error message, saying that there is an application-defined or user-defined error.
View Replies!
View Related
Place Tick & Cross In Cells
I am trying to use WINGDING FOnts on some forms. I am not having full success with them. Sometimes they seem to work, other times not at all, and then sometimes if the code is less that 127. I am programically creating the forms from a worksheet. The code used to create is as follows: Sub MakeUserForm() Dim TempForm As Object Dim NewButton As MSForms.commandbutton Dim NewLabel As MSForms.Label Dim NewTextBox As MSForms.TextBox Dim NewOptionButton As MSForms.OptionButton Dim NewCheckBox As MSForms.CheckBox Dim x As Integer Dim Line As Integer Dim MyScript(4) As String Dim BC As String 'This is to stop screen flashing while creating form Application.VBE.MainWindow.Visible = False Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3) 'Create the User Form With TempForm..............
View Replies!
View Related
Variable Locked Cells Dependant On Cell Change
I'm trying to make a spreadsheet more secure to prevent unintentional changes. What i'm looking to do is to lock all cells so no changescan be made unless the user clicks column A. Column A should remain unlocked and when the user clicks it i would like the entire row unlocked for editing (but only after column A is clicked) I will run a macro to lock the entire sheet again once the sheet closes.
View Replies!
View Related
Fill In The 3 Criteria Fields, Calculate The Result, Copy The Result, And Place It Into The Correct Place On The Table
I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie. | ID X | +------+------+------+---> | | 1999 | 2000 | 2001 | +------+------+------+------+---> | Jan | $100 | $250 | $300 | +------+------+------+------+---> | Feb | $200 | $300 | $200 | +------+------+------+------+---> | Mar | $300 | $250 | $100 | +------+------+------+------+---> | Sum | $600 | $800 | $600 | | +------+------+------+---> | ID Y | +------+------+------+---> | | 1999 | 2000 | 2001 | +------+------+------+------+---> | Jan | $100 | $250 | $300 | +------+------+------+------+---> | Feb | $200 | $300 | $200 | +------+------+------+------+---> | Mar | $300 | $250 | $100 | +------+------+------+------+---> | Sum | $600 | $800 | $600 | | +------+------+------+---> Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria: >= First Day of the Month <= Last Day of the Month = ID # The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
View Replies!
View Related
Multiple Conditional Format Conditions Changed Based On Another Cells Value?
A1:A6 have numeric values 1 to 6. I want the conditional format values to change Based on the value in Cell B2. If the value in B2 is "old", then I want these conditional format conditions for A1:A6. Numbers between 1-2=green text, 3-4=orange text and 5-6=red text. IF the value in B2 is "new" then I want the conditional format conditions in A1:A6 to change so 1=black text, 2-3=green text, 4-5=orange text and 6=red text. I know how to do multiple conditional formats but i have no clue how to change multiple conditions based on another cells value.
View Replies!
View Related
Consolidate Multiple Workbooks Based Only On Pre-defined Cells
I have multiple workbooks in a particular folder. Each workbook has only one worksheet named "Data". I need a VBA script that could open each file in the folder, copy only particular pre-defined cells that have been mentioned in the script, and then place it one below the other on a new sheet. Ex, if the pre-defined cell is A2, C6, and D7. I want the code to open all the sheets in the folder one by one, copy A2, C6, and D7 of each workbook opened, and paste it one below the other on the new workbook that is running the macro. As an add-on, it would also be great if the next column cud carry the file name of from which workbook the information was picked out. The result thus in the new workbook wud be like... A2's data | C6's data | D7's data | File Name Andrew Male Married march3.xls Peter Male Un-Married march4.xls Susan Female Divorced april2.xls
View Replies!
View Related
Multi-Criteria Sum Based On Multiple Column Cells
I have a large worksheet that lists employees and the hours they work each day in a pay period. For Example: Column A holds a date and Column E holds the number of hours worked for a paytype. So i want to create some function that sums the numbers in column E, but only those where column A holds a date. Then i want to return that number to a cell, say C20. I think I've managed to tell the macro which rows i want to return values from, but I can't figure out how to say "add these numbers together and put the total in C20." I'll check this frequently if clarification is needed.
View Replies!
View Related
Check Multiple Cells, Fire Macro Based On Result
I have three cells where a user will input data, in some cases (2T Weld Condition) they will only enter in B12 and C12, but in the case of a 3T weld they will also enter data in the D12 cell. I then use a formula to check for the thinnest material and that is entered into another cell with a formula, B14. I then need to check the value in B14 to verify if it is above zero, but below 0.65 (mm). If it is then I would like to have a message appear on the screen notifying the user that they are outside the acceptable range. I cannot figure out how to use the information in cell B14 because it is a formula and my code only works with a direct value. The code I am using works if I point to one of the three input cells, B12, C12 or D12. How do I use the information in B14 to work with the code below. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$12" Then If Target.Value < 0.65 Then Run "MyMacro" End If End If End Sub MyMacro loads a userform with buttons, etc.
View Replies!
View Related
AutoFilter Via Macro & Place Text In Visible Cells
I need a macro that can change the text in Field 46 from Criteria1 to something else, ie from "To be capped" to "Capitalised". This is my current Selection. AutoFilter Field:=39, Criteria1:="OPEN" 'STATUS Selection.AutoFilter Field:=46, Criteria1:="To be capped" 'TYPE Range("AT1").Select how to change the text only on the selection found by AutoFilter?
View Replies!
View Related
Select A Variable Based On A Variable
On my userform I have 20 comment icons (imported pictures), that when clicked need to bring up an InputBox for the user to add a comment, and store that comment in a Public variable specific to that comment which will later be written to the spreadsheet. To keep it simple, lets say I have two comment icons to click, one to add comments to the "Testing Completed?" field, and one to add to the "Sign-Off?" field. The first comment icon is named TestCompIcon, the second is SignOffIcon, and the public variables they write to are called TestCompComment and SignOffComment respectively. To avoid having to code the InputBox procedure for every comment icon on the userform, I was hoping that upon click, the icon would call a centralized routine that would establish the name of the variable that needs to be written based on the name of the icon comment that was clicked. Something like as follows:...............
View Replies!
View Related
How To See Where The 55th Place In A Cell Is
I use Excel 2002 - I have a column with Text in its cells. The cells usually range from 30 to 130 characters including spaces. I need to use word wrap. I would like to be able to see where the 55th character is ( Including spaces ) - but I also need to see the rest of the contents of the text in the cell. I have thought about coloring the first 55 character/spaces ? or inserting a character at space 56 ? Example below XXXX XXX XXX XX XXXXX XXX XX XXXX XXXX XXXX XXX XXX XXX XX~XXX X XXXX XX
View Replies!
View Related
Cell Value Based On Multiple Conditions
I need to create a macro that will calculate a value based on the contents of multiple cells. Looking at the example attached, if columns A and/or B (employee ID and name) are empty, then allowable OT (G) should be 0. If either have data, then if Stage (F) is CAN, G should be 20. If Stage is FAS or FAR, G should be 10. I'm not very savvy with IF statements, which is how I imagine this can be done, and I don't know if this would be easier to do as a macro or as a formula within G. Since the contents of the of the cells will be changing on a weekly basis, I'd prefer the formula to only be there if there is content on the line, so we don't have nulls showing.
View Replies!
View Related
Multiple If Else And Statements Based On Cell Value
I’m trying desperately to get this if statement to work If Workbooks("Master.xls"). Sheets("intro"). Range("A2") <> "" And _ Workbooks("Master.xls").Sheets("intro").Range("B2") = "" Then For Each vaFileName In .FoundFiles ProcessDataCrit1 vaFileName Next If Workbooks("Master.xls").Sheets("intro").Range("A2") <> "" And _ Workbooks("Master.xls").Sheets("intro").Range("B2") <> "" And _ Workbooks("Master.xls").Sheets("intro").Range("C2") = "" Then For Each vaFileName In .FoundFiles ProcessDataCrit2 vaFileName Next If Workbooks("Master.xls").Sheets("intro").Range("A2") <> "" And _ Workbooks("Master.xls").Sheets("intro").Range("B2") <> "" And _ .................................. Basically what it should do is to start the code ProcessDataCrit1 if there is a value in A2 but not in B2 or C2, start ProcessDataCrit2 if there is a value in A2 and B2 but not in C2 and start ProcessDataCrit3 if there is a value in all three cells. The formula is working for ProcessDataCrit1 as soon as there is a value in B2 the formula is not working.
View Replies!
View Related
VB Code To Place Formula In Cell
Upon user making selection from data validation list in Active Row Selection & Column A : i would like to do the following (is possible) ,,,,,,,,,,,,,,, Place formula in Active Cell Row & Column S =MT4|BID! (followed by cell A1 but replace the "/" with "") followed by "m" I have tried several combinations but with no success. Also - where would i place this code so that it automatically updates the formula in Column S as soon as the user changes selection in Cell A?
View Replies!
View Related
Place Date In Cell H6 Automatically
I have the following Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A2:A100")) Is Nothing Then With Target(1, 2) .Value = Date .EntireColumn.AutoFit End With End If End Sub I just want the date to appear in cell H6 autmatically and the time to appear in H7.
View Replies!
View Related
Populate Cell Based On Multiple Criteria
I think the best thing is to take a look at the attached, then read what I'm looking for, as it would make more sense... That being said: what I am looking to do is change cell L3 based on new criteria in cell M3. Right now if B3=Stationery World then L3=Stationery. What I then want to happen is if E3=Toner then M3=67200 which then has L3=Equipment Consumables and NOT stationery.
View Replies!
View Related
|
TRACKER
