I have the following code which deletes the specified value from each cell in column B that contains that value, (note it only deletes the value from that column and not all in the row).
Code:
Sub delete1()
Dim lr As Long, i As Long
With Sheets("Database")
lr = .Range("B" & Rows.Count).End(xlUp).Row
For i = lr To 2 Step -1
If .Range("B" & i).Value = 1 Then .Range("B" & i).ClearContents
Next i
End With
End Sub
Now what I need is to adapt it slightly so that it also adds a comment to the cell in Column I and the current date in Column L, (on each row where the deleted value was).
The comment would be something like "old data archived" and the date in any format, preferably dd/mm/yyyy.
I have slight problem with an OnChange Event by Target.
I am looking to be able to change a cell value from one to another, create a comment block that says "person B for person A" , change the color and be done.
If the cell is empty then the code should exit the sub (not working). If the cell has already been change once then I want the comment box to show a second line of who changed what.
What I tried in the comment box, thinking it would keep the value:
I need to identify any cells in a range that do not contain text then colour and comment them. I intended to use the following code, however it doesn't work as I expected it to.
With rRange.SpecialCells(xlCellTypeBlanks) If .Count > 0 Then .Interior.ColorIndex = zErrorColour .AddComment "This cell must contain a value" End If End With
The blanks cells are coloured OK but only the first blank cell gets a comment. Is there a reason why I can't add comments to all the cells in the range? And if there is a good reason, then I guess I'm going to have to write a loop to add the comments.
When adding a comment I check for an existing comment, if exist then delete and add new comment and some text in that cell. This works as long as the cell has an existing comment; other wise it advances to the "Else" where I thought I would be entering a comment and text in an empty cell. Why does my macro treat an empty cell as if it has a comment?
I have (several) worksheets that have protection enabled. I have unlocked all the cells that users need access to and locked all the column and row headers/labels. When I enabled protection on the sheets, the ability to add comments was taken away. Is there a way to add comments to an UNLOCKED cell in a PROTECTED worksheet?
I have a few dozen pictures created when a macro runs. They all have unique names. I'd like to add comments to cells, where the cell.value decides which picture to pull. All the examples I've found online show how to do this if you have pictures saved on your hard drive by referencing the file path "c://mydocs/...blahblah/"
Is there a way to reference the pictures I've created/named with my macro?
Here's the snippet of code that creates the pictures and names them:
Code: For i = 2 To Application.CountA(Sheets("Allocation").Rows(1)) Sheets("Allocation").Activate Set rInput = Sheets("Allocation").Range(Cells(1, i), Cells(10, i)) sPicName = "_" & Sheets("Allocation").Cells(1, i) & "_" sSheet = Sheets("Allocation").Cells(3, i) dDate = Sheets("Allocation").Cells(5, i)
[Code] ......
Here are some examples that are close to what I'm looking for.
VBA Popup Pictures - 1108 - Learn Excel from MrExcel Podcast - YouTube VBA Express : Excel - Add pictures that float like comments.
Am trying to put comments on column G based on information on column A. Column A contains a grade eg CDEF picked up from another sheet using vlookup. This grade is suppossed to be the same, but I have cases where I add a different grade say GHIJ to fill up my 99 required grades.Column G is for comments such as "Transfered" (which at the moment am entering manually)How can I use a formular so that each time I transfer from another grade other that the one intended it returns a comment "tranfered" on column G? Is it also possible to indicate the cell from which vlookup picked that grade on the same comment column G.
I have a form that has three fields (1. Comments (TEXT), 2. Legacy_Comments (TEXT), 3, Comment date (DATE))
Now my users need to keep adding comments to the comment text box, and when they do it automatically adds the date they entered the comment in the Comment date box. Now my problem is that since they keep adding comments to the comment box, I need to keep track of these comments in the Legacy_Comments (Text box).
For example, the First time a user enters a comment into the (1) comment text box it auto populates the date in the comment date box, and then adds the comment and date to the Legacy_Comment box. the end result is (comment,4/3/2014 now lets say a user needs to add a comment to the comments box tomorrow - I want the legacy_Comment box to then read (comment, 4/3/2014; comment2, 4/4/2014, ...., comment(n),date(n)) OR it can be vice-verse, because I just need to keep track of the comments, I am not worried if the new comments are before or after older (yesterdays / the day before yesterdays comments)
How can I write a VBA code that will always add the new comment to the legacy_comment field, without deleting the comments that were entered previously?
Code: If isnull(me.comment.value) Then Exit Sub ElseIf me.comment.value = true Then me.comment_date.value = date me.legacy_comment.value = me.comment.value & "," & me.comment_date.value & ";" me.legacy_comment.value = me.legacy_comment.value & "," & me.comment_date.value & ";"
It adds the comment only the first time, but it does not concatenate the string from yesterday to the string to today. I do not care which order the comments are, meaning if I added a comment today it can be before OR after the comment from yesterday.
Need one column to combine three columns I have a in a spreadsheet.
Column A - Comments I have a comment called Duplicate or blanks
Column B - Comments 2 I have a comment called Loss Fund or blanks
Column C - Comments 3 I have a comment of Y or N
I just a want formulae which put the following in one column Where it is Duplicate in Column A Where it is Loss Fund in Column B Where it is Y in Column C
I have a substantial schedule that has been completed by a 3rd party who has used the comment function for important data. Can anyone suggest how i can utilise a macro to extract all of the comments into the adjacent column?
The comments are currently against cells in column E and i'd like to extract them to blank cells in a newly inserted column F.
I try to edit comments in cells of a certain column and nothing happens. I am able to edit other comments throughout the spreadsheet, but when I select "edit comments" for these particular cells, no edit box is displayed for editing. It worked fine a couple of weeks ago.
I have an worksheet that refreshes every day from a .csv report. I would like to be able to have a comments column at the end that users type in. The problem is how to keep the comment next to the row it was on before the refresh.
When it refreshes there will be new rows, rows that are no longer there and all in a different order.
There is an Order Number column that can be used as an ID column.
I needed to match the width a merged area of seven columns to a single column width (for row autofitting). Adding the column unit values and setting the single column to that value produced a significantly narrower width.
The documentation mentions that the column width unit is scaled to the font type and size and the absolute width is given in points.
This is set by the normal style setting in Excel Options or by VBA application.standardfontsize = 8 (in this case).
For instance, ten columns of Arial font 8 at 8.5 units you would think to be equivalent to a single column of 85 units. In points, the difference is 420 vs. 386.25, or 33.75 points.
Well, the standard character zero has a width at this setting of 4.5 points and 1 unit is 8.25 points, leaving 3.75 points for margins.
Then (10-1) margins allowances time 3.75 points resolves the difference.
Determining the margin allowances is straightforward, and reveals that the gradation with size is stepped by MS design. For instance, sizes up to 11 use 3.75 points for margins and increasing points for characters (except between 9 & 10). Sizes 12 through 18 use 5.25 points, 20 & 22 use 6.75, 24 & 26 use 8.25, etc.
I have created a table for this purpose, however I rarely use a "normal" other than 8, so I can probably use that set in programming.
I have a worksheet (Sheet1) that is constantly growing with information. I have several categories under the "Category" column and then the various amounts under "Amount" column. I would like to have Sheet2 be able to keep a running total of the "Amount" column for each category as it increases in entries. I've attached an example sheet.
Column 1 Column 2 January 1000 January 1234 February 1300 March 1600 January 15 March 123 April 234 January 3000
I would like a formula that adds all the January numbers together returning a result of 5249
To move this one step further i would eventually need to add these numbers based on quarters, for example if the value is January February or March in column 1 then add the numbers in column 2.
I'm sure i have done this before using a countifs maybe but my mind has drawn a blank
I want a cell on one spreadsheet (SP-A) to add up a column (M) in another spreadsheet (SP-B) rows 11 through 10000.
Here's the thing, I want that cell to actually display not the sum that it gets but that sum minus all numbers (in M column again) which column E is filled out with any data for their given row...
i'm not even sure if this makes sense lol... let me use example
on SP-B there's a column M. in row 11, value = 3 in row 12, value = 5, in this same row column E is filled with whatever in row 13, value = 2, in this same row column E is filled with whatever in row 14, value = 6
in my SP-A I need the cell to display 9, since rows 12 and 13 have values in E and I don't want to add those to the sum.
How would you prevent the copy/paste of cells that have comments?
Also, how would you allow cells with comments to be copied and pasted without pasting the comments?
I also have an aside question about the forum advanced search. When searching for multiple search words, how would you type the search to include all words, for example, "prevent" & "paste" & "comments".
I am having a column which has numbers. The length of a number should be 8 or should be 8 digit.I want to standardize the columns by adding leading zero. For example
I have a column which has either EDC or EDT in it, can I add a function to this column which says 'if EDC then output Eau De Toilette' and then Eau De Toilette get's ouputted into a seperate column? Is this kind of thing even possible in Excel?
This is a simple one, for someone who is smarter than I. I need to add a column but omit the rows that are blank or has a zero value in either row C or D. In other words I want to add every row in column C if there is a value in row C&D of that item. Attached is a small example.
I've got an excel sheet which has names in column A followed by 5 numerical values in columns B-F. I'd like to get a macro that will compare the values in Column A and if they match, add the B values, the C values, the D values, the E values, and the F values and delete one row - effectively combining the row. What's the best way to go about this? Also the sheet is already in ascending alphabetical order by Column A, so the rows that need adding will be next to each other. So I guess compare each A value with the one below it and if they match add the rows, but how?
I've been searching for the answer but either I do not understand or it isn't out there. I want to add every other column in one row resulting in an average of all the columns in that one particular row.
Can I have a macro code to insert a row from column A:K.
I have data in column L to IV which I want to stay as it is. i.e. for example when a row is inserted from A:K, that row should not extend beyond Column K.
I have several columns of times in the HH:MM.SS format. I need a sum at the bottom of each column.
If I put a Sum or a Cell+Cell formula in, it gives me the #value error. I've tried a few different things but can't get it to work.
Could someone be so kind as to tell me the formula or point me to the instructions for this?
(The data reflects times spent on certain tasks for each day, and I need a sum at the end of each column giving me a total amount of time spent on that task for the month.)
I'd like to add the numbers in column C that correspond with Fuji, Fuji Royal, or Fuji Premium in Column B. Essentially I am trying to count up the total number of all variety of Fuji apples. I've tried using the sumif and sumifs formula, but haven't been able to crack it.
RED APPLESSIN ESPECIFICAR2.352 RED APPLESROYAL GALA1.029 RED APPLESCRIPPS PINK280 RED APPLESROYAL GALA896 RED APPLESCRIPPS PINK1.064 RED APPLESFUJI867 RED APPLESFUJI PREMIUM1.919 RED APPLESROYAL GALA4.871 RED APPLESCRIPPS PINK1.176 RED APPLESFUJI112 RED APPLESROYAL GALA1.064 RED APPLESSIN ESPECIFICAR2.282 RED APPLESSIN ESPECIFICAR4.522 RED APPLESCRIPPS PINK1.176 RED APPLESROYAL GALA2.352 RED APPLESCRIPPS PINK7.056 RED APPLESFUJI1.96 RED APPLESPINK LADY1.344
