Identifying Groups Of Control Names
Nov 4, 2006
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?
View 9 Replies
ADVERTISEMENT
Jul 30, 2009
If I have a list in Collumn A such as:
1
1
2
2
3
4
5
5
6
6
Is there a simply excel function I can use so that in collumn B I return only the unique results. That is, collumn B will look like this
1
2
3
4
5
6
????
View 9 Replies
View Related
May 28, 2014
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
[Code]....
View 2 Replies
View Related
May 21, 2014
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.
Orders Example EUR RM.xlsx
View 12 Replies
View Related
Feb 20, 2009
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.
View 9 Replies
View Related
Mar 4, 2014
Is there any way in VBA to refer to a control in its own event procedure without referring to it by name/hard-coding?
It might be clearer to explain by a dummy code example:
[Code] ......
I'm seeking what I would need to replace Line1 with.
View 11 Replies
View Related
Jan 9, 2008
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)?
View 9 Replies
View Related
Jan 12, 2007
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).
View 4 Replies
View Related
Oct 4, 2007
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])
View 3 Replies
View Related
Jun 7, 2013
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.
View 4 Replies
View Related
Jun 29, 2007
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.
View 2 Replies
View Related
May 14, 2014
I have some data that has names but is not consistant i.e
Smith, John
Smith,John
I want a formula that says, if there is a space after the comma do nothing, otherwise insert a space between.
I have figured out the second part so my formula will look like
=if(some logic to identify a space after the comma ,"",already figured out this bit)
View 5 Replies
View Related
Aug 1, 2014
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:
=IF(AND(OR(Q2=4),OR(AA2<0.10)),"YES",IF(AND(OR(Q2=4),OR(AA2>-0.10)),"YES","NO"))
View 3 Replies
View Related
Apr 17, 2014
I have two tables,
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.
View 3 Replies
View Related
May 5, 2009
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.
View 11 Replies
View Related
Sep 30, 2009
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.
View 3 Replies
View Related
Oct 10, 2009
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.
View 7 Replies
View Related
Jun 10, 2013
I want to sort a spreadsheet based on whether any of the numbers '1', '2' or '3' appear in a particular column.
There are 2 particular rows which I care if they have a '1', '2' or '3' in them, let's say row 17 and 18.
I want to create a formula say in row 20 which would tell me if there is a '1', '2' or '3' in the corresponding cells in EITHER (!) row 17 or 18.
An example to clarify:
Cell A17 = 1
Cell A18 = 2
Then Cell A20 would be = 1 because both A17 and A18 have a '1', '2' or '3' .
2nd example:
Cell C17 = 1
Cell C18 = 0
Then Cell C20 would be = 1 because C17 has a '1', '2' or '3' .
3rd example
Cell D17 = 6
Cell D18 = 12
Then Cell D20 would be = "" (Null - no value) because neither D17 nor D18 has a '1', '2' or '3' .
Note: I am looking just for the numbers '1', '2' or '3' not (!) numbers like 11, 12, 13, 21, 22, 23 etc...
View 4 Replies
View Related
Feb 5, 2010
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?
Sheet 1
A B
1 1
1 4
1 5
2 9
2 3
2 7
3 5
3 2
3 6
Sheet 2
A B
1 1
2 3
3 2
View 12 Replies
View Related
Nov 24, 2003
I would like to write a macro to examine a range of cells, and over-write some of those cells with a zero.
I want to over write those cells with either a numerical constant, or a formula with only constants, e.g.
"12345" or "= 123+567-(2*5)"
I do NOT want to over write cells that contain links or any functions:
"=page3!a5" or "=sum(a1:b6"
View 4 Replies
View Related
Mar 15, 2013
Is there a way in Excel to identifying certain combinations of numbers? If tried every IF statement I know.
I have an Excel with two columns of numbers and I need to identify their combinations.
18
18
18
19
18
19
18
20
18
18
18
20
The combinations 18 and 18 would return a "LOW" message in the third column, the combination 18 and 19 would return a message "HIGH" etc.
View 2 Replies
View Related
Sep 3, 2008
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.
View 9 Replies
View Related
Nov 13, 2008
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?
View 9 Replies
View Related
Nov 25, 2008
I'm using a cells.find command to locate a value in a file. How do I return the current row number that I'm on following the command?
I'm guessing it is something along the lines of:
MyCurrentRow = ActiveCell.RowNumber
but I know that that is an invalid statement.
View 9 Replies
View Related
Feb 10, 2009
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
View 9 Replies
View Related
Mar 24, 2009
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
View 9 Replies
View Related
Aug 6, 2009
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.
View 9 Replies
View Related
Jun 20, 2006
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?
View 7 Replies
View Related
Mar 11, 2007
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))
The problem is with my line
Set r1 = Range(Cells(MyCount, 4))
View 2 Replies
View Related
May 17, 2007
Sheet 1, column A, has invoice numbers from 1 to whatever is needed for the month.
How can I get Sheeet 2 (the following month’s) to automatically start numbering from wherever Sheet 1 leaves off?
View 3 Replies
View Related
