In the workbook the sheet names are month names e.g. july,august,September etc and 1st of each month is to be entered in a cell in each the sheets. By some trial and error I wrote this macro
VB: Sub test() Dim j As Integer, monthnr As Long, monthname As String For j = 1 To Worksheets.Count
This worked in July sheet it is written as 7/1/2013 and in august sheet it is 8/1/2013 etc Then I shortened the macro like thlis
VB: Sub test() Dim dte As Date dte = "july" & "/1/2013" Range("A1") = dte End Sub
It worked. Even if I use shortened 3 letter form of month like "Jul" or "aug" then also it works
But if type on the spreadsheet itself ="july"/1/2013 It Gives value error.
Perhaps it works only in vba and not spreadsheet.
I accept nobody is going to use "july" & "/1/2013" instead of 7/1/2013 But this will be useful if sheet names are month names so that when we write the date we can use sheet names Mine excel 2007 and windows 7.
I have created a pivot table using a family name in row labels. The names appear in alphabetical order unless the person's name is the same as a month or day of the week. The result is that I have Mr Sun and Mr May at the top of my list, rather than listed alphabetically. Sorting the list does not solve the problem. It switches Mr Sun and Mr May, but does not included them in the main list. Is there a setting I can use that will stop Excel 2007 thinking that these words are something that they are not?
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)
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.
I have several sheets I use for MI and most have similar amounts of data in, ie, a tab for each month with out 15-20 columns and upto 10,000 rows, then a couple of summary sheets with filtering and calculations in.
One of my sheets is currently 16Mb, when the others are about a third of that, with similar amounts of data.
Is there a way to find what is causing the extra space to be taken in this sheet?
Or does this seem about right for the amount of data?
If I have a cell which begins with a few spaces and then has a character, how do I use the find function to see how many spaces until some character is present in the cell. The * does not work with this.
I am wanting to find rows that with unique values in the first column, and then take those rows and display them in a more readable format. The section titled input is an example of information that would be pulled from a query into Excel. The Desired Output section is how I would like the info to be displayed. If this is possible without VBA then that is even better.
I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?
I have two spreadsheets. one contains a master list of computer names. the other contains a list of computer names to be removed from the master list (exception list).
I need to go through the master list and remove any computer names that are on the exception list- for example, if 'computer1' is in the exception list, i have to find and remove 'computer1' from the master list.
The exception list is quite long, and I want to automate this process if possible. not sure how to achieve this.
Following the tips doesn't allow to remove a character.
I exported email addresses from Outlook and they have the following character ' in front of the email address.
Using Excel 2007.. it says :"check if your search formatting and criteria are defined correctly. If you are sure that matching data exists in this workbook, it may be on a protected worksheet. Excel cannot replace data on a protected worksheet"
So I made a new file, and copied and pasted the cells into the new spreadsheet.. same message.
Using Excel 2007 and conditional formatting, how would I find duplicates across all fields in the table, similar to the "remove duplicates" tool that in default mode selects all the fields or offers the option to select fields of choice. I need to look at all the fields.
I've tried concatenation, but only with a limited number of fields; larger amounts slow the process to a crawl. If my table is 10,000 records with 15 fields, what process would I use to highlight the duplicates. How about something in VBA?
The above basically checks for a number, and if fails (there could be #N/A, #DIV/0!, text, or Number-stored-as-text in any cell here), it checks for text. (the 65000 is arbitrary; we will never have more than a few thousand rows)
However this is not reliable--the main problem seems to be number-stored-as-text. And it's not possible for me to do the Paste-Special Multiply *1 trick to convert the numbers because this data is dynamically generated from a database--the user would have to do that every time the sheet opens.
Is there not some formula that simply says: "Give me the last non-blank cell after row 4 in column E, regardless of what type of data"? (Excel 2007)
I have this macro below written. It isn't working for me and I am trying to figure out how to write it. Basically, the macro is supposed to go to the test.xls workbook and then look at names in row 4, from there it will take each name and search for it on the all csrs new.xls workbook and then move over to the right 5 times and copy data and paste 1 down from the name on the test.xls workbook. One workbook has the names going to the right and the other workbook has the names going down....
We were looking at a list of about 100 FedEx tracking numbers, each 30 characters in length, in an otherwise blank Excel spreadsheet. All tracking numbers in column A.
I used the Conditional Formatting - Highlight Duplicates feature to find any duplicate tracking numbers. It highlighted numerous. We sorted the column smallest to largest to look at two duplicates back to back, but found that there were actually no duplicates. Excel was highlighting values that had the *first 25* characters the same, but it didn't matter what the remaining characters were. For example:
1234567890123456789012345XXXXX and 1234567890123456789012345YYYYY
were highlighted as duplicates of each other.
Excel only looks at the first 25 characters when analyzing duplicates (at least in this specific function). That in and of itself blows my mind. Tracking numbers seem like an abundantly common type of data, and to think that Excel would fail to analyze them in such a simple manner seems impossible to me.
It gets slightly more confusing though. If you back a character off the end of the values, it NO LONGER sees them as duplicates. So...
1234567890123456789012345XXXX and 1234567890123456789012345YYYYY
are NOT highlighted as duplicates while
1234567890123456789012345XXXXX and 1234567890123456789012345YYYYY
ARE highlighted as duplicates. So there's some funny business going on about values of the same character length. Maybe Excel first looks at character length before analyzing specific characters. If length doesn't match, it doesn't even bother to analyze specific character values. That seems like a very strange programming choice though.
I need to find the average numbers on sheet 1 column D. Column D contains numbers as well as Blank cells. The info will be presented on sheet 2 in a monthly view. In other words i need to ssearch awithin a date range and determin the average of those numbers. This works on some months but return a DIV/0 erro most of the time.
Dim strLoan As String Dim longCat As Long Dim rHere As Range Range("A1").Select [Code] .....
I can see the cursor move to all the desired cells when this macro executes; it just isn't dropping any data where it should be. I have been working at this stage for the last five hours with no success. I don't know whether my copy-paste methodology is broken or if it is my selection criteria
Excel 2007 I have a spreadsheet of file boxes the keeps a log of boxes, contents, locations and shred dates. Currently figuring out when and which boxes can be shredded is a manual hunt, find, and then deal with it. I can use CF and Sorting but I have been looking at some search type functions to return a list meeting the requirements and trying to step my way towards that.
1st attempt. Was find out how many boxes meet the shred (before today) that have not already been detroyed. Column K is the Shred Date, D1 is Todays date, Column G is Status. Got this working good.
My failed attempt came at trying to find the earliest shred date excluding those that have been destroyed. This is not really neeed but I was hoping it would get me closer to creating the list of boxes that meet shred that have not already been destroyed.
Note: Column A is the Box No. and the spreadsheet contains blanks as we continue to add file boxes to storage.
Below array formula find the highest box number used by each department.
I would like to create a list of boxes including Column A "Box no.", Column H "Location", Column K "Shred Date" for any box whose shred date is before today that has not aleady been labeled "Destroyed" in Column G.
Am working in Excel 07, but this would need to work in 2000 as well.
Need a macro that will...
1. Select a range of cells from B4 to RX. X is defined as the last row where Column A has a value. 2. Copy the visible cells 3. Open an email in Outlook (not via the email workbook function of excel), enter "Submission" into the title, enter "Dear X," insert 2 returns. 4. Paste the copied table (not the workbook, just what is on the clipboard) into the body the email.