I have tried many ways to get script to do the following without success. I need a vba formula that identifies claims in a Range that are not 12 characters in length and do not have "00" at the beginning & end of the number. The format of the number should be "00########00".
View 3 RepliesMy worksheet contains data with the reaction times on a psychological test. Each respondent in the test has 280 rows in my excel sheet.
The 'perfect' length of the row, is from A to M. When an error is made in the test, the length of the row will increase. So the error length can be A to AA.
For me it is important to analyse the error. So I would like to give a perfect row length, the value 1, and an error row length a value 2.
So, in conclusion:
If:
Cell length = A1 - M1? --> Copy A1 B1 C1 (A B Cof that row) to Sheet3, and give D1 in sheet 3 the value 1
Cell length >= A1 - M1? --> Copy A1 B1 C1 (A B C of that row) to Sheet3, and give D1 in sheet 3 the value 2
I'm trying to avoid using merged cells or text wrapping with altered row height to display some text in multiple rows - similar to using centre across selection to have a header across multiple columns without merging. The guys that use the sheet type a comment that is relevant to five rows and the easy solution here would be to just type the first portion of the comment in the first row (about 30 characters will display in the column width available), then put the next 30 characters in the next row and so on but the guys keep getting lazy and merging the cells so they can type the comments more easily. I can lock the sheet or force validation but I think there's a better solution.
I can effectively "wrap" the text across the five rows the header is relevant to by using a formula to pick up everything except the first 30 characters of each cell. Ie if they type whatever they want in cell B16 then I can use this formula to break it in to 30 character lengths to "wrap" it in to the next four rows:
=IF(LEN(B17)>30,RIGHT(B17,(LEN(B17)-30)),""),
And I'm sure it would be easy enough to use search with the formula to break it where there is a space in the text so partial words don't flow over.
BUT because the text ends up slightly different widths I want to use formats to force only 30 characters to display (whilst keeping the remainder of the text string intact). I can't figure out the syntax to format only 30 text characters to display but you can easily do it with numbers and dates and so on.
But I've read that the new versions of excel allow for up to 64 nested if functions. I need to nest about 5 if functions, but after I write out the 3rd excel says there are too many arguments.
=IF(INDEX(vlookup!$D$2:$D$157,MATCH($F16,vlookup!$B$2:$B$157,0))="CPM", INDEX(vlookup!$E$2:$E$157,MATCH($F16,vlookup!$B$2:$B$157,0))*(J16/1000), IF(INDEX(vlookup!$D$2:$D$157,MATCH($F16,vlookup!$B$2:$B$157,0))="CPA", INDEX(vlookup!$E$2:$E$157,MATCH($F16,vlookup!$B$2:$B$157,0))*(L16), IF(INDEX(vlookup!$D$2:$D$157,MATCH($F16,vlookup!$B$2:$B$157,0))="dCPM", 0.04*(j3/1000))), IF(INDEX(vlookup!$D$2:$D$157,MATCH($F16,vlookup!$B$2:$B$157,0))="dCPC", 0.008*k3, "calc")
I created a macro on an Excel spreadsheet:
Sub Macro1()
Range("C11:D19").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
Range("E5:E20").Select
Selection.NumberFormat = "0.00%"
Range("D24:E43").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=24
Range("D58:E339").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=57
End Sub
When the macro is run, it works with the exact amount of data that I've entered.
However, because I have specified a specific range, if I add a row or column of data to the spreadsheet and run the macro again, it throws the whole thing off (certain cells get formatted when they shouldn't and others aren't formatted)
How do I tell the macro to look in the spreadsheet for varying ranges of data and format those cells?
I have a chart in my spreadsheet that shows number of claims per defect. Is there a way to format this bar chart so that it will only show the top 5 automatically, even when they are constantly changing? There are a total of 13 different catagories.
View 9 Replies View RelatedThis should be pretty straight forward but the solution has escaped me so far. I have some formulas that ratably spread monthly budgets across the life of a program. Ocasionally these budget formulas are hard keyed over with a value which overrides the ratable budget formula. I would like to conditionally format all cells that contain hard keyed values so they don't get inadvertently copied to new budget lines.
View 8 Replies View RelatedI've set up a filing system which saves sheets/ workbooks based on the value of a cell - Range("B1") Everything works great apart from when ThisFile String length exceeds 31 characters which you may know is the max useable character length for a sheet name - I had no idea! 8-0
Is there a way i can check if string length exceeds 31 characters then, if it does, shorten it to 31 characters?
[Code] .....
How to create a code formula to calculate the arc length from a given chord length?
If you know the radius of the major circle.
Say the chord is 50mm and major circle dia is 72mm (radius 36mm)
arc from chord.jpg
I get all my results from a CSV file. This isn’t a problem except for the first nine days of the month where excel cuts off the first 0 so 01012009 reads 1012009 and cannot be properly concatenated.
DateNameItem
1012009BobKeyboard
1012009SidMouse
1012009DaveMonitor
1012009EdMonitor
1012009AndrewKeyboard
1012009DanKeyboard
1012009BruceKeyboard
What I would like to do is have VB read the contents of the date cell and if it is less than eight characters, edit the cell by adding a zero at the beginning of the string. This needs to be a one off process for all cells. Can anyone come up with code that might make this happen?
I have two columns, one with an account number (call it A) and another with a vendor id (call it B). I need the length of the number in column A to be truncated to 10 characters by just deleting any additional characters after 10. That should be based on whether or not the corresponding cell in column B equals the following number "147212336". Right now, my code edits the ENTIRE column A, not just the cell in the same row.
I know it's because I'm using a "FOR EACH CELL" argument but I don't what else would work.
Code:
Set acctlengthrange = Range("N2", Cells(LastRowB, "N"))
Set dplrange = Range("P2", Cells(LastRowB, "P"))
With dplrange
If cell.Value = "147212336" Then
For Each cell In acctlengthrange
cell.Value = Left(cell.Value, 10)
Next
End If
Next
I'm having a bit of trouble with a macro designed to read the length text in a cell and if it = a certain length then perform an action (in this case Text to column)
Here is a small sample of the data I'm working with:
Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<
Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<
Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<
Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<
Tue 02/11/2014
LastBootUpTime
Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<
*End Sample*
The text length I want it to perform the action on is highlighted with "<<" if the length of text does not meet the required number then I want the statement to skip and move onto the next one.
I have the text to column code already done with relative references however the long text string I want the statement activated on is not always present which means that the pattern (0,3) is not always consistent.
I need to write a Excel VBA (2003) code that can arrange the cells of one column based on the character length. An example is this:
Column Data before Running VBA
(Column A)
cat
oranges
apple
Column Data after Running VBA
(Column A)
cat
apple
oranges
I been doing this manually by using the LEN() command in an adjacent column (Column B) and sorting Column A. based on Column B. However, I wish to do this all in a VBA code that does not rely on how many rows are in Column A. I have dozens of excel sheets with various number of rows for Column A. Therefore, the VBA code has to also figure out when the last non-empty row is in Column A. Can anyone offer their suggestions to my problem?
I have a spreadsheet with numbers like this
9404388
9404374
9404391
9405695
38301663
9409724
1791016
9435145
9530758
9440464
9441182
71000569
9467438
There are a thousand numbers like this, I was wondering how I can move the cells with 7 numbers to the right column, and keep the cells with 8 numbers where they are (or move them to the 3rd column to the right)
Is there a way to change the text in a cell to proper apart from 2/3 letter words which I want to keep as upper? Basically can it ignore all words that are 2 or 3 letters long, but change all other words to proper text?
View 3 Replies View RelatedI would like to know how to figure out the vacation accrual rate based on length of employment.
Lets say for years 0-5 the accrual rate is 3 hours per pay period.
for years 6-10 the rate is 5 hours per pay period
for years 11-15 the rate is 7 hours per pay period
for years 16-20 the rate is 9 hours per pay period
and for years after20 the rate is 10 hours per pay period.
I want a formula that will look at say cell C5 (which is the cell that includes length of service) and output the accrual rate. Example. The number 12 is in cell C5. I would like the cell with the formula (D5) to say the number 7 (which is how many hours accrued per pay period based on the table above.
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)
How to identify the criteria (break or lunch) that he took first based on the time stamps. Here's the sample scenario: I would like to know which one is the 1st break or 2nd break they took based on the Date & Time criteria. See attached file for the sample data.
Sample results:
Agent 001 : 1st Break = 2/21/2014 10:13:00 AM
Agent 001 : 2nd Break = 2/21/2014 7:13:00 PM
Would like to enter a formula in column B of Table 1 (on attached file) that would identify if that customer spent $90 or more (on any one purchaese) for any of their 4-5 purchases from Table 2. Expected results have been entered in Column B of Table 1.
View 3 Replies View RelatedI will be working with an array of data approx 500 rows by 50 columns (eg Sheet1!$A$1:$CV$500) in which I will need to check if approx 1000 unique values exist and in which column they appear in. Each unique value should not appear in more than 1 column. Once identified, I need to return the header value at the top of the relevant column.
My thoughts on approach would be perform a lookup of some sort to identify the column number and then use that within an index formula to return the header value based on the column number.
However, I'm not sure how to perform a lookup to get the column number. A match formula can, as far as I am aware, only lookup against a single column or single row.
I have a file with telemetry data for n individuals. For each individual I have data on a number of days, over the course of a couple of years. On any given day, I have from 1 to several "fixes" for an individual. Fixes range from 1 to 3, with 3 being the "best". Note that I do not necessarily have data for all individuals on a given day. What I want to do is extract the row with best fix for each individual, on each day for which I have data for that individual. If there is more than one best fix for an individual on a given day, then I want to choose the first best fix (temporally). Right now my data is set up with columns:
INDV DATE TIME FIX ....other attributes
INDV = unique code identifying the individual
The data is sorted by INDV, then by DATE, then by TIME.
I figured I could do this using nested IF statements, where "TRUE" would be added to a new column (e.g., HIGHFIX) in the row of the first highest fix on each day for each individual, but have not been successful in doing this. I've also tried conditional formatting. Again, no success here.
I have the following data:
Column A = Date
Column B = Reservations made per day
For ex:
A B
1 3/1/2011 5
2 4/5/2011 10
3 3/8/2011 15
Then I have a look up table where based on the date ranges it assigns a week number.
WeekDATE Range 1Date Range 2
718-Feb-1124-Feb-11
825-Feb-1103-Mar-11
904-Mar-1110-Mar-11
1011-Mar-1117-Mar-11
1118-Mar-1124-Mar-11
1225-Mar-1131-Mar-11
1301-Apr-1107-Apr-11
1408-Apr-1114-Apr-11
1515-Apr-1121-Apr-11
1622-Apr-1128-Apr-11
I am looking for a fomula that would assign a week to the corresponding dates on column A and tha would then add all of the reservations booked for each week.
I would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?
View 5 Replies View Related(I need to do this using VBA so that it can work as an excel macro and/or a vba function in access.)
How can I extract the first occurance of any substring over a certain character length from within a string ?
eg. My string may take any of the forms given below (or something similar), how can I extract the first set of characters that is at least (say) 4 characters long ? i.e. always get the 'Phillips' out of the below...
Phillips Homes Ltd
Mr T A Phillips
TA Phillips Homes Ltd
T A Phillips Homes Ltd
T. A. Phillips Homes Ltd
The names used are only an example (Phillips could be any name whatsoever).I am familiar with Search, Len, Left, Right, Mid, Instr etc. but just can't figure this one out.
I have Sheet with 40 employees who each proposes their work schedule, so I have to give each Employee access to the same sheet and want highlight and unlock only those cells that specific employee can use.
Each employee has to login from a drop-down (sourced from Sheet.Employee Master), so their unique Employee Number is in "A13" of Sheet.LOGIN
Can I identify the ROW number and then use that ROW number in a macro to highlight and unlock specific Range of Cells in Sheet.PROPOSED SCHEDULE?
---where "Sheet.LOGIN("A13") = (the value in the cell Col A:"row" of Sheet.PROPOSED SCHEDULE)
I have attached a scaled down version of the Workbook.
Following code is scaled down-- this is for Employee 02 who appears on ROW 16 of the sheet. (macro is same for each employee, just uses a different row)
Y3:Y500 contains a range of years, 2014/2015 through to 2018/2019, for a total of 5 different values (So conditional formatting is out the window). I'd like code which highlights the rest of the row, Z:FN, in a certain colour depending on the value in Y3:Y500.
Also, where there is data in the cells between Z:FN I'd like that to highlight in yet another contrasting colour.
I want to be able to change the font color of the numbers in a column based on even or odd. I selected the columns and formated them to all be blue... thats the color I want for even, but now I can't figure out how to set a condition that checks for odd numbers and changes them to red. I now have a count of even/odd for all records, and even/odd for the last 30, this provided some very interesting results.
View 9 Replies View RelatedI try create function, which will find based on format value. I mean f.e. when i have in cell function today() it show like 12.01.09. I need compare this date with date, which is create from other function. In my case is it
=VALUE(CONCATENATE("1.";$D$2&".";D3)).
So i want cell A1 (12.01.09 ) search in array (B1:B10), which dates are created from funcion =VALUE(CONCATENATE("1.";$D$2&".";D3)). There is 12.01.09 in B5.
results will move cursor on cell with the same date if there is, or do nothing, if there is not the same date.
Is there any way to do an IF statement that is based on the format of another cell. For example
View 3 Replies View RelatedI have a spreadsheet that I use to track my commissions on a monthly basis from about 8 sources. I have been tracking these monthly commissions from each source separately. In my spreadsheet I have a column that shows YTD total for each row(about 50 rows). Every month I have to go into each formula to update it to the corresponding month as follows:
"=SUM(B3:J3)" then the next month I have to change it to "=SUM(B3:K3)"
What I would like is if row "B-J" represents Jan.- Sept then on October first it will automatically sum the row "B-K"