If UCase(TextBox1.Value & "," & TextBox6.Value) < UCase(currsheetname)
This works most of the time, but for something like:
Smith,Bob < Smith,Jim it will give erratic results. currsheetname is not declared so is a variant I guess.
Is there an easy way to sort variants? I've searched the internet and it seems that sorting works just with ranges
[Code] .....
I have variant M with 20 rows and 4 columns and want to sort the rows according to the fourth row (from the smallest to the largest). I tried analogical things such as:
[Code] ......
which does not work. I do not want to believe that VBA does not have any internal way (function) to solve variants sorting since it should be quite casual thing..
I need a macro that will change the background colour of a cell depending on whether it equals a certain variant. Let me explain.
Cells D8 to N8 will either equal 1, 2, 3 or 4. Depending on the value of the cells on row 8 (D8:N8) I need the above cells on row 4 (D4:N4) to have a different background colour.
If the cell on row 8 is 1, the cell on row 4 must have a green background.
If the cell on row 8 is 2, the cell on row 4 must have a yellow background.
If the cell on row 8 is 3, the cell on row 4 must have a red background.
If the cell on row 8 is 4, the cell on row 4 must have a blue background.
Therefore, if D8 = 3, D4 will have a red background. If F8 = 2, F4 will have a yellow background. And so on.
I need a macro that when ran changes the background colour of the cell range D4:N4 depeding on the value of the cell 4 rows beneath (D8:N8).
I have created a sheet which gets populated by information but have left out the top row. I want to use the tope row to enter my header fields but was looking to see if there a more effcicent way of doing this.
I am using the following code below but was wondering if there was a way using arrays as variants or ranges to do it more efficiently.
SUMIF function. I am looking to build on this original formula but neither the SUMIF or SUMIFS formulas are working...here what it is:
I am looking to create a formula that will all up the figures based on the criteria within a specific date range - for example by selecting Company A and Workshop in Feb 2011 it will return £1678.75
I've also tried IF functions within SUM functions but can't get it to work.
I'm trying to meet set criteria in b5 using AND the first works if both conditions are met in two cells but bur won't on one cell?
Private Sub PTIColours_Click()
If Range("b5") > 0 And Range("c5") < 0.149 Then
Range("a5").Interior.ColorIndex = 54
End If
End Sub
But this dosen't
Private Sub PTIColours_Click()
If Range("b5") > 0 And Range("b5") < 0.149 Then
Range("a5").Interior.ColorIndex = 54
End If
End Sub
I have tried searching for a good example of how to use logical operators on Mr Excel however the search is frustrated by "Small Words" in the search field ie IF, AND, NOT ,OR are too short to search.
Can anyone list the formulas needed in Excel
+
_
x
divide
I am trying to write some code that will repair errors in a standard workbook. One of the errors is a blank cell that needs to be changed to "325¨¬F ¡Â T ¡Â 350¨¬F". How do I insert these symbols/operators into the VBA code?
View 6 Replies View RelatedIs it possible to set a range using comparison operators? ie. if a number lies within x and y then such and such happens.
View 6 Replies View RelatedI am trying to alter the type of operator used in one formula based on the result of an argument in a separate cell generally as follows: If Cell 1 equals something then use the ">" operator, otherwise use the "=" operator. I appreciate this sounds really simple and I'm having a major brain fade as I cannot figure out how to relate the two arguments as follows: [If(Value 1 "Operator" Value2, then do something, otherwise do something else)] where the operator is pre-defined in another formula.
View 4 Replies View RelatedI'm trying to figure out how to reorganize data based on boolean values in two different columns. My goal is to write something in VBA that will look to see if the boolean in the first cloumn is true then procede to check if the boolean in the next column, same row is true. If that is the case, it will take several values from the row and move them to another worksheet. If the boolean in the first column is true, but the second column is not, it will look down the rows of the second column until it finds true then move several values from rows it has passed through. Here is an example of the sample data I will be dealing with.
Date Time Duration Boolean1 Boolean2
6/22/200715:0032TRUETRUE
6/22/200718:0058TRUEFALSE
6/22/200719:0060FALSEFALSE
6/22/200720:0060FALSEFALSE
6/22/200721:0060FALSEFALSE
6/22/200722:0060FALSEFALSE
6/22/200723:001 FALSETRUE
Based on the example data my goal is to move the data in a new worksheet that would look something like this
Date Start End Duration
6/22/2007 15:28 16:00 32
6/22/2007 18:02 23:01 299
I have Three different combinations which are in cell A & B, These combinations are with similar signs as in A1=2- & B1 = 2- (& A1 = 2+, B1=2+). Then I have numbers with no signs which A1 =2 B1= 2. My approach is to limit to these and these combinations only and use a formula which is uniformally applied on these combination. I want A1 and B1 to take only like signs like -- or ++ or without any sign. The rest shd be ignored. IS there a way of sorting two columns which have different combinations liek -- or ++ or -+ into a area where u can apply a particular formula? Hence first i take a number and move signs on the left and then apply this
IF A7 = 2- . B7 = 3- THEN
IF(OR(RIGHT(TRIM(B7),1)="-",RIGHT(TRIM(B7),1)=" ",RIGHT(TRIM(B7),1)="+"),LEFT(TRIM(B7),LEN(TRIM(B7))-1)*3,B7*3)
Basically I have two sets of data. One will be new each week. I'd like to use the non-changing data as a base to compare new data to. The formula would need to match multiple values, including a 'time between', and then return whether a minimum rate has been met.
SampleRateExamine.xlsx‎
I have two ranges of numbers stored as text on two different sheets.
The first one is from product code and the second is from product category.
The problem is both ranges are of different lengths and I have to find out if a product from the right is part of a particular product category. Even if the length is different the first digits are indicative of the belonging of a code. For example 1234 and 12345 are “family”-their first 4 digits match.
Just to give you an example of what is desired:
Category____ Code
2200 ________22002
2323________ 232347
So, the loop should do the following:Compare the first string from the “Category” column to each and every entry on the right, if a match exists (we have no match here for 2200) write “ok” next to it.
Next trim one digit from the right from every string in the “Code” column.
Compare same first string from the “Category” column to each trimmed string from “Code” column (here we should have a match 2200=2200)
Write “ok” next to it
Now the loop goes to the second string from “Category” column and for this one we will have to trim 2 digits from the right of each string in “Code” column to achieve the result (2323=2323) and so on.
I need to compare the date from the user input and the date listed on excel. How can I compare it? Is it correct? lngCmp = Val( Cells(I, 31))
Dim lngBegin As Long, lngEnd As Long, lngCmp As Date, lngResults As Long
lngBegin = 9 'beggining of data
lngEnd = 232 'end of data
lngCmp = InputBox("Please enter the date", "Begining of the week")
Lngcmp1 = DateAdd("d", 1, lngCmp)
lngCmp2 = DateAdd("d", 2, lngCmp)
lngCmp3 = DateAdd("d", 3, lngCmp)
lngCmp4 = DateAdd("d", 4, lngCmp)
lngCmp5 = DateAdd("d", 5, lngCmp)
'lngCmp1 = lngCmp + 1
'lngCmp2 = lngCmp + 2
'lngCmp3 = lngCmp + 3
'lngCmp4 = lngCmp + 4
'lngCmp5 = lngCmp + 5
lngResults = 0
lngResults1 = 0
lngResults2 = 0..................................
I am trying to create macro in Workbook 1 and that looks up Workbook 2, searches column A (starts in cell A3) and compares value in Workbook 1, column B (starts in cell B30). If the value doesn't exist in Workbook 1, then create a new row in Workbook 1 and copy (or add) info from column A, B and C from Workbook 2.
View 9 Replies View RelatedI am using vlookup to decode binary words. Some of the words begin with at least one zero (eg 00101001) when I enter such a word it is considered a number and removes the leading zeros. The comparison then fails. If I call it text or some such, the vlookup fails because, I guess, it is complaining I'm treating a number as text. If I seperate those words begining with a zero from those that don't and create two tables, each table works fine but this will lead to a lot of ranges and make things messy. How do I get the decoding done with just one range?
View 9 Replies View RelatedI have attached my spreadsheet I am working with. I have not been able to get a formula to work with what I am trying to accomplish.
Need to compare Column B to Column A... If there is a match anywhere in Column A, copy the item in that same row of Column C into the Column D
I have 2 xls file what I want to do to compare the 2 files see attachment
vlookup.zip
On sheet 2 is the main xls ... what I want to compare sheet 2 to sheet 1 and if it's on sheet 2 and sheet 1 if will post the result to sheet 3 pulling all the data to those colum ie
Employee IdInstrument IdQuantity Br Account
95086 Dec 300 511 2046
what has to match is sheet 2 Emp# and Qty to sheet 1 Employee Id
and Quantity
I have two separate lists, one in Column A and the other in Column B. Both lists are roughly 2,000 rows long. For the most part, the two lists are identical, but not quite.
I'm trying to determine what data is present in Column A that is absent from Column B, and vice versa. That's easy enough to do with COUNTIF (at least that's what I've tried), but to complicate matters, duplicate entries appear in both column A and column B.
For example, in column A, the word "Electric" appears 13 times...but in column B, it only appears 8. I need to know these differences in a quick and easily readable way (remember, the spreadsheet is roughly 2,000 rows).
A is a list of numbers 12345, 23456, 34567 etc all unique, ascending sort
B is a list of types, its either 1 or 11 or 3
C is a list of the same numbers as A but they are not sorted and that cant be changed
D is where I need to insert the type (1, 11, 3) for each number in C
Ideally, it will take A2, find it in column Cxxx, and insert the type from B2 to Dxxx and continue doing it for each row
I need to compare add Column A&B in sheet1 and column A&B in sheet two and compare the two result for the difference(like vlookup).
formula to merge two cells in two sheet and compare.
I have two cells which contain text, and I'd like to use IF function to compare between them. Something like: =(IF(A1=B1),"TRUE",FALSE"). But it seems like the function cannot do it with text..
View 3 Replies View RelatedI have two different tables, 1 table gives the conditions you need for specific stations. Stations is shown vertically (10 to 150), and conditions people must have are shown in the top row. (Alphabet). This data is given and can change alot. If I compare this data, a new table will be created that compares the conditions per station with the people if they have it too. Then it will need to give an answer, 'Yes or 'No''. Yes means he has met all the conditions needed, so has all the competences needed for the station.
I tried to do this with normal formulas, but no success. I can do it by doing like 100 IF's in 1 formula, but that isn't efficient.
Also I have done a formula:
IF(SUM(IF(Competentions!$D$21:$BT$21=Competention!$D10:$BT10,0,1))=0,"x",""),
But that only compares if the person has exact the same competences, if he has more then the needed on 1 location, a NO will be returned. Same with count, that doesn't work either, if he has the same amount of competences needed for example station 10, he is allowed to, but he doesnt have the competences that are needed for that station..
The following code gives me error message. There is problem in Format function. How can I write using VBA that "Scantime is less than 08:00"
[Code].......
Using Excel 2003 on XP.
In Workbook1 I have a list of users with a leave start date in column N and a leave end date in column O.
In Workbook2 some of these of users appear with a payment start date in column C and a payment end date in column D.
I need to find out if the any or all of the payment period for these users in Workbook2 falls within the date ranges of their leave in Workbook1. If not, I need to ignore (or delete) the payment record in Workbook2 for that user.
Is this possible using VBA? Mind you, I'm stuck either way as I simply have no idea about how to approach this (possibly ... would first need to work out how to identify if the dates cross and then perform a lookup ...?)
I am trying to compare one range to another. If an item in one list is not in another i am adding this item to an array called addarray. The problem is when i try to access the array to see if one of its entries is "" i get a type mismatch error. My code is shown below:
View 5 Replies View RelatedI have two large ranges of numbers stored as text.
I would like to compare first element from first one to each of the second, make a shortlist, and then further analyze the data on shortlist by some additional criteria.
After that the result is written to some place and the cycle continues with the second element from first range etc
Question:
Is it possible (in VBA) to create temporary arrays(baskets) where the fist set of data is analyzed and then after obtaining the result, array(basket) would be emptied and new sets of data is loaded and the cycle goes again?
how the code would look?
I have tried google, but not 100% sure of exactly what i am looking for, well how to describe it to google at least.
I have 2 spreadsheets for an online store, 1 is the CSV update with all active store products on it, the other is the suppliers main price list.
What i am trying to do is get some application or macro code to compare teh 2 sheets.
Update File has 4 fields, SKU, Product Name, Price, and Availability.
Suppliers sheet has many more Starting with SKU, Price incl, Price excl., Product Name and Descriptions.
What i would like to do i have is compare the SKU and Price inlc between the sheets and the if differences are found update it on the csv file.
The supplier sheet also has separations for different product categories on the 1 page, totalling about 60 000 rows. The separation rows just break the product areas and are not merged do the resulting scrip or application would need to search and replace not just flow down the column.
I have one xls sheet with employee’s information, at column A I have ID number -called Mater. I have second xls sheet with update employee’s information, at column A i have the same ID number - called update file. I need help with VBA code that update the Master file according to the update file (The ID at column A is the key between the files)and mark with color the update. The file contain more the 20,000 records and with 10 columns.
View 5 Replies View Related