Use Excel VBA To Find Values In Table And Insert In To A Comment?
Jul 5, 2012
I have a table refreshed from a MySQL database that I use to create a report. I can use this data quite well using formulas in the cells.But in order for me to add more information to the report I use without making it cluttered I need to add some data as comments.
I have created a loop that picks up some cell values that I want to use as criteria but I cant work out how to use these variables to search through the table and get the info I require to put in the comment. In a cell I would use offset/match or index/match to get the required cell value but how do I do this in VBA?
The rows, from row 3 down in column A contain part numbers.
The Columns, in row1, from B to (last column with data) contains job numbers.
On another sheet, named: Non_Completed. I have the part numbers in column A (as in the PartNumVsJobNum worksheet). In column E down are Job Numbers (IE: 4PZ). In column G are quantity values.
So for instance if Cell (A3) = 360010 (first part number listed) and Cell (B1) = 4PZ I need a macro the will find the rows with 360010 in Column A on sheets( Non_Completed) and try to find the job number (4PZ) in (columnE) for one of the rows. If the job number is found, then the value in G of the (Non_Completed) sheets should be copied to the corresponding XY cell in the PartNumVsJobNum spreadsheet.
I will need to do this for each job number on PartNumVsJobNum Sheet.
I'm wondering if it's possible to use a lookup formula to populate cell comments? In other words, I'd like to put a lookup table into a cell, and if the value is not FALSE, have that Lookup table then pull in certain information into the cell's comments.
I have manually shown comments on 2 cells in sheet2 that shows detailed description on how was the total taken.
I need the comment on all the cells from D8 to G11 (not taking the total column) that shows on how was the total taken. When any entry in sheet1 is inserted or deleted the comment in sheet2 should change accordingly.
I also do not want to freeze the cells D8:G11 as when new entries are made or deleted the columns would increase or decrease.
I have data validation and conditional formatting set for my sheets. However, I cannot figure out something.
I want a "comment" box (just as if I would click on "add comment") to pop up automatically if the user enters a certain value in a cell.
For example, if cell C4="1" then no further action is required. If cell C4=anything other than "1", then the user would be required to enter a comment. I would also like the comment to autopopulate with the users login id.
I will attach the book I am working on for reference, but I do not have any code written for this as I could not figure out where to start.
scenario: sheet & work book are protected to users; however need them to sometimes insert a comment to this sheet.
all columns/rows are locked except columns K, R, Y and every 7th column thru CJ (rows are from 4 - 100).
how do I enable a user to insert a comment without unprotecting sheet / WB? note: there's no pattern, it could be any cell within aforementioned columns.
I wanted to make a simple macro forinserting a comment, given certain parameters: margins & alignment...but its not working...how would i get this to function properly
I'm trying to create a macro that when run, scans Row 12 (only after column E), Finds the first empty cell, then inserts the cell value from Sheet4 CellE8. Then the hard bit begins. I need it to insert cell info in all the cells below it, from different locations...
For example
A B C D
[Code].....
In the above sheet, I need it to go to cell D2 and insert the values from Sheet4 CellE8, Then proceed to D3 and insert data from Sheet3 D4, then to cell D5 and insert data from Sheet1 A7, etc etc
I dont mind doing each cell individually, but they will always be in the same column (and row 2 "Value" will always be the one that determines the next empty column).
Is there a way to auto insert a cell Comment when a particular value is entered and then have the Comment copied to a cell in another worksheet in the same workbook?
The value entered into the cells i want this function to work with is from a Validation list. If the cell's value was to change, for instance the user going back to a cell to change the value for whatever reason, I would like the previous comments to remain and then give the user the choice to edit comment or not.
I've had a look in the Forums using search but nothing close seems to be forthcoming.
I have a user-form with around 10 text-boxes. Each one of them has labels.
I was wondering if there was any way to add a function similar to the "insert-> comment" available in excel? So that there would be a tiny marker and when the mouse hoovered over it, some text in a comment bubble would be revealed? Or even better that somehow using VBA that each time you enter a text-box a comment would temporarily appear?
For a variety of reasons I can't just add labels with this info. This info would be instructions on what sort of data you should add to the field, and the instructions in some cases will be quite long and the user-form is too big as it is.
I need a nudge in the right direction with how to amend the below code so that it : 1: cycles though all cells in a workbook, and sees whether the text reference of the cell contains a picture filename (i.e. searches for .png or .jpg in the cell contents) 3: if (1) is correct, it retrieves the picture from "C:/Users/jeff/Documents/Standards/" and pastes it as a cell comment background in the cell to the right (and overwrites any comment backgrounds that might already exist there)
The code below does something a bit different: it looks in a defined range, then adds a comment with a background picture retrieved based on the text in the cell to the left.
I'm sure this is a pretty basic change, but my VBA skills aren't up to it...I've only just started reading though Walkenbach's Power Programming! I'm using Excel 2007
Sub InsertComment() 'www.contextures.comxlcomments03.html Dim rngList As Range Dim c As Range Dim cmt As Comment Dim strPic As String
On Error Resume Next
Set rngList = Range("A1:A5") strPic = "C:/Users/jeff/Documents/Standards/" On Error Resume Next For Each c In rngList With c.Offset(0, 1)...................
I've found many resources that tell me how to allow for the insertion/editing of comments within a protected worksheet. When I protect the worksheet, I can select "Edit objects" to allow anyone to insert comments.
However, my issue is that I'd like the Insert/Edit comment functionality to require a password, and this happens to be the opposite of what those resources instruct. I also want the content in those same cells to be editable by anyone.
I would like to Insert the contents of a Range of Cells into a single Comment. ie: The contents of Range A1:A50 into a Comment placed at F1. Is there a Macro that will do this?
Is it possible to insert text as a comment on another sheet based on a date?
I have Sheet 1, that has 3 columns, Name, Date, Reason
Sheet 2 is a monthly calendar with the dates in E5:AH5 and the names from D6:D10.
What I am trying to do, is when they enter their name, date and reason on sheet 1, I want the reason to to be inserted on sheet 2 as a comment in the cell that matches the date and the name.
I have created an attendance tracker for a team of people at work. It simply involves a grid of the days of the month and on each 'day' (or cell) I have set up the Validation command to prompt the the user to select whether they were at work, on holiday, off sick etc. etc from a drop down box. If they do not change the cell, its defaulted value is that they are in work.
When a user selects the days that they are planning a holiday in the future, they are meant to write a comment on the cells they have changed, to advise when they had 'requested' the holiday.
What I would like is (a macro?) which makes the comment box automatically 'pop up' when they change a cell from its default value prompting them to fill in the details rather than relying on them to 'add comments' manually.
In addition, what would be the best way to 'restrict' them from booking holiday within the next 7 days - we have a problem with people booking holiday on 'lastminute.com' for 2 days time, booking off the holiday on the spreadsheet and saying that they had it planned for weeks!
One aspect of my Excel-based project involves comparing the operator-entered part number (in Excel) to a predetermined list of part numbers in one column of an Access database table. Right now, my program is telling me that every part number entered in the spreadsheet (50+) does not match any part number in the database, which I've verified to be incorrect. I've checked that both the spreadsheet part number and the database part number are of the string datatype.
To the best of my knowledge, my looping logic seems valid and robust. To the best of my knowledge, there are no hidden characters in either the database cells or in the spreadsheet cells causing this apparent mismatch. I'm completely stumped at this point as to why my program doesn't detect any matches between the spreadsheet and the database table. Below is the Sub containing the code for checking that the part numbers match:
[Code] ....
This issue seems to be a hybrid issue between Excel and Access with (to me) more of the issue on the Access end.
I need a find the intersecting price values for any given height and width. From what I have read on the internet, I can use the "=INDEX( ),MATCH( ),MATCH( ))" formula. However, this doesn't seem to work because the height and width is separated into intervals.
What I'm looking for is a formula that will search a data table on a source tab and find a value in a column. And have the ability to function like a vlookup and display the contents of cells in the same row on a different tab. The catch is the value will repeat it's self at random interval in the data table. So for example the values are ON-1 to ON-8 and MB-1 to MB-8. Each value will have it's own tab and I want the formula to search the Source tab and capture the value in order but on the next row of the spread sheet.
I'm trying to come up with a formula to find and proper sum the value in a table like this one:
Month/year 1995 1996 1997 1998
Jan 3,63% 0,86% 3,5% 1,5%
[Code] .........
But the actual table goes till december and the years till 2013. For instance, if I need the cumulative index from, let's say, jan/2010 to feb/2013, I'm having trouble with this formula. I can't get excel to sum them properly.
Problem description: I need to put together twelve arrows each comprised of four components; field point, front insert, shaft & fletch and nock. Therefore, I have forty-eight components in an Excel table. Each of these components may differ slightly in weight. My objective is to mix and match all of the components to create as many arrows as possible of the same exact weight.
Two questions. 1) How should I be thinking about setting up the problem? 2) Which Excel function returns the results I'm looking for?
I am pretty new to VBA and have been wrecking my brain and reading just about every Thread there is on this and still can't figure out why I am not getting the code to work.
I am trying to get data from Column "Sale Price", stored in Table "MasterInventory" on worksheet "Master Inventory" to populate a textbox in a UserForm by means of Vlookup.
Upon running the code below I'm getting Value Error 1004, and during Debug when I hover over "MasterInventory" it shows "MasterInventory=Empty"
Also Im trying to figure out how to do it so I can call the "userform" up from any worksheet and add the entries in the table on worksheet (Jan, Feb, Mar, etc.) for the month depicted in the TextBox "Date" on the Userform
The code looks like this:
Private Sub CBx_PROD_AfterUpdate() 'lookup value in Col F [Sale Price] based on Product (Col A [Description] in Table [MasterInventory]) With TB_SP If OB_Y.Value = True Then Me.TB_SP.Value = Application.WorksheetFunction.VLookup(CBx_PROD.Value, MasterInventory, 6, False).Value End If If CBx_PROD.Value = "" Then Exit Sub End If End With End Sub
Table 1 is a long list of unique items in no particular order, each of which belongs to a unique group of items, for example:
ItemsGroup ID apple pear grape fig banana guava peach mango
Table 2 lists all the Group ID numbers and in the same row as each Group ID number, shows the names of all the items belonging to that group, one item in each column. Different Groups have different numbers of items in them. For example:
Group IDItem1 Item2 Item3 etc. 214applepeargrape 33fig 7bananaguava 59peachmango
Is there a formula I can use in the second column of Table 1 to look for each item in Table 2 and tell me what group it is in? For example for "apple" or "pear" or "grape" it would look in Table 2 and return the Group ID number "214".
I can't find the Pivot Table icon in the toolbar. I tried "Insert", I can only see the 'table' icon, but cannot see the 'pivot table' icon. how can I find this 'pivot table' icon, or create it in the tool bar?
