I am trying to extract some data based on two criteria. The first is that one row cell value is greater in than an agreed date/time, in my case this is 7:00am yesterday. The second is that the first 5 characters of a second cell, in the same row but several columns along, are not equal to 'Monit'. If both of these are met then the code will strip out several cells of data from that row.
I can do the first and last parts of this but the 5 characters check is proving a little more difficult in my limited VBA knowledge. The second cell is simple text format and I can't do a LEFT equation in a different cell to get those 5 characters (restricted workbook range so no room). how to go about running a check? I'm thinking it's probably quite simple but trying to search for an answer to it is tricky.
Is there any way to insert a carriage return after each instance of a character? I have a section of data that requires a carriage return after each instance of -v eg:
I have the below in cells A1 and A2. I need to find the last comma then return all characters after it. I need the same formula for both cells because the value is always changing.
HUG,PU-TUB,BW,-,-,64 Need to Return 64 in B1 HUG,PU-RFL,BW,-,-,176 Need to Return 176 in B2
IF there is a whole number in either column a, b or c, I need to have column e reflect that with the insertion of a / for each column that has a whole number. i.e.
Column - A - B - C - D - E 3 4 / / 5 6 9 / / /
I am attaching a copy of the spreadsheet showing the examples, I can't seem to get the function to work.
I have two columns that have similar information. I want to copy the unique information from one cell to a third, blank cell. In my case cells d2 and cell e2 are almost identical except cell d2 has addition information. I want to copy that information into cell f2. I have a feeling I am making this harder than it needs to be.
Is there a way to pause a macro while it is running? For example:
COL A COL B 1 2 b 4 5 5
My current macro highlights all characters in this case b. Is there a way to make it stop when it highlights b, and make it continue running when I want? I'm using a Form for this purpose.
What I need is a macro that will remove any instance of "R", "-1", "-2", "-3", "-4" from the end of a cell. neither of the 5 values listed in the last sentence are present, the the cell will be unchange. So, after running, the above values would look like this:
I have a long list of items in one column, with parameters that are all split by an underscore. However, in some instances, some of the text that is separated by an underscore needs to be joined together.
So for instance I'll have;
This_is_a_very_good_thing This_is_a_perfect_thing
Where I want the result to be that "good_thing" becomes "good-thing" and "perfect_thing" becomes "perfect-thing".
How can I get this done with a Macro, instead of constantly doing a Vlookup to change this instances.
Is there an Excel formula to remove the spacebar + characters in red, as shown below? I need to be left with only the last name, first name and the semicolon.
Mouse, Mickey ;
Microsoft Outlook has changed the way that email addresses from the global addressbook copy and paste (from version 2003 to version 2010).
i have a problem counting the number of characters in each cell in column "A" in a sheet and checking if number of characters in a cell exceeds 5 characters.
i have a problem counting the number of characters in each cell in column "A" in a sheet and checking if number of characters in a cell exceeds 5 characters.
Is there a way to specify if there are 46 characters in a cell, to apply text wrap, indent the second line, and resize the row height to 25.5?
For example, I always will have text on merged cells B7:C7. I'd like to have a macro that determines if the text goes over C7 (I figured that it would take 46 characters to do this), that the merged cells will be text wrapped, then row 7 will be resized to 25.5.
I know how to record a macro that will text wrap and resize the row height but am not sure how to do the "if" condition.
I have a macro to sort a very large list of materials by a variety of conditions.
The macro runs automatically when a cell on the master list spread sheet is selected and using If/ElseIf it sorts based on what cell they have selected.
Example If ActiveCell = "ALUMINIZED" Then ActiveSheet.Range("$A$14:$K$1945").AutoFilter Field:=2 ActiveSheet.Range("$A$14:$K$1945").AutoFilter Field:=4 ActiveSheet.Range("$A$14:$K$1945").AutoFilter Field:=1, Criteria1:= _"Aluminized"
Which is working perfectly. The problem I am having is that some of the cells are two lines so it looks like this:
BE BEARINGS
I don't know how to get it to launch based on that cell content because of the line break in there. Can I make it launch based on the first two characters only?
I pull an excel sheet from a database each day that gives me sales data for different products sold. The way the spreadsheet comes from the system, the first tab is named "Document Map" and lists all of the products, and each product then has its own tab but they're generically named (Sheet1, Sheet2, etc.). The name of the product is always in cell T8, but the name itself is only the last 8 characters of that cell.
What I'm hoping to accomplish with a macro is two things:
1) Retrieve the last 8 characters from T8 in each tab, and name the tab with those characters (same effect as the formula "=right(T8,8)"). 2) Ensure that this only occurs on tabs with the word "Sheet" in the beginning. Depending on the day, there could be any number of products and they each get their own tab, so I was hoping to be able to get the macro to only work on sheets where the first 5 characters are "Sheet" regardless of the number after that.
So we have people's names in a table. First name in one column and last name in another column. We have a 3rd column where we can use 8 characters to do a combination of First 5 of Last Name + first 3 of first name. However, if someone's last name is only 3 or 4 characters, we'd then want to take more from the first name to fill out the 8 characters.
So: Charles Johnson -> Johnscha John Smith -> Smithjoh Willian Wu -> Wuwillia
What's the best way to do that without creating some crazy formula with tons of if/thens?
The hyperlink can be activated from a number of difference sheets. Once the user reaches the instruciton sheet they need to be able to scroll around to read it fully but they will not need to enter any data or activate any cells. How can I get it so once they have read the instrucditons they can return to the sheet they started from?
I have a workbook with multiple worksheets. Several have hyperlinks to one sheet called Calculator where users can perform certain calculations using a calculator I have made. I want to use a macro to take the user back to the sheet they were previously viewing before they came to Calculator. In other words if they got there from Sheet2 they go back to Sheet2, from Sheet3 to Sheet3 etc. Is there a way to do this?
When I run my macro, it always ends leaving me having to scroll my worksheet back to the top manually. Is there a way to simplify this by adding a line of code to the end?
New to creating macro's but looking for a macro or a function to get the below results. The first 2 columns are givens and looking to return the 3rd column. Is there any code that can do this?
I have made a function (udf), called linjenr. It's input is an n by n matrix, and it returns a n by 2 matrix. This works fine in the worksheet. However, when I'm trying to use this function (udf) in a sub it fails, i.e. I get the error "object does'nt support this property or method". How do I use an own made function that returns an array in a sub macro? (ctrl shift + enter..)
Sub linjegenerator()
Dim nodematrise As Variant Dim antlinjer As Integer nodematrise = Range("F6:T20") antlinjer = WorksheetFunction.Sum(nodematrise) / 2
If antlinjer < 3 Then MsgBox "This is not a network. Minimum 3 lines." ElseIf antlinjer > 2 Then If antlinjer = 3 Then 'Range("F25:G27") = linjenr(nodematrise) ElseIf antlinjer = 4 Then 'Range("F25:G28") = linjenr(nodematrise) ElseIf antlinjer = 9 Then Range("F25:G33") = WorksheetFunction.linjenr(nodematrise) End If End If 'antlinjer = WorksheetFunction.Sum(nodearray) / 2
i have an excel doc. with three sheets... i may start that macro on any sheet any cell the 1st thing that macro does is to go to sheet1 and after running the whole code it calls another macro... macro2
now macro2 runs in an infinte loop and runs evry 20 sec. the problem is every 20 sec the cursor comes back to sheet3.. i want the macro to return to the place where the user was working.. best wud be that the user can work seem lessly...
I'm trying to match the data in a column from one excel file with another column of data from a separate excel file. If there is a positive match, I would like it to return a cell data on the same row in the following column on the first excel file. I understand that this can be done with vlookup but because the data in separate excel file contains commas and thus I can't get a true match.
To visualize what i'm trying to illustrate, i'll breakdown into steps.
I would like to match the data below in excel file 1 to excel file 2.
Lets imagine that there is another column of data next to the list of data in excel file 2. If there is a positive match between excel file 1 to excel file 2, i would like to return cell next to the data in excel file 2 on the same row.. to the cell next to the data in excel file 1.
This is difficult as I have a column with few thousands of such data.
I have a macro already made to protect all the worksheets upon leaving. One thing I have noticed as I go into these workbooks is it opens on whatever page you left or saved on. I would like it to open up to the first sheet, which is my master.
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets Select Case ws.Name Case "Delta Crew" ws.Protect "delta"
I have a macro that selects various cells whilst it runs - nothing unusual there! What I'd like it to do is return to the activecell immediately prior to running the macro. So if cell 'B34' is active and then the Macro runs it currently returns to cell 'A12' (the last selection in the macro).
I would like it to return to cell, in this instance, 'B34'. The activecell could of course be any reference within the spreadsheet, so whatever is active prior to running macro is returned to when macro is complete.
