Bold On Copying Cells Which Has A Forumula
when he copies some data across a worksheet - the new data has a bold format, so he has to unbold everytime.
I can't see any obvious reason why this is happening.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Merging Cols Changes Bold To Non-bold Or Non-bold To Bold
I am merging columns. Col B has all text in bold. Col C has all text in regular font. When I use ASAP to merge, the merged column comes out all bold. When I use a formula to merge the merged column comes out all regular. What I want is for the merged column to contain the bold text from Col B in bold, and the regular text from Col C in regular. What I want is... This is for service plan B: Take all data and transer to new server. What I am getting is... This is for service plan B:Take all data and transer to new server. OR This is for service plan B:Take all data and transer to new server.
View Replies!
View Related
Macro To Print Active Area Excluding Cells With Forumula That Doesn't Return Value
Trying to put together a macro that looks down active sheet for all cells that contain a value, sets a print area and then prints ! Is this possible? FYG, I have a column that run from 3 - 2000, which contains a formula, which may produce a value depending on corresponding cells. I used this code from a post on a similar topic, but excel is complaining code in bold Private Sub Print_Area_Click() Dim lastCell As Range Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0) Do Until Application.Count(lastCell.EntireRow) 0 Set lastCell = lastCell.Offset(-1, 0) Loop ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address End Sub
View Replies!
View Related
Split Cells By Bold Text
I have a sheet full of names, addresses and phone numbers in one cell like this Albert Young 16 teststreer 12C..................7888-6338 The name Albert Young is bold. The thing is I would like to split the cell putting the names in another colomn. Then it would be ideal to split the addresses and phone numbers as well. But I have no idea how to split any of these. I scanned this from a phone book, the dots are different on each row.
View Replies!
View Related
User Define Function To Count Bold Cells
I currently have this macro that helps me SUM all the cells that are bold. Public Function SumBold(Rng As Range) Dim Total As Double Dim Cell As Object Application.Volatile For Each Cell In Rng If Cell.Font.Bold Then Total = Total + Cell.Value Next SumBold = Total End Function I want to have the same kind of Macro, but that could COUNT the number of cells that are Bold.
View Replies!
View Related
And/if/or Forumula
actual revenue planned revenue 5 4 125% 3 4 75% -5 -3 -66% -3 -5 60% I need to get to those percentages (or close) the tricky part is when the numbers become negative. the forumla is actual revenue over planned revenue. If the actual revenue is below 0 and less than the planned revenue number than the attainment % should be a negative. However, if the actual revenue is below 0, BUT LESS THAN PLANNED revenue the percentage than becomes positive.
View Replies!
View Related
Rank If Forumula
Trying to figure out how to write a formula to rank values in B if the values in A are the same? I tried rank(if(A2:A17=A2,B2:B17))but this did not seem to work.
View Replies!
View Related
Replace A Forumula With A Vb Function
I've always used event driven functions - ie. user clicks a button and my function gets called. What I want to do now is replace a HUGE formula that is impossible to debug with a function. So the idea is, any time the user changes any cell, the function should be called and update a certain cell with a new value.
View Replies!
View Related
Use Forumula As Criteria For Sumifs?
I'm trying to sort out an accounts receivable ageing into various age categories For example, for a customer with 3 months credit term, i've used the formula =SUMIFS($C3:$AA3,$C$1:$AA$1,">=4",$C$1:$AA$1,"<=9") to sum up amounts that are 1 to 6 months past due. For the next age category 7 to 12 months past due i've used =SUMIFS($C3:$AA3,$C$1:$AA$1,">=10",$C$1:$AA$1,"<=15"). As my customers have different credit terms (30 days, 60 days etc), i've to adjust the above formula accordingly e.g. instead of ">=4", I'll have to change the criterion to ">=7". This is time consuming as i've got a long customer list.
View Replies!
View Related
Adding A Condition To An IF And Vlookup Forumula
Title: How do I add a condition to an IF and Vlookup Formula: I need to edit a formula to include a condition. I attached an excel file for ease of explaining the problem. I want to say if $E$5 has “A to B” then F5 will automatically have BOR, but if E5 has anything else then the Vlookup formula applies. I was thinking something along this formula: =IF(AND($E$5<>”A to B”,ISBLANK($B5),"",VLOOKUP($B5,'Drop Down'!$D$1:$E$500,2,FALSE))) I know I need to add “BOR” in the formula above somewhere Where am I going wrong?
View Replies!
View Related
Format Date With Other Text In Same Forumula
I am trying to display both text and a date in the same line. Currently I have something like ="The Date Is " & A1. where A1 is a cell with a date formatted in a preferred format. However, once I call reference to the cell, excel re-formats it into the serial date, so it displays something like. The Date Is 40220 instead of something like: The Date is 2/11/2010. Does anyone know how to format the number back into a date form? Just going through the format menus and setting it to date does not change it.
View Replies!
View Related
Copy Cells From Column To Adjacent Column If Bold Or Empty
I have a worksheet on which the data is already grouped. At the top of each group is a row that contains only the group name. Since the rest of that row is blank, I want to use a blank cell on that row as a reference, then copy the group name to a newly created column, then fill that column down to the next group. The goal is to create a column that contains the group name, rather than just having the group name as a " header" at the top of each group.
View Replies!
View Related
Runtime Error 1004 :: User Create Forumula In A Combo Box
The app. that I have been developing requires the user to create a formula in a combobox in a userform that I have setup for them. They have a list of variables that they can choose from, which they are allowed to use in their formula creation. A typical formula may look like this: =SUM(25*DistFromBack) where DistFromBack = 4 I then take their formula and place it out on the sheet which returns me a value of (in this case) 100. Now this works fine until someone wrote a formula that looked like this: =SUM((25*DistFromBack) Now with the double brackets at the front it creates a formula that Excel cant deal with and gives me a 'Runtime error 1004'. I have tried to trap this error (On Error Goto .....) but unsucessful. I would really like to have a check when the user types an erronous formula, but dont really know how to go about it.
View Replies!
View Related
Copying Cells
I have a worksheet with columns as follows: col A Name col B Category Col C Subcategory In column A every row has a name, but in col b and c only the first row of a category or sub are popluated, for example: Name CAT Sub a 1 1 b c 2 2 d 3 e f 3 4 g h As I work with many of these spreadsheets, they are all different in respect to number of products(name) and number of CAT and SUBCAT.
View Replies!
View Related
Copying Cells To Another Sheet
I would like to be able to copy a group of cells from sheet 1 to sheet 2 or 3 based on a value in another cell on sheet 1 eg sheet 1 If Cell A1 = 24, A2=London Road (this make up an address), A5= omit If A5 = omit I would like the address copied to sheet 2 If A5 = Complete I would like it copied to sheet 3 I have 1600 address to go through.
View Replies!
View Related
Copying Blank Cells ..
i am trying to copy specific cells for mutiple worksheets and past them into a summary page. i can get that to work but not all the cells on all the sheets are populated and i cant get the blank cells to be pasted to the summary sheet so that each sheet has 31 entries.
View Replies!
View Related
Copying Cells With A Macro
I would like to copy a range of cells with a marcro, for example copying range C3:C15 over to D3:D15 and when I run the macro again I want range D3:D15 to copy to E3:E15 and from there to copy E3:E15 to F3:F15. I want to keep that going to the next and the next with the same macro. But it just keeps copying the the original range which I recorded in the macro, but I want it to keep going. If somebody can help me with that, I would sure appreciate hearing from you.
View Replies!
View Related
Copying Cells Using VBA
I need help with copying some data from one sheet to the other, but I am really bad at explaining this. Can I send the excel sheet to someone and then explain it? I think that will be easier, cuz Ive tried explaining it on another forum and nobody understood what i was saying without being able to see the sheet.
View Replies!
View Related
VBA Copying Cells
I have a list of names in column a, and in column b either a "0" or a "1" as part of a longer piece of code I need to be able to copy the names that have a "1" next to them over to column c
View Replies!
View Related
Copying Cells Between Workbooks
I am creating a macro to copy data from one workbook that is emailed to me to another workbook that contains the actual macro, I will be using the second workbook to import the data into an application. What I need is to be able to copy text in cells say D1, D3, D6 etc located in workbook1 to cells say A1, A2, A3 etc in workbook2 without having the macro go back and forth each time to copy the cells one at a time. Also is there a macro that will recognize an already open workbook that I can use to copy the data
View Replies!
View Related
Copying Coloured Cells
managed to count cells based on colour, however if the colour is variable due to conditional formatting then the UDF doesnt work. So my next thought is just to copy the colours into another column next to it and then get the UDF to count those colours. however copying just the colours is not as easy as seems. when i use format painter the colour all comes out the same (once again i am presuming this is due to conditional formatting). the macros etc seem to complicated for this is there a simple thing i am missing?
View Replies!
View Related
Copying All Cells On Sheet To Value Only
I am trying to do a loop to go through all the sheets in a workbook (11 ina all) that will copy all the cells and paste special value. below is the code i am trying to use and it is giving an error. For Each sheet In ActiveWorkbook.Sheets Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Next sheet
View Replies!
View Related
Copying Cells For E-mail
I'm using Excel 2003, Windows XP, and Microsoft Outlook. I'm trying to copy some cells, which include merged cells, from Excel into a new e-mail using Outlook (the output of some calculations), but every time I do this, Outlook seems to unmerge the cells. When I paste into Word, the cells remain merged; I can then copy/paste from Word to Outlook with no unmerging occurring. what's going on and/or how to resolve this issue so I can copy things into Outlook straight from Excel?
View Replies!
View Related
Loop Through Cells For Copying
When I run the following code, I get "Run-time error '1004': Application-defined or object-defined error"...most of the time. Sometimes it will work when I don't pass any variables to the function, and insert a raw number for the variable "counter". But it still gives me the error most of the time. Sub WriteLatLongs(counter) 'counter Dim j As Integer Dim toprow As Integer Dim bottomrow As Integer For j = 1 To counter toprow = (((j - 1) * 250) + 1) bottomrow = (j * 250) Sheets("PixelCoord"). Range(Cells(toprow, 2), Cells(bottomrow, 3)).Select Selection.Copy Sheets(j).Range("B7").PasteSpecial Paste:=xlValues, Transpose:=True Next j Sheets("Sheet1").Select ActiveWindow.SelectedSheets.Delete Sheets("PixelCoords").Select ActiveWindow.SelectedSheets.Delete End Sub The error highlights the following line: Sheets("PixelCoord").Range(Cells(toprow, 2), Cells(bottomrow, 3)).Select.
View Replies!
View Related
Copying Range Of Cells From One Workbook To Another
I'm looking for an algorithm that does the following: 1. Open one-by-one a number of excel workbooks in a directory and copy their .UsedRange (i.e. all sheet content in each of the workbooks). 2. Paste append one-by-one each of the workbooks .UsedRange of data into a master workbook that is already open (i.e. active workbook). Note that the source workbooks and target master workbook contain the same format of data. So a simple copy and paste of the .UsedRange is apt. Basically I need each .UsedRange of an opened workbook to be appended to the data from previously opened workbook in the master workbook already opened.........
View Replies!
View Related
UDF Causes #value Error Whenever Copying Cells
1) The following code works well except for 1 wierd circumstance. This UDF code resides in a Cell in workbook 'A'. If I have another workbook open (call it workbook 'B'), and I do ANYTHING in workbook 'B' (like copy a cell to another cell within workbook 'B', or perform a calculation within workbook 'B', or even copy a cell from 'A' to 'B'), then the cell that this UDF resides in (workboook 'A') will change from a good number to a #VALUE error. If I work ONLY within 'A', then this UDF functions properly (giving a good result). 2) If I hit the F9 button (i.e. re-calc), with workbook 'A' active, then the #VALUE goes away, and the good number comes back. To clarify, all workbooks have AUTO CALC enabled. I guess the 'Application.Volatile' line causes this code to run when I hit the F9 button (thereby clearing the #VALUE error). If I hit F9 with 'B' active, then the #VALUE error (back in 'A') goes NOT clear. 3) NOTE #1: If I put a BREAKPOINT inside this code, and then repeat a "copy from cell to cell" within workbook 'B', I find that the following code DOES NOT execute, as it does NOT "stop" the code at the BREAKPOINT line. But, when I hit F9, then it DOES stop at the BREAKPOINT. 4) NOTE #2: If I "comment out" the Application.Volatile line, it still does the SAME thing. Except worse, then when I hit F9, it doesn't correct the #VALUE error. I really need the Application.Volatile step with this code, as these functions will not automatically update if their referenced cells are changed. 5) NOTE #3: The variables Row_Num & Column_Num are declared at the Module Level. If I use unique variables and declare these at the Procedure Level, it makes NO difference. I also tried putting a "DoEvents" line after Application.Volatile, but NO difference. 6) It seems I need this code to execute whenever there is a "change" on ANY workbook. Or else, do something to keep these cell values from changing anytime there is "action" on another workbook. Or, is there better code to do the samething? ... Do you have any CLUE how to fix this SNAG?
View Replies!
View Related
Copying Values Between Symbols To (different) Cells
In a MSquery on a ODBC source I am have a field in which there are several values in one (alfanumeric) field seperated by an alfa key. Example: A1 = &&&1000,80&&&500,35&&&11810 ABC&&& The values between the &&& have to be copied into other cells so that I can use them for calculations Example: B1 = 1000,80 C1 = 500,35 D1 = 11810 ABC
View Replies!
View Related
Copying Cells From 194 Workbooks
I have 194 Workbooks that all contain data on the first sheet only. I need to go into each sheet and pull the information from cell B6 and F21. That information needs to be copied to a new workbook into columns A and B so when I am done I have 194 lines in two columns. Each of the workbooks I am pulling from have the info I need in Sheet1. Is this possible, or do I need to do a lot of copying and pasting? If it is possible, how do I do it?
View Replies!
View Related
Copying Text To Empty Cells
I currently have created a form for the user to fill in details on my sheet. My idea is that when the user clicks a button, the macro will take that piece of text and populate my other sheet into the correct column. Therefore the task I am trying to resolve is copy the text into a field and then if the user clicks copy again, the text will fill into the field below as it is empty and so forth until the user has finished entering values. I have the following setps in pseudocode below but having trouble converting it to a macro!
View Replies!
View Related
Copying Data Down Into Blank Cells...
I have a table with over 12,000 rows in it. In one column I have activity and the next a name. A B Walk John Run Harry Sleep John *blank* Harry Eat Percy *blank* John *blank* Harry Reading Tom So *blank is completey blank and that means Harry also put time to sleeping, and again John and Harry both put time to eating. How can I make the blank cells auto populate with the data from the entry above it?
View Replies!
View Related
Copying Cells From Another Workbook With VBA...
I just need to know how to reference another workbook in order to copy values from cells on it to my own version of it. It's 18 pages, and I need to pull certain columns cell values from certain sheets (these columns are all fixed, no fancy statements required). I'm unfamiliar with the structure required to refer to another workbook. All I need to do is say "This workbook-Sheet1!D2:D20 = Workbook"Feb"-Sheet1!D2:D20", and so on, throughout the sheets. I did look for solutions to this, but all of the answers are mixed in with complex statements for other simultaneous calculations... I find myself unable to pick out the small piece which I need from the mash of code I'm unfamiliar with.
View Replies!
View Related
Copying Cells Into Adjacents Across A Row
I have a spreadsheet like that attached where there are titles down Column A, and every second column from B on there are data entries I wish to copy to the other alternate columns. For example B1:B2 into C1:C2, D1:D2 into E1:E2. I have an example of how it is and how I wish it to be in my attached spreadsheet. This is my penultimate dilemma in the spreadsheet I've been working on for a few days, excited to nearly be there!
View Replies!
View Related
Copying Values Of Merged Cells
I have a pair of workbooks in which one calculates a number of values and I want to store the values only in the other. I would expect to be able to highlight the range of the calculated cells in the source workbook, Copy and then Paste Special | Values in the other. Both the source and the target contain some merged cells. When I try to do the paste, it complains that "this operation requires the merged cells to be identically sized". They are identically sized. I have even done Paste Special | Column Widths to guarantee they are identically sized. It doesn't complain about the merged cells then; in fact, it doesn't complain about the merged cells with any of the other paste operations, only with Values. I have been able to repeat the behaviour with a very simple example. See the two attached sheets. Try pasting Values only of the range $B$2:$D$4 from either one to the other. When I first created the samples they worked. What seemed to triggered it is I changed one of the merged column widths by one pixel, tried expecting it to fail (and it did), changed that column width back again and it hasn't worked since. As I said, I even tried pasting the Column Width to the target, which it appeared to do successfully, but it hasn't helped. Now, the attached examples are only to show the principle of the problem. They are very simple and there would be a myriad alternative work-arounds for them. In my original case though there are many formulae over a much larger range and I want other people to be able to paste values without having to follow complicated instructions, so I am not looking for work-arounds unless someone has a mind-numbingly simple one that my numb mind has overlooked. The only other way of solving the problem that I can think of would be to write a macro that copies cell values individually, but I'd rather not do that because I know these people: they will get narky about having to enable macros. It will be "against organisation policy" or something. Is there any way of resetting the target workbook so that it will receive the values from the source?
View Replies!
View Related
Copying Cells Under Certain Conditions Using VBA
I have found several forums with this question but none of them seem work/fit my needs What I need to do is look in Sheet1 at column 'B' begining at row '15' (row 14 is table header) and if the cell contains "Yes" then copy 3 cell directly to the right (C15:E15 in the case of the first row) into Sheet2 into cells B15:D15 then do the same for row 16 and so on up to row 600 Creating a cut down copy of the Sheet1 which does not contain the row which determines if it should be copied.
View Replies!
View Related
Copying And Evaluating A Range Of Cells
Sheet 1 to look into Sheet 2 > Find a certain string "name1" > if string is name, copy succeeding columns if not goes and checks another cell till it finds it. > Then copied columns will be evaluated (sum, divide, etc) this loops through all rows. next problem i am seeing is what if it finds 2-5 names how can i combined all the cells copied?
View Replies!
View Related
Search Filter Copying Cells
I'm trying to write a simple macro that I have filtering values of a column that "= 0" and trying to take the results from the filter and copying those rows into another spreadsheet.. i know there other ways to do this but im hoping if there is a way to do it like this because in reality im filtering a specific month and other criterias also which is gonna make for one long nested loop
View Replies!
View Related
Macro For Copying Certain Cells To Another Sheet
I browsed through the other posts concerning copying and couldn't find (or maybe I didn't just understand) how to do what I want. I looking for a macro for a comand button that when pressed copies certain cells in the active row to another sheet. I recorded a macro for what I wanted to do ( to make the explenation easier) for one row but even the recorded macro refuses to work. I'll put the recorded macro here if it helps someone, but as I said even that doen't work Private Sub Kopiering_Click() Range("E3").Select Selection.Copy Sheets("TOTAL").Select Range("K3").Select ActiveSheet.Paste Sheets("BRL Newbuilding").Select Range("D3").Select Application.CutCopyMode = False Selection.Copy
View Replies!
View Related
|
TRACKER
