Larger Than & Comparison Not Working When Used With A String
Apr 15, 2009
I have a macro that should be exceptionally simple and works fine up until the point where I try to use an If statement with a conditional.
I have run the macro up to the If statement, at which point FinalRow is 40 and StartRow is 6, so the If statement below should run but it doesn't. I've had this problem before and just given up, the only thing I can think of is that the If statement only compares the first digit in the String.
I've only copied a small part of the code as I don't think the rest is relevant and just muddies the page but I can paste the rest if needs be...
View 3 Replies
ADVERTISEMENT
Jan 23, 2008
I'm trying to extract a first name and surname from a username :
CN=Joe Bloggs/OU=stneots/OU=EU/O=SAC needs to read Joe Bloggs
CN=Fred Smith/OU=stneots/OU=EU/O=SAC needs to read Fred Smith
CN=Ray Jones/OU=stneots/OU=EU/O=SAC needs to read Ray Jones
The string is always in this format and the name always has a space between. Given a day or so I could probably figure it out, but unfortunately deadlines are breathing down my neck,
View 9 Replies
View Related
Apr 9, 2014
I'm trying to lookup a 10 digit number against a string of numbers seperated by commas. And then return the Carrier Name and On-Time
LOOKUP TABLE
Order #(s) Carrier Name On-Time
5082940535,5082940507 Freight Lines Yes
5083055781,5083056150,5083056098 Ocean X No
Order # Carrier Name On-Time
5082940535 ? ?
5083056150 ? ?
5082940507 ? ?
5083056098 ? ?
View 5 Replies
View Related
Jan 16, 2013
I have a column of cells with compound if statements like this:
VB:
=If(And(N10,K10<>""),"X","")
I'm trying to conditionally format this column of cells so that if the cell changes from this formula it'll turn orange. I've tried different variations of the following:
VB:
=N10<>"=IF(AND(N" & ROW() & ",K" & ROW() & "<>""""),""X"","""")"
=mid(N10,1,len(N10))<>"=IF(AND(N" & ROW() & ",K" & ROW() & "<>""""),""X"","""")"
=left(n10,len(n10))<>"=IF(AND(N" & ROW() & ",K" & ROW() & "<>""""),""X"","""")"
Basically, I'm trying to do a string comparison of the formula contained within a cell (N10) to a string that is assembled on-the-fly. If they don't match, then the cell should fill orange. I think I'm most of the way there, but can't quite get it to work. While this is simple to do with VBA, I'm rebuilding a lot of the functionality of this sheet using the functions available through the Excel UI -- users always forget to enable macros and always save copies as xlsx.
View 2 Replies
View Related
Dec 30, 2009
I am currently using this code which works:
If Me.txtNSNumber.Value = "1" Then
Me.Height = 198
End If
This is the original code I found and believe it will give me what I am trying to achieve (I have tested and works):
Dim ctl As Control
For Each ctl In Me.Controls
If Left(ctl.Name, 3) ="txt" Then
ctl.Text =""
End If
Next ctl
I have tried to modify to suite my requirements however it fails to work correctly (no error messages just does nothing). Basically I am trying to combine them so that the various textboxes named as txtNS1 (with varying characters after the numerical digit) would clear the data dependant on the value of txtNSNumber.
This is my attempt at modifying (from my basic knowledge) which I have tried to altered the string start and character elements without success:
Dim ctl As Control
For Each ctl In Me.Controls
If Me.txtNSNumber.Value = "1" Then
If Mid(ctl.Name, 6, 0) > "1" Then
ctl.Text = ""
End If
Me.Height = 198
End If
Next ctl
View 9 Replies
View Related
Feb 27, 2014
I have the following data
A1 - 65.23
A2 - 1923.52
A3 - 945.18
A4 - 182.54
In column B
B1 =exp(A1)
B4 =exp(A4)
both are fine, however
B2 =exp(A2) - it is showing #NUM
B3 =exp(A3) - it is showing #NUM
Same case for all larger numbers
View 1 Replies
View Related
Feb 5, 2009
I am looking to return the larger value of 2 cells into a seperate cell.
View 2 Replies
View Related
Nov 26, 2012
I have 83,33 value. If I like to increase into 84 and not 83 without decimal point
View 11 Replies
View Related
Dec 12, 2006
I want to do is import a TXT file that has about 70000 rows of numbers into excel. I know it has a max of 65536 so I would like it to import the numbers and put them into 2 columns C and the rest
in D.
Sub ImportLargeFile()
'Imports text file into Excel workbook using ADO.
'If the number of records exceeds 65536 then it splits it over more than one sheet.
Dim strFilePath As String, strFilename As String, strFullPath As String
Dim lngCounter As Long
Dim oConn As Object, oRS As Object, oFSObj As Object
'Get a text file name
strFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please selec text file...") ........................
View 9 Replies
View Related
Aug 15, 2006
I know function Vlookup finds the largest value less than or equal to the given value. However, If I want to finds the smallest value larger than or equal to the given value, How can I do it? assuming I do not want to use VBA.
View 5 Replies
View Related
May 1, 2009
I have Excel file A with a full list of IDs that were selected to take part in a study - about 5000 IDs with only one field "ID". I also have Excel file B with about 1500 ID of people from file A BUT who already took part in the study. Now what I need to do is filter out from file A all the people in file B so in file A I am only left with those who didn't take part.
View 2 Replies
View Related
Jun 7, 2013
I'm having some problems sorting a list of numbers that some are larger than others, but it would sort it by how large the number is in regards to 123 or 45... 45 would go first instead of 123... I tried to change the column to numbers but it still won't work. as well as in the custom sort section. I'll try to upload a sample...
View 11 Replies
View Related
Nov 10, 2008
I am currently using the following formula:
=SUMPRODUCT(('Step 1_Data_Current Qtr'!T2:T10000=B2)*('Step 1_Data_Current Qtr'!C2:C10000<>"")*1)
...to count the number of instances in a data set where:
T2:T10000=B2 is counting records where the value in the cell is set to B2
and
C2:C10000<>"" is counting records where the value in the cell is not blank.
The current formular is working, but I want to change the formula so that the values in C2:C10000 is looking up specific names (in this case, physicians) and not just where the field is not blank. How can I change to lookup physicians in a specific list that may change, located in a separate tab from the main data set?
View 11 Replies
View Related
Nov 25, 2012
I have a table that receives the number sequence 1 and sequence number 0.
I need to calculate the highest sequence numbers that repeat in Secunia lines.
A
B
C
D
E
F
G
H
I
J
K
L
Result
1
1
1
0
0
1
0
1
0
1
1
0
3
[code].....
See for example the last line.
1 1 0 0 1 0 1 1 1 1 1 0 = 5
I have a sequence number 1, where it repeats 5 times segudamente.that is greater than other sequences.
View 3 Replies
View Related
Jul 11, 2007
I have a form in excel that has a box which prompts you to add a picture when you click on it. It then resizes the picture so it fills the box. The file with no pictures is 2.3 MB. The picture is 700 KB. The file with the picture is 24.5 MB and 49 MB with 2 pictures. I need to E-Mail the form to other people but I can't send anything larger than 22 MB. I tried using th compress pictures option on the toolbar but it only took it down to 23.9 MB for a file with one picture. Why is it doing this and how can I make the file smaller?
View 9 Replies
View Related
Aug 7, 2007
I have 5 columns AS-AW. Column AS contains a letter (either A, B, C, D, E, F, or G) which is generated by a VLookup formula. Columns AT-AW contain numbers (from 1 to 6) generated by a conditional formula. I need column AX to combine column AS with the smallest of columns AT-AW. Is this possible?
View 9 Replies
View Related
Apr 8, 2008
Each time he clicks on a command button, the size of the command box grows larger and larger until it eventually is covering over the display area of the report. Also, the other buttons along with the one he clicks grows larger.
View 9 Replies
View Related
Sep 19, 2006
I hope my title was clear enough, but I’m not sure how to set this up. I have numbers in cells B4 and B13. I want to take the largest of these two numbers and multiply it by 0.85. I then want to take this number and compare it to a number in cell B23. If B23 is greater than the larger of B4 and B13 X 0.85, I want B33 to display “No” in a boldfaced red font. If B23 is less than the larger of B4 and B13 X 0.85, I want B33 to display “Yes” in a normal black font. This formula will be in cell B33.
View 5 Replies
View Related
Mar 5, 2007
I have a command button in a spreadsheet that for some reason each time I click it, the button and text gets larger. I can edit the button to shrink it down again, but can't stop the growth...
View 6 Replies
View Related
Feb 27, 2014
I have a set of data in column A that looks like this:
White:0:0|Counter Height:0:0|Orange:40:0|Counter Height:40:0|Green:40:0|Counter Height:40:0|White:40:0|Bar Height:40:0|Orange:80:0|Bar Height:80:0|Green:80:0|Bar Height:80:0|
The delimiter "|" separates the unique values of an item (in this example they are dining stools of differing heights and colors), I need to only keep the lower value and remove the larger one.
The example above contains both White:0:0 and White:40:0 as two different values associated with the same dining stool. I need to only have White:0:0 remain and White:40:0 to go away.
View 2 Replies
View Related
Dec 12, 2012
I need a formula that states: If the number in A1 is larger than the number in C6, but smaller than the number in B6, select the number in D6. But, if the number in A1 is larger than the number in C5, but smaller than the number in B5, select the number in D5 and so on through Row 2.
A
B
C
D
1
#
2
0.4
10.0%
[Code] ....
View 2 Replies
View Related
Apr 20, 2009
I am trying to use the codes below to find text "xmxy" and "xmx" within a column, then try to move the cells between the texts to the right by one column. I believe that the problem for the code below is that
Cells(j, "B").Insert (xlShiftToRight)
or If Cells(i, "B").Value = "xmxy" Then has limitations. I read that when you dim a variale as integer, it only can contain value between -32,768 to 32,767 . But I have more than 32,767 rows of data. I have already set dim i,j, etc. as long, but how do you set the cells range to recognize number larger than 32,767?
Sub ShiftRightbbb()
Dim i As Long, j As Long, lastrow As Long, rowscount As Long, count As Long
lastrow = Range("b" & 65000).End(xlUp).Row
For i = 1 To lastrow
If Cells(i, "B").Value = "xmxy" Then
For j = i To lastrow
Cells(j, "B").Insert (xlShiftToRight)
If Cells(j, "C").Value = "xmx" Then GoTo Nextgroup
Next j
Nextgroup:
End If
Next i
End Sub
View 9 Replies
View Related
Dec 31, 2013
I have sheet containing a schedule of data. It is a record of changes on a project, each change is sequentially numbered and contains a row of data (date of change, whether approved, cost of change etc). Each change is given a cost centre reference (1 of 10 cost centres are being used) depending on the type of change being made.
I need to keep that record of the overall schedule of changes but I also want to extract each of the cost centre categories into a sub table on the sheet to give a schedule of changes against each category.
I have almost achieved what I want but one method leaves blanks in the sub table which I want to avoid and the other method I used to remove the blank lines but needs the first method sheet calculated before it then goes to work and seems rather cumbersome and I suspect there is a much easier method. (I also want it to be compatible with excel 2003 so it can be used across several platforms.)
View 7 Replies
View Related
Jan 13, 2014
I had a spreadsheet with 17 photos on, one in each cell. I had to resize so you can see them all on the screen. If you have some pictures that are resized to fit in cells, is there any way to double click (or hover?) them and make them actual size or full screen when you want to view them in detail?
View 2 Replies
View Related
Jun 11, 2009
I have a spread sheet with 4 columns x 20,000+ rows running this forumla
=IF(FALSE=ISERROR(VLOOKUP($G2,$B:$B,1,0)),$G2,$H3)
The formula works perfect - the only problem is the calc times can take 10+ minutes ( and longer with larger data sets ) because the data sets are so large. Is there another formula or way to get the same result that would speed the process up?
View 9 Replies
View Related
Feb 19, 2014
I have two rows of data, each row also have similar data. I need to find the number of matches that the two lines For example :
7,7,K,A,8,7
K,6,7,7,A,9
To reach the conclusion I need to build six such functions:
=1*(COUNTIF($A$65:$F$65,A66)>=COUNTIF($A66:A66,A66))
=1*(COUNTIF($A$65:$F$65,B66)>=COUNTIF($A66:B66,B66))
=1*(COUNTIF($A$65:$F$65,C66)>=COUNTIF($A66:C66,C66))
=1*(COUNTIF($A$65:$F$65,D66)>=COUNTIF($A66:D66,D66))
=1*(COUNTIF($A$65:$F$65,E66)>=COUNTIF($A66:E66,E66))
=1*(COUNTIF($A$65:$F$65,F66)>=COUNTIF($A66:F66,F66))
The result is 4
Is there a single formula that will give the result ?
Another question, is there a formula that I can compare to the principle of one line against the 20 lines ?
View 8 Replies
View Related
Jun 12, 2009
Compare 2 columns of data and have a new 3th column tell me the result of the query based on some conditional information. So, here's an example.
Column A = Programmer Assigned (Yes/No)
Column B = Project State (concept, plan, develop, qualify, rollout, etc)
I'd like to compare the values in A to B and have the formula tell me if the programmer should be assigned in that phase or not. So, in english.
If A = No, then look to see if B = "develop" or "qualify", if it does, populate C with "Update required". If it doesn't, populate with "Correct".
I hope I explained it well enough for you all to help. I originally looked into using conditional formatting to just format the cell differently if I needed to perform an action.
View 3 Replies
View Related
Oct 24, 2008
to compare the values of data on one sheet (sheet1) with that of another (sheet2) to see if there has been any changes between the sheets over the previous week. If a macro could go through my data on sheet1, compare cell-by-cell the data that's in sheet 2 and highlight in red font any cells that have different values that would be awesome.
The macro would need to leave the "fill color" of the cell as it was.
View 14 Replies
View Related
Mar 6, 2009
I have two spreadsheets, The spreadsheet #1 has the information of two years and the Spreadsheet #2 is a montly report. The Spreadsheet # 1 hasta on the column A a number of transaction, and at the column Q the invoice number. The Spreadsheet # 2, only has in the column A the Invoice number. I need to find in the Spreadsheet # 1 the Invoice numbers that match the Invoices that I have in the Spreadsheet # 2 by bringin the "Number of Transaction".
For example:
Spreadsheet 1:
Has on column A3 the Number of Transaction # 0123, and at the column Q3 the Invoice number 555. At the Spreadsheet number # 2 I have the INvoice # 555 located on A10. I need to know what is the transaction # by adding a formula in a new column (G) so I want to have the Transaction # 0123 in the new column added G.
If I do this manually it will take me hours since these report and the Master is so big, and right now I am doin it using Ctrl-F
View 15 Replies
View Related
Oct 11, 2011
I have two columns of dates. I want to see if there are duplicate dates and highlight them in the first column. What is the easiest way to do this?
View 9 Replies
View Related
