Need a macro for the following:
C | D | F (Column)
10 | 100 | Yes (data)
If column F = Yes then
column G = "abc = 10, def fgh = 100"
where 'abc =' is additional text, '10' is C value.
Loop till the last row of F.
Basically, I want to autofill column G with the data derived from column C & D + some text.
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 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.
how to copy the contents of multiple comment boxes and paste in a single comment box.
The big picture is that I have a number of cells with numerical values in and text in comment boxes. I want to be able to click a button to copy the contents of the comment boxes and paste them, along with the numerical value from the cell, into a single comment box, ordered by highest to lowest value within the comment box, then delete the original cells and comments.
I am quite new to VBA but have been coping quite well so far with information of the web and analysing recorded macros.
how can i run a macro after a comment is inserted?
View 6 Replies View RelatedI am using the following macro to creat comments based on the contents of certain cells. Everything works except the it resizes the Comment Boxes exponentially. The Last comment box in the range seems to be the default size, the next to last a little bigger, then it start getting even larger and larger until the first comment in the colomn.
Sub InsertComm()
Dim RgPartnumb As Range
Dim NRg As Range
Set RgPartnumb = Range(Range("B2"), Range("B2").End(xlDown))
On Error Resume Next
For Each NRg In RgPartnumb
With NRg
.Comment.Delete...................................
I want to design a macro to check if the cell has a comment in it. If so, the macro should read the comment and store it in a variable.
View 2 Replies View RelatedI 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
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=""
With Selection.Font
. Name = "Tahoma"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False...............................
I know that there is a way to delete macros in a file using another macro in order to save the file as a macro-free copy. I was wondering though if there is a way to comment and uncomment code using a macro, as well. The reason I need to do this is that I have a module that builds Excel files for me using a template, but that template has to have certain lines of code commented first in order for the module to run correctly. After the module is done building the file, I must uncomment those lines manually and was wondering if there is an automatic way to do this.
View 3 Replies View RelatedI need a quick function or a Macro which can add cell comment based on selected cell.
let me explain in detail
Column
a1 Mr A wrote
a2 Mr B Checked
a3 Mr C authorised
etc etc.
now i want the same in Cell B as its cell comments
so after b1 comment will be Mr. A wrote & so on
I have comment blocked the code that causes Excel to crash.
View 4 Replies View Relatedplease have a look at the attached file.
I want to sort the data by the comments in Column B, specifically by comments that do contain a telephone number, whether it is "Telefon" ( e.g. in B17 ) or "Phone" ( e.g. in B24 ) or "Téléphone" ( e.g. in B16 )
I know a Macro written in VB could do that.
I am in need of a macro that will copy the text from A1, and paste it to a comment on every cell that contains text within sheet 1.
View 4 Replies View RelatedI really appreciate help from the excel gurus and expert here on my little problem. I'm rarely using excel and have a very little knowledge on VBA programming.In short, I've been requested to develop a shift schedule for my department that can automate the shift staff schedule on the monthly basis.
I've created two worksheet of which the first worksheet is represent as master data list (INPUT Worksheet) that contain with formulas. While the other worksheet is the automated shift data (REPORT Worksheet) that linked from the master data list (INPUT Worksheet).
Attached herewith is a sample of my work. As you can see,I wanted to copy comments from the input worksheets (on DUTY column) so that it will then automatically appears or updated in the shift schedule. Whenever the dropdown selection change, the data will change accordingly. On the REPORT worksheet, each cells, range D14:AH38 are referring to DUTY column month by month. I'm able to link it but it fails to copy over the comments as well. I believe that macro can resolve this, can someone help me on this?
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!
making a macro that will scan for duplicates, length & empty cells.
I have the sample file below that does conditional formatting but it doesnt help as much because I want to show the reason for the highlight's on a comment instead.
sample file : [url]
file that might help out: [url]
checks would be:
column A - duplicates and/or length should not be over 100 characters
column B - duplicates
column F - should only contain 2 comma's(or 3 keywords)
all columns - check if no entries are found(empty cells)
I am trying to make a save&close workbook macro.
I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
I am looking for a macro that i can store in my personal.xlsb. what i need is pretty much is something like this
private sub workbook_open
if workbook.name "inventorysummary.csv"
then application.run "personal.xlsb!capacity"
end sub
I only need it to run just for this file and i cannot place it in the file due to it gets replaced every day. Which if it didn't get replaced. I know how to do auto opens when the file stays the same I am just unsure for this.
(Brand) New to VB so apologies in advance if this is a simple fix. I have a spreadsheet that monitors truck cycle times. I have it set to update every minute. One of the columns contains date/time info sorted last to first. Every time the first cell in this column updates, I want to run a macro that copies info from adjacent cells to another area of the sheet. I have seen the "byval target as range" posts, but don't really understand what it does, and whatever changes I make I can't get it to run.
View 9 Replies View RelatedIs there a way to add auto complete to this Macro so that when a user starts typing in a cell in column A it auto completes.
[Code]....
The spreadsheet I'm working on is kinda hard to describe, but I'll try.
It's a finance calculator of sorts that I need done, and it shows me the total cost of various products. Each of these products require different components, and numbers of components, and also these components all cost different prices. So what I have is the following spreadsheet setup:
I have a macro to run which will clear out all rows and move them corresponding sheets on the same workbook. At the minute i have this macro to run at 21.00 hours everyday. Is it possible to fire this macro to auto run before me closing a workbook so i don;t need to wait and also worksheet is saved with the latest changes
View 3 Replies View RelatedI am using one spread sheet to store date that gets updated throughout the day and am pulling information form that sheet to another file. I need to save it to get the current data and I don't want to rely on the person entering data to remember to save. The sheet I want to save is called Log.
View 8 Replies View RelatedI have a sheet pasted below for reference. I am trying to make a macro that when I type the word "Completed" into "Sheet1" row N then in this sheet pasted (ProductionList) below the corresponding row gets deleted. I hate explaining this stuff. I'm so bad at it.
******** ******************** ************************************************************************>Microsoft Excel - Unique Print Schedule.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)boutA9B9C9D9E9A10B10C10D10E10A11B11C11D11E11A12B12C12D12E12A13B13C13D13E13A14B14C14D14E14A15B15C15D15E15A16B16C16D16E16A17B17C17D17E17A18B18C18D18E18A19B19C19D19E19A20B20C20D20E20A21B21C21D21E21A22B22C22D22E22A23B23C23D23E23A24B24C24D24E24A25B25C25D25E25A26B26C26D26E26=
ABCDEF1*2345678Job*NameTowelQuanitityDate*Due:HandlingPress9GCHS*Tigres1525-23-BKHL759/14/2007Ship*From*Team*Spirit*TowelsB10Crunk*Energy1518-125*WHHC50009/18/2007NoneDONE11Atlanta*Falcons1518-15WHHL500069/18/20076*Post*Pro*To*DonB12Sunstreet*Mortgage1626-30*WHDC3029/18/2007Hook*&*GrommetR13McDonalds*Hacer*Golf*Outing1626-30*WHDC3049/18/2007Hook*&*GrommetR14Arizona*Cardinals1218-13*WHFI670069/20/20076*post*Pro*To*DonR15Michigan*Go*Blue1118-12PMS10910169/20/200710*towels*to*Greg*Cannon*6*to*DonR16Golf*Etc.1626-30WHDC1549/24/2007Hook*&*Grommet.**4*samples*to*sendB17Buffalo*Bills*"Billieve1118-12WHHR600369/26/200736*post*pro*to*Donny*DonB18Wreck*Em*Tech1518-125*WHHC100510/1/2007NoneR19Go*Hogs1518-125*WHHC100510/1/2007NoneR20U*Conn*Huskies1518-125*WHHC100510/3/2007NoneR21Go*Bulldogs1518-125*WHHC150510/3/2007NoneR22Chargers??6800010/8/20070R23Kansas*University*Homecoming1118-13RYFC2501810/30/2007Strike*to*Corey*&*VernR24Go*Aggies1518-125*WHHC50511/1/2007Drop*ShipsR25St.*Paul*USBC1118-16*COLORS1512FlexibleRDDC,*HUDC,*RYDCB26Fire*Antz1118-12WHHR2000PENDINGNoneBProduction List*
[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.
I have a macro that allows me to type numbers in cells and it auto adds to the previous value... what would be nice is if when i hit enter the cell focus stays at the current cell and doesnt move the next cell down... that way if i wanted to enter multiple values into one cell i wouldnt have to touch the arrow keys to get back to the cell
e.g: i enter a value into A1 and hit enter, instead of the box focus moving to A2, it stays at A1
Heres my current
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("DATA")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value + oldVal
Application.EnableEvents = True
End Sub
The following code has been tested on Excel 2003 and Acrobat 8.0 Pro:
' This line of code specifies your directory as well as the cell or range which you want the filename to come from. As you can see, I have a specific cell with the range name "InvNbr" so that the macro knows to pull the filename from there. If you don't want to use a range name, just replace InvNbr with your cell reference, such as C4.
Filename = "C:Folder1SubFolder1" & ActiveSheet.Range("InvNbr").Value & ".pdf"
' This line of code sends the filename characters and the ENTER key to the active application. The "False" statement allows the macro to continue running without waiting for the keys to be processed.
SendKeys Filename & "{ENTER}", False
' This line of code calls the Adobe PDF printer and runs the conversion. To ensure that you replace this code correctly with your own PDF printer, simply record a macro to print to Adobe PDF and then copy and paste it here....
Sub Custom3()
Range("A785:BW1455").AutoFilter Field:=2, Criteria1:="a"
Range("A785:BW1455").AutoFilter Field:=3, Criteria2:Range ("N").value
End Sub
Is Field 2 referring to Column B? So if I want to filter on the 9th row heading (going from left to right), my Field: = 9?
In the example above, the 1st criteria revolves filtering on dates with the goal of exceeding a certain date (i.e. > 1/1/08). Is there a way to reference the cell (which is in another workbook which is already open) in the criteria?
I have a spreadsheet where on one tab, there is a list of user inputs for each item number (i.e. Item 1 - Length A, Height B, Width C; Item 2 - Length D, Height E, Width F etc.) so the user is able to modify the number of items and the specs for each item. On another tab in cell BB8, the user is able to type in the item number and an image of that item is displayed. The user can then print out the image.
My problem is that usually the number of items is very big list and so it is tedious to manually change the item number each time you want to print out a particular image. I want to write a macro that can automatically change the item number and print out the corresponding image (So for example, if there are 10 items, print out all 10 images with a single macro, as opposed to manually changing the item number and pressing print each time).
I am trying to set up an macro that runs when a workbook opens
I have placed the following code in the This Workbook Object
Private Sub Workbook_Open()
Worksheets("Test").OnEntry = "Every_Update"
With ThisWorkbook
Application.OnTime Now, "'" & .Name & "'!" & .CodeName & ".Workbook_Open2"
End With
End Sub
I am tyring to instruct Excel to run the macro "Every_Update" every time an entry is made in the worksheet "Test". This seems to fuction.
However, I have other code that I am tring to run in a a macro called "Workbook_Open2"
That macro contains functions that are not yet loaded into Excel during the Auto open sequence. I was hoping that by calling the subroutine, it would allow the functions to be enabled. Basically the function that I a trying to get is to open up another excel file and read a cell.
The code above gives me an error - it cannot find "Workbook_Open2" Should this not be in a module?
my macro simply activates a cell and all i want it to do is to auto fill downwards!
its intended to autofill down for about 100 rows but just cant figure it out!
but keep in mind the cell is always going to change so it cant be fixed
i.e this wont work:
Selection.AutoFill Destination:=Range("C95:C659")
Range("C95:C659").Select
ActiveWindow.SmallScroll Down:=513
because i need it to be autofilled from the CURRENT active cell only!