I have successfully generated my code to validate the 60 + text boxes in a MultiPage UserForm. Now I need to identify those TextBoxes that require validation. To aid me I have distinguished those which need to be validated by naming them with a unique suffix, (i.e., _Text vs. _Txt). This code will participate as a Boolean within a With statement as part of the identifying validating code. I realize that a
Right(?, 5) = “_Text”
statement should do the trick but I can’t seem to correctly get the appropriate syntax to use within the parenthesis. Any suggestions? Is there a better methodology out there to accomplish this?
I have previous been given the below macro from here that splits a string of text in one cell into groups of 30 and then puts them into several cells, works perfectly.
What I would like to incorpate now is the ability to overide the point at which it splits the text. i.e. if the inputter puts a "|" (for example) in the original text, this will cause a split in the text and it will restart its 30 count from this point onwards.
Example of what I would like to achieve:
Cell A1 = I would like to change this string of text into groups of 30, where this appears | I would like it to start a new split of 30s from this point on wards and again if another one of these | appears in the text.
Cell B1 = I WOULD LIKE TO CHANGE THIS Cell C1 = STRING OF TEXT INTO GROUPS OF Cell D1 = 30, WHERE THIS APPEARS
How do I create a macro to sort rows into groups, add break rows in between & insert a product sourced from another excel workbook onto the break line?
I receive a spreadsheet each day with a series of requests, most of the information is the addresses & names etc with the important data in columns K,M & O. Data in each of these columns is a code that refers to a product and information sheet to be sent out to the person requesting it. They may want just one in which case data will only be in column K, if they want two there will also be data in M and if they require three there will be data in column O as well. The bit I do know is how to sort the important data into groups of the same by the country (column I) and the product sku's.
Whilst I can do the sort as a recorded macro I really need it to be part of a larger macro that does the following bit I can't get my head around. I need to insert a break row above each group then by cross referencing the data in the product sku columns to a separate workbook insert the appropriate item / letter combination onto the break line row, telling the volunteer which items to send out. This is a v look up?
I have attached an example of the files we work with, the first tab is with the data simply sorted and the second is what I need it to look like. I normally receive it as a CSV file, there are around 8000 possible combinations of product and instructions and the files are normally around 4000 records long. At present we insert the break lines and copy/paste the information manually, seriously slowing down the process.
I have a spreadsheet with sheet numbers that are controlled by the tab name. Is it possible to limit how people name the tab? For example, my sheet numbering must be: 101, 102, etc. Thus the tab would of course be 101, 102, etc.
Within the ComboBox properties, is there anyway to control after "enter" his hit, you move to the right instead of down (similar to the edit under Tools/Options)?
I have created a userform within VBA which has a TreeView Control and a Spreadsheet control on it.
I have populated the TreeView control with data and what I want to be able to do is to drag the nodes off the TreeView control to the spreadsheet control.
I can drag onto a normal worksheet but not onto the spreadsheet control (the no drop mouse pointer keeps showing).
How do I determine which control the user is currently modifying on a multipage form (either changing, enterying or exiting the specific control). when I use "userform1.activecontrol" i get "multipage1" as the control name but I need the actual control on the specific active multipage. (also the .TABINDEX is for the multipage regardless of the on-page control) I use a generic data-field change SUBroutine so need the control name (and the TABINDEX) to provide my SELECT CASE. (so every fieldname_CHANGE calls the same SUB [with no parameters])
So I have a list of integers in col A that is a truncated list from col B. I want to select the units in col B that do not appear in col A. I tried this statement
=IF(A2=B2,A2,0)
Since both lists (A and B) are ordered from smallest to largest this only worked until a value that is not in ColA appears in ColB and then all the cells following = O.
I have a column of cells, of which certain values have the prefix "EmployeeID:". I'm trying to identify the cells containing the prefix by evaluating each cell using activecell.offset and value to no avail. I've also tried to use the selection. find, however the first instance of the prefix is continually selected, and does not move on. I'm assuming that I must use the findnext feature, however I'm not having much success.
I am trying to develop an IF formula based on 2 variables;
1. The value in column Q = 4 2. The value in column AA is between -10% and 10%
When both are true, it should return a value of "YES" (or "NO" when untrue). I am having trouble with the range part. It could be that the range spans negative and positive integers, but I'm not sure. Here is what I've got at the present time:
one table (TABLE1) contains the name and a mile point a
the other table (TABLE2) contains name, mile point 1, mile point 2, and other data
I want to be able to pull data from TABLE2, based on TABLE1's name and MP. I want to be able to select data from TABLE2 whose name and mile point's fit with the data from
TABLE1 Line MP A SYS
100 8 ?
TABLE2 SYS Line MP1 MP2 DATA
A 100 0 5 BOB
A 100 5 10 KON
B 100 10 15 B
I want to be able to replace the ? from table1 with the correct SYS from table2 based on the MP given
I started with something like: =if(and(name=name,mpa=>mp1,mpa=<mp2)),vlookup...
The problem is this doesn't work because im comparing one to many.
I've collected some data from a GPS logger regarding the speed of an athlete. I want to calculate how many sprints this particular athlete undertook during a training session.
Sprinting is defined as a speed of > 20 kph.
One sprint would be the attainment of one peak >20 kph before decreasing below 20 kph.
It's easy to identify the 3 peaks and thus sprints from the xy scatterplot in the attached file, but I'm struggling to find a way to calculate this.
I'm trying to identify duplicates with a return of "True" or "False". The attached workbook has column F for results (to read PI2 A if in column L, PI2 B if in column R or Both), but I want to query if everyone in column E is in column L or is in Column R and if so which one.
i have a spreadsheet that contains dates spread across different rows and columns. How can I identifying dates for this week and next week by colouring the cells?
example dates that fall within this week will be red, next week will be blue.
In Sheet1, If I have Group IDs in Column A and values in Column B as below, is it possible to create a summary in Sheet2 where the MIN value for each group ID is returned?
I have a list of variables created by a user like this:
BAND ID START END Band1 06:00 08:59 Band2 09:00 15:59 Band3 16:00 17:59 Band4 18:00 22:30 Band5 22:31 24:29 Band6 24:30 05:59
Seperately I have data from a third party like this:
DATE TIME VALUE 18/05/08 17:22 10 19/05/08 02:15 1 20/05/08 07:14 5 20/05/08 21:36 15 20/05/08 25:43 1 21/05/08 19:15 15 22/05/08 18:26 16 22/05/08 24:59 2 etc.
I need to be able to identify which BAND ID belongs to each TIME in the list so that I can sum the VALUE figures for each BAND ID (e.g. the first time given,17:22, would belong to Band 3). have tried using LOOKUP but I can't seem to get it to work. Additionally, I have a problem with the 30 hour clock. Would I have to stick to a 24 hour clock? This would mean that I would have to find each time from the list that was between 24:00 and 05:59 and allocate it to the previous day because it is important to keep the values that I ultimately derive in the right day of the week.
Our unique identifiers are from SF.com... they have created unique Account ID's.
Comp Name Unique ID Company A - 5102AA Company B - 5102Aa
The problem: Excel does not recognize the capital A and the lowercase a as being different. I need to pull this data out and bounce data across tables for analytics.
The Question: Is there any way I can get Excel to recognize 5102AA and 5102Aa as being DIFFERENT ID's?
In column H I have a list of numbers seperated by a space, the number of lines can change. In column L I have a list of numbers which can change either expand or retract.
I would like to check each cell in column H and if any numbers are not listed in column L then it/they should be shown in column G.
Example1 H2 shows 6 11, therefore cell G2 should show 11.
Example 2 H6 shows 5 6 9 11 therefore G6 should show 9 11
Sheet1 HIJKL1Container ID26 11 135 8 11 245 7 11 355 7 565 6 9 11 675 6 9 Excel tables to the web >> Excel Jeanie HTML 4
I have a worksheet, where in C3:C100 (or even higher) i will have text strings. A lot of these will be repeated, and i want to pick out just one instance of each text string and display it in E3:E10 (or higher).
So, for example, if i had the following values in column C:One Two Two One One Three Seven Two Four Seven One Three
I would want the following values in column E:One Two Three Seven Four
Here is the deal I have 4 columns. Each line gives you the following information: The Id is the record number, Code_Name is a code for each Fox in the study, date and area is a sub area in a bigger grid. Basically I have an area divided by squares and every time a marked fox enters in one of my squares a new line in the data is created. What I won’t to know is if a fox when in my area of study will return to the same squares or not.
ID
CODE_NAME
Date
Area
Logical 116
SSS1 02-Jan-09 1A1
273
RRR1 02-Jan-09 2A2
2959
BBB1 02-Jan-09 1B1
2959
What I’m trying to achieve is a 5th column were ill get a logical value of TRUE or FALSE if, for each fox in the next available record a fox went back to the same square or not. So if you check for the fox RRR1 I have 2 records one in 2 of January in area 1A1 and a second in 7 of January in the same area. For the Fox BBB1 you will see that she was always in different areas and for CCC1 she only came back to one square.
The problem is I have over 400 fox’s and 12000 records and I’m trying to get a way of doing it automatically.
When using the formula '= CELL("format",G3)' the result is ',2' for Euros, US and Canadian Dollars. As you can see in my spreadsheet this does not enable me to calculate sums (ie. Total Canadian Dollars) using the currency format. Is there something other than "format" that would get a more precise result that would differentiate between currencies?
I'm trying to set a range to a particular cell in Col D but I don't know the row number because this can vary depending on the amount of data entered. At present my code looks like this
Dim MyCount As Integer Dim r1 As Range 'No of rows in Col D MyCount = Range("A2:D2", Range("A2:D2").End(xlDown)).Rows.Count Set r1 = Range(Cells(MyCount, 4))
