I didn't find any solution yet, concerning reorganization of cells in an excel sheet. To sum up, I created a macro that is able to reorganize a 2*5 table (E4:F8) into a row (H4:Q4), but I would like to apply this macro to any 2*5 table that I select (not only E4:F8, but also E9:F13 for example...) because I have a sheet with hundreds of these 2*5 tables. Here is the script of my macro, how can I adapt it so that it works on cells I select manually and not only on the E4:F8 cells?
View Complete Thread with Replies
Related Forum Messages:
Is there any easy method to tranform the table from Tabel 1 to 2?
Import And Reorganize Text File
I have this text file that gets sent to me, and i want to be able to re-organize the horible set up (see Import tab) into the proper format (see Reorganized tab) using Macros. There are many variables, such as the number of part numbers, the number of problems can all vary. As well as the number of claims. I don't even know where to begin. See attached file as a sample.
Reorganize Spreadsheet To Comply With Vendors Inventory System
Basically We have a column with our part numbers and a column with the vehicle applications which contains the years, make, model, and engine.
Most of our part numbers have multiple applications that they work for.
The vendors system only uploads unique part numbers, so I need to find an easy way, if posable, to combine all the applications that use the same part number in one cell next to it's specific part number.
I don't know if I should be messing with formulas, pivot tables, VBA...?
Date Cells In Project Plan To Change Based On Other Cells Including Other Date Cells
This is a project plan with tasks and dates. Column A is the activity number. (Example 1, 2, 3" etc). Column B is the task (Ex. "Complete Report"). Column C is number of days required to complete the task. Column D is the dependency column. (Ex. Cell D2 =1 in other words Task 2 is dependent on task 1). Column E is the date.
I would like to have a seperate start date cell and a go live date cell.
The objective is to enter a start date, and have each column E date increase based on the number of days entered in Column C. If a task is dependent on another and I change the number of days in Column C I need the dependent task to change the same amount of days.
Color Cells Based On Adjacent Cells Being Empty & Add Text
I’m trying to make my life a bit easier, by adding a few macros and formulas to the spreadsheet (Everything was done completely manually before I got here!!!).
What I would like to do is take two columns, which contain a start and end time for work shifts, and colour them GREEN once I have entered a name in the Worker column (Along side the two with the time), and also to fill a cell with a Yes or a No. I’m aware of auto conditioning, and I’ve tried to have a play to get this to work, but I just can’t work it out.
I have posted a link to an image which shows what I want. I hope I've explained it well enough!
Forcing Cells To ALWAYS Find MIN And MAXIMUM Values From A Specific Range Of Cells
I'm working on a project for my company. We make plastic tanks and for quality control we want to start recording the thickness of the tanks in different areas/zones of each tank.
Attached to this message is an Excel sheet that I've been working on. From "Sheet 1", it records inputted thicknesses into WorkSheet "1098". On the top of "1098", it shows all of the recordings, and just below that are the "10 Most Recent Entries".
Right below the "10 Most Recent Entries", there are formulas to calculate the Min and Max Values. Whenever a new entry is recorded, the selected cells for the Min and Max formulas change. Is there a way to force the cells to always stay the same?
SumIf Function To Sum Cells When Other Cells Begin With Certain Characters
I want to use the SumIf function to sum cells when other cells begin with certain characters.
I've toyed with a few ideas of how this could work, but i don't know how to specify that the cells need to begin with certain characters. The cells that would be the criteria and the ones that would be summed come out of an Oracle database (and i have no control over the way they're pulled out - yet) so the beginning characters are connected to extremely unique information, so i dont want that to be included in the if part, for obvious reasons.
2003 - Merging Four Adjacent Cells With Text And/or Blank Cells
I am trying to merge text, in four adjacent cells in the same row (say cells A1,A2,A3 and A4), into a single cell (say cell A5).
I would like a comma or full stop and then a space between each item merged (cell A5).
The text to be merged may appear in any one of the four cells (cells A1 to A4). Those cells without text are blank.
Only where all 4 cells are blank, will I need cell 'A5' to indicate this.
The formula needs to be relative as I will need to copy the formula down the spreadsheet so that it applies to additional rows.
Lock Cells Based On Value Of Other Cells - Code Simplification Query
I'm using the code below to lock certain cells depending on the value of other cells. The code below deals with one line of my spreadsheet only and as the spreadsheet comprises 38 data entry rows I've repeated this code 38 times in the worksheet module with the appropriate changes to row numbers.
It works, but causes much screen flickering and "thinking". I'm new to vba so no doubt I've made this code too extensive or lengthy or whatever (or just plain wrong).
Can anyone assist with suggestions on how to simplify the code and/or help with code to handle all 38 lines without repeating the routine 38 times?
Fill Empty Column Cells With Reference To Adjacent Cells
I would like a macro that when run, finds empty cells in a column within the used range and fills them with the same formula in the other cells in the same column but relative to the row.
I have a basic understanding of VBA so if someone can set me on the right track i'll have a go myself as i appreciate this would take a while to write out from scratch.
Lock Empty Cells In Range Based On Other Cells Content
I am trying to lock the unused cells in 32, 2 column by 7 row named ranges, based on whether or not two cells, above each range are equal or less than each other. In other words while one of the cells is less than or equal to the second cell all cells in the range below should be unlocked, as soon as that condition is no longer true the blank cells need to be locked.
I am trying to use this in the Workbook_Sheetcalculate so that the macro will run automatically.
Count Unique Cells Based On Ajacent Cells Having Values
I have the following data in 1 of the tabs.
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:
Can this be achieved through a formula?
Copy Cells Where Range Address Resides In Cells Of Another Sheet
Tried to write a Macro to Range Cells in a sheet, setting the range values from a another cell. I have encounted a Run-Time error as select method of Range class failed. Below is the Macro.
Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Set r1 = Range("J4")
Set r2 = Range("K4")
Set myMultiAreaRange = Union(r1, r2)
Have the Ranges been set incorrectly
Copy Certain Cells Upon A Row Onto Specific Cells On Another Tab
I am seeking a formula which can be “dragged” which will copy certain cells upon a row onto specific cells on another tab, when i drag at present it skips rows from tab 1?
Tab1 B12 to Tab2 C8 & Tab1 E12 to Tab2 C12 & several others
Tab1 B13 to Tab2 K8& Tab1 E12 to Tab2 K2 & several others
Tab1 B14to Tab2 S8& Tab1 E12 to Tab2 S2 & several others
Example spreadsheet attached
Simple for all you Excel genius’s, I am only just getting to grips with excel & cant wait to know as much as all of you!
Clear Contents Of All Unlocked Cells (many Are Merged Cells
I am looking for a code that will clear all of my unlocked cell in sheet 1. That is not a problem but since many of the cells are merged I know it keeps throwing me an error saying cannot change contents of merged cells or something like that. Does anyone know how to get around this without unmerging the cells. I saw a code to unmerge all of the cells on a sheet but I really don't want to do this as I already have worked around most of my problems with the merged cells.
Adding Up Values In Nth Cells With Text In The Cells In Between
i have a problem adding cells every 5th cell for example i have
this adds every 5th row in this range and it does it right as long as there is no text in the cells in between
it works fine when it looks like this
but it does not work when it looks like this
when this happens it appears as #VALUE, meaning an error but i don't understand why if i'm just telling it to look at every 5th row and those specific rows have no text in them
i have a spreadsheet with text in between the cells that need to be added so i need a formula that only adds up the cells with numbers on it, to be specific the numbers are either ones or zeroes.....
Find Value In Cells, Merge Found Cells To One Column
I need to mine a huge spreadsheet of about 30,000 cells and make columns from specific words. As in.
I need to search for all words "table" even if the word is "My_table" or "aTable" and suck them into one column. I have to do this one several words.
Let me know if you have the solution. I am trying to write something but I am unsure of the syntax at this level.
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.
Show Contents Of Vertical Cells In Horizontal Cells
I am referring to the post made by NBVC at http://www.excelforum.com/excel-gene...xcel-help.html, Try: =IF($A1="",INDIRECT("A"&ROW()-ROW($A$2)+COLUMN(B1)),"") in B2, copied across and down
I have a similar case but there are no empty rows between lists, what would be the formula to get the same results? (The transposed address should appear next to each first line of each block).
Earlier, I posted at http://www.excelforum.com/excel-gene...-one-cell.html and I got brilliant answers, I also would like to get benefit of the above mentioned formula but in cases when there are no empty rows between lists.
Shading Cells If Values Exist In Same Cells On Another Sheet
We have a simple holidays workbook, with seperate sheet tabs for each member of staff in the department to note down there holidays in a calendar.
The 1st row (from B1 and onwards) contains the day numbers of the month, ie. 1 - 31
The 1st column (A2 downwards) shows the 12 months of the year.
The holidays are then marked in the relevant cell matching the dates required. The codes we use are F (full day), A (morning), and P (afternoon). A countif adds up all the occurences of the various codes and totals the ammount of used/remaining days.
Hopefully that drew a picture.
The problem is, each sheet only shows the holidays marked down for that person. There is no way of checking for a "clash" (ie. 1 member of staff off on the same day as another) without flicking to the other sheets and checking yourself.
how can I get a cell shading to automatically show up on the sheets, if there is a holiday noted down (ie a value, A, P or F) in one of the other sheets?
Formatting Cells Dependent On Other Cells Relation To That Cell.
i have just read the thread started by scabertrain regarding "if" conditonal formating, but mines slightly different and i cant make head nor tale of the fomula shown.
So... Monthly budgeting... using a projected outgoing (A1) and an actual outgoing (A2) i want the colour "A2" to change depending on whether i have spent over the budgeted amount, green being underspent, orange for on target and red for over spending....
I predicted (A1) that i was going to spend £50 on a night out, but my actual spending (A2) was £100, i would like A2 to turn to red.
Highlighting A Range Of Cells Dependant On Data In Any Of Those Cells
On the attached sheet I am trying to indicate that enough data has been entered by highlighting the entire column. Any 3 cells in Rows 7-13 and 18-36 will be filled in with any of the values from cells O6-O11 (hidden). When the total = 6 i would like to highlight the entire column to indicate it has been completed.
The aim is two fold: to ensure that the correct number of points is allocated in each race, and secondly to indicate which column is the next to be filled in (as human error sometimes misses the column and adds the points to either the previous or next columns). You could call it idiot proofing the sheet. At present i have the cells in Row 37 conditional formatted to show this, but would much prefer the entire column to highlight.
Count # Of Cells Minus Red Cells VBA
I have tried to figure out how to count all cells in a column and subtract any cells that contain characters with a red font. I'll attach an example. I need code that will work in VBA so that in the case that the column does not contain any cells with red numbers it won't come up with a debug error.
In excel I normally do this manually with this formula: =count("") I normally highlight all cells except the red ones. See attachment for example.
Also, the # of rows is never the same. Some files will be 100 rows, some will be 1000, depending on the store's file we get. so the code would probably have to contain something like .lastactiverow or something.
Calculating Sum Of Cells Only Where Adjacent Cells Are Blank
I am constantly editing this (we currently have over 100 accounts) and therefore the totals are changing.I have a formula for Total but I need formulas for the other two, based on when the cells in columns F and J are blank or have dates in them: For active, the total is the sum of all numbers in column M but only when there is a date in column F and a BLANK in column J. For yet to enter, the total is the sum of all the numbers in column M but only when both column F and column J are blank. At the moment, my accounts run from row 6 to row 142, with the first line of totals in row 145, however this is constantly expanding.
Fill Cells Based On Corresponding Cells Matching 3 Criteria
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
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.
Join Cells & Compare To Single Cells
i've put this previously on here with no results. There is a formula already in there though does not work because what I want to achieve is to have Column A & Column C to Match Column E & Column F, to Return the Value YES or NO. I’ve Highlighted in ROW 947 one that should Say NO yet Says YES.
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.
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)
Extracting Data From Cells In A Row Based On Other Cells
Please check the attached workbook. Sheet "Data" has some bank account data, and whether the dues are paid or pending.
What I need is, I need to extract only the data of Column A and B into a new worksheet, only if G is in the Pending status.
I don't want to use a macro, nor a pivot, nor autofilter. I need to do it through formulas. I have seen this been done before, but can't figure it out. The max number of pending accounts wont be more than 10, so need to just pull the formula down 10 rows. I have already entered the data in the second sheet that would be the result.
This second sheet is sort of a supervisor sheet, so that whenever they want they can open it to see the number of Pending accounts.
Splitting Separated String Data In Cells To New Cells
I have pricelists tables with 300 and more rows and about 50 columns. Three letter codes in first column cells are in strings separated by separator (,), I need to split this strings so that each three letter code would be in its own cell in first column, at same time all other cells in same row as original string data should be copied to rows near appropriate 3 letter codes. In attached xls file I have made small example how data looks before and how it should look after applying macro. Table should not move to other place on worksheet or on new worksheet, in attached example I moved it just to show what kind of form result should have.
Highlight/Flag Formula Cells Referencing Other Cells
I am trying to write a macro which highlights all the cells with any reference to other cell. The macro should highlight a cell with formula like = 3 * A25 but not highlight a cell with following formula. = 3 * 0.4535566. 'Hasformula' property is not useful here as it can't distinguish between above examples.
Auto-populate Cells With Data From Other Cells
My sheet goes from monday to friday on the coulombs. On the rows i have various data sections for each day. I would like the data on the last day (any day after monday) that there is data for a sheet to input that data into the monday slot when the "master date" is changed on the cheese sheet. The idea here being that the script will take the last entered data for a given sheet, and put it into the monday coulomb on the same sheet when the date is changed. This is kind of hard to explain so if you need clarification let me know. Attached is the sheet i'm working with.
Compare Values From Cells To Shapes To Cells
In the attached excel file I have two macros that create hyperlinks:
1. BalloonToNumber - Creates hyperlinks from "Oval" shapes on sheet "Op60_1" to sheet1 (column D).
2. NumberToBalloon - Creates hyperlink scheme from sheet1 (column D) to sheet "Op60_1" "Oval" shapes.
In Q #1 my macro seems to skip some shapes on sheet "OP60_1" and I simply can not figure out why.
In Q #2 my macro creates hyperlinks to shapes that do not exist and hyperlinks to numbers that are not a "100%" match, IE: 182 and 82 would share the same hyperlink?
If you open the workbook and use the hyperlinks on sheet1 they will take you to sheet "Op60_1" and shape hyperlinks from sheet "Op60_1" take you to sheet1. On sheet "Op60_1" there are some red arrows indicating the shapes that get skipped when running the "BalloonToNumber" macro.
Shape color changes as the hyperlinks are selected from sheet1.....