Sorting 2 Different Type Of Data / By Colors

Mar 18, 2007

i have a file which consist of sheets called june 2007, color, and report. When i click on the command button on sheet june 2007 the code works and copies some data into sheet called report in that case some of them white color copied over and some of them blue.

Since at the actual file there are nearly 500 rows and 32 columns when the report works, i want to sort each colors individually by looking the values at column called ETA (column "E")

white rows should be sorted in ascending order in column e wise
blue rows also should be sorted in ascending order in column e wise
each colors should be sorted individually

View 9 Replies


ADVERTISEMENT

Sorting Data Into Type And Site Name?

Aug 13, 2014

I am trying to fine an easy way to sort the attached spread sheet. it is a down load from our supplier which has a lot af data that i am not interesed in the only ones i need are columns description (r) and units (o) and name (g).

I am wanting to split each waste i.e oil fitters, acid batteries etc and each site so that the total amount each site has returned can be tabled ,so that i can create graphs showing who has/ has not return their waste steams.

View 6 Replies View Related

Macro To Compare Colors In Cell And Type Result

Feb 6, 2014

Like u see in attachment i have products in cells wich are painted in different colors. I need to compare two numbers of different product, and write result to column "Type D,C,B". If one of two products is without color/white or they are not the same color then result is always "D", if both products/cells have same color and different number then results is "C", if the color and number is the same then result is "B"

Below cell B2 I will write numbers by hand, when I finish and start macro it will compare first cell B2 and B3 and write result to C3, then compare B3 and B4 and write result to C4 and do that until there is any number in column B:B.

Attached File : excel help.xlsx‎

View 8 Replies View Related

Excel Pallet Lost Colors (hovering Displays Colors But Visual Clues Are Not Shown)

Jul 7, 2013

My pallet lost color-coding - if I hover over each little scare it displays the names for the colors and if I click on them they color the cells with the right colors, but the palette itself lost the visual display of colors except for 8 colors: black, blue, red, magenta, yellow, cyan, and white.

I use color-coding of cells a lot and I find it difficult to work without visual clues. At least the hover-support allows me to get the work done, but with difficulty.

View 12 Replies View Related

Same Colors To Same Data Series

Nov 27, 2013

I have a big set of data and I want to create different graphs in different tabs with it.

So Imagine that in Tab 1 I want to do the sales forecast for the next five years for product A for all countries in Europe
Tab 2 would be the same, but for product b
Tab 3 the same, but for product c

So with dynamic tables in each tab I can easily do my graph

The problems is that since values vary per product country A in tab 1 is red, in tab 2 is blue and in tab 3 may not exist.

How can I set up a template so taht I do not have to color code each country in each tab? I.e. I want Spain to be blue in all my graphs in the workbook, Italy red and so on...

View 1 Replies View Related

Link Colors In Charts To Data

Oct 4, 2006

How can i link colors used in charts to companies i have i my database. Now excel provides colors in a certain sequence but this is not what i want. I attached a file in which i for example wants to create pie charts of the company's sales. What do i have to do to give each company its own color?

View 2 Replies View Related

Print Data Without Any Cell Colors

Oct 17, 2006

Within the Workbook events there is a "Before_Print", however I also need something like "After_Print" which does not exist (as far as I can tell).

I have several rows of data which alternate colors. When I print it I want to take out the fill (so as to save ink) - which I can do in the "Before_Print" event. [No problem.] However, once it is done printing I need to restore the alternating colors, but I cannot think of any event or trigger which would enable this.

I have thought through several things and the closest thing I can think of is setting up a timer. But there is of course no way to now how long to set it for. [Before/After Save also has the same "issue".]

View 9 Replies View Related

Sort Data Without It Moving Cell Colors

Apr 30, 2009

I have a sheet with alternating colors (gray,white) for the rows. This makes it easier to read each individual rows data, just like some printer papers. The problem i have is when i sort the data it takes the background color with it and i end up with a mess. does anyone know how to get around this.

View 2 Replies View Related

Populate Cells (or Map) With Shaded Colors From Data

Jan 6, 2013

I'm trying to create a formula that can display colors based on the strength of the margin of victory of a candidate, take these two examples for instance.

County
Frank
Cole
Dean
Total
MOV
MOV%

[Code]....

I want to be able to draw in the outline of these counties (or at least the cell proceeding the MOV%) displaying the color that corresponds with the winner of that county. The added twist would be to shade the color depending on the Margin of Victory (MOV) as well. The closer the vote, the lighter (or mixed?) the cell (or county) would be. If it could be at all possible I would really be cool to mix both colors so that a result 100% for one candidate would get a color 100% pure while a 17% MOV would get a color 17% more of the winning candidate than of the loser (or losers) colors. I don't think a VBA is necessary for this one.

View 2 Replies View Related

Data Validation List Can We Show Colors

Dec 9, 2008

in a data validation list can we show colors,assume the following list as a data validation list

Item #
Orange =50
Apple =60
Banana=56
Mango = 40

in this list i want to give colors (as shown) to these two items

View 13 Replies View Related

Conditional Data Point Colors For Scatter Plot Chart

May 5, 2008

I have to create a chart XY Scatter plot in excel. I have to differentiate the data points color based on the another column "category". Now I can change the data points color manually. Since the number of points is huge, is it possible to color the data points based on a column?

View 5 Replies View Related

Type Data Subsequintly

Jan 7, 2009

i have five values as :
127
134
143
158
170
how can i list is in column for more than on time as
127
134
143
158
170
127
134
143
158
170
127
134
143
158
170
and so on

View 2 Replies View Related

VBA Functions: Type # For Date (Type:=?)

Oct 22, 2008

1. Is there a VBA Function equivalent to the FIND() function, If so What is it?
2. Let's say Im Putting a Date into a inputbox, what is the type # for date (Type:=?)??

View 2 Replies View Related

Chart Type For Data Comparison

Oct 23, 2006

I am trying to figure out a way in excel to graph some data I have in a new, sophisticated type of chart. My data is new square foot sales divided by new square feet (for a retail store). It is a dollar amount. I also have this data as a percentage change year over year. If I want to compare two stores with different data -- what would be the best way to present it on a chart. Is there a way to show this on a 3-D plane so that both companies have the data in the same chart up against each other?

View 2 Replies View Related

Limit Entry According To Data Type

Jul 3, 2007

I have a user form with textboxes on it, in some text boxes, text should be entered, but in some number should be entered, and in some date is required, but How can control the entry, if some one type text instead of number, then it should not be accepted. NOTE: Before I have seen such example here, that uses the KeyPress event.

View 6 Replies View Related

Identify Cell Data Type

Oct 21, 2007

How can I identify a cell type at excel sheet (w/ VBA command) ?
I find this command (from Access forum):
http://www.thescripts.com/forum/thread601180.html
but its working only with Access...

View 4 Replies View Related

Count Formula For A Negative Type Of Data

May 7, 2014

I can't seem to find the right formula that can tell me how many times i have a negative number in a series of cells.

View 1 Replies View Related

Cell Data Type Change Code?

Dec 13, 2011

Is there a code that changes any numbers stored as text into regular numbers?

View 3 Replies View Related

SUMIF - Value Used In Formula Is Of Wrong Data Type

Jul 27, 2012

I have a formula =SUM(IF($I$4:$I$302="A",$K$4:$K$302)) works fine.

I am using the same formula referencing a different column =SUM(IF($W$4:$W$302="A",$Y$4:$Y$302))

And I get the error : "A Value used in the formula is of the wrong data type"

View 2 Replies View Related

VBA / Match String Or Long Data Type

Nov 5, 2012

I'm trying to use application.match() but I'm running into a issue where I don't know if I'll be matching a string or long data type...

Here's the snippet of code:

Code:
' UNIT DATA
' Set Unit#
s1 = ActiveSheet.Pictures(Application.Caller).Name
iLen = Len(s1)
s1 = Mid(s1, 2, iLen - 2)
' Set Sel Ex Work Date
iCheck = Application.Match("MACHINE_NUMBER", Sheets("Allocation").Columns(1), 0)

[code]....

How can I get application.match to look for strings if s1 = "ABC123" or numbers if s1 = "123456"

I've tried dim variant and s1 + 0... But, haven't come up with a solution.

View 4 Replies View Related

Determine Type Of Data Of Right Click Parameter

Aug 21, 2013

I need to make a fork in my code based on the type of data received from an input box launched from a right click and passed via the actioncontrol parameter.

The input box box is a range selector.

Dim seriesIdArray As Variant
seriesIdArray = Range(CommandBars.ActionControl.Parameter)

Generally, the user will have selected multiple cells as their range and I loop through using:

For j = 0 to ubound(seriesIdArray, 2)

However, if they only select one cell, I am getting back the value of that string in seriedIdArray, and that gives me a type mismatch error. I'll need to handle this a little differently, and I know how to do that part, I just don't know when I need to do this.

How can I tell whether they have selected one cell or multiple cells based on the value of the actioncontrol parameter?

I considered trapping the error type (13) and branching based on that, but then I end up with spaghetti code and I'm trying to avoid that.

I think I may need to create another more specific variable to take the action control parameter, test it, and then decide whether I should use an array or a range, but that's just a suspicion.

View 7 Replies View Related

Method Failed Error And Data Type

May 14, 2007

The error is:
Method 'Range' of object '_Global' failed
Line it fails on:

Set r = Range("myRange")

Sub LearnCells()
Dim r As Range
Dim n As Long
Set r = Range("myRange")
For n = 1 To r.Rows.Count
If r.Cells(n, 1) = r.Cells(n + 1, 1) Then
MsgBox "Duplicate data in " & r.Cells(n + 1, 1).Address
End If
Next n

End Sub
Questions:

1) 1st Dim statement, is this valid? I still get a little unsure- in the data type lists in the help file, the list file does not list things like Workbook, Worksheet Range.
2) Why is the error ocurring?

View 9 Replies View Related

Data Type Of Items In Controls Collection?

Mar 2, 2008

I have a UserForm function which accepts as a string the name of a TextBox control on the form. The function returns a reference to the named TextBox control (or Nothing if the control does not exist). I have the function header defined this way:

Public Function TextBox(byval strName as String) as TextBox

The function returns a reference to the TextBox control like this:

set TextBox = Me.Controls.Item(strName)

(I know, the .Item is not required.)

The code which calls the above function first declares a TextBox object with this Dim statement:

Dim txtTextBox as TextBox

The routine then goes on to call the TextBox function like this:

set txtTextBox = TextBox(strTextBoxName)

When run, the above statement genrates a Type Mismatch error. I'm confused, because if I 'TypeName()' the TextBox function's returned value, it's "TextBox,".

I thought I could work around this problem by changing the TextBox function's return value's data type to Variant, but that produced the same result.

The only "solution" I've come up with is changing the TextBox function's return value's type (and any reference to the functions' return value) to Control.

I expected that the Controls object would behave similar to a Collection object in that it's items can be different types (TextBox, Label, CommandButton, ListBox, etc.) and no Type Mistmatch erros occur so long as the type of the item returned matches the type of the variable referencing that item. But is seems that Controls requires that any reference to one of its items must be type Control, not the actual type of the item returned.

I'd prefer not to use Control data rypes in my applcation, as that would require additional code in all of my subs and functions to ensure that any Control object passed to it is the correct type of control (TextBox, Label, etc.).

Can anyone explain what's going on here? Why shouldn't I be able to assign a TextBox type variable to Controls.Item("xyz"), so long as the item returned by Controls.Item("xyz") is type TextBox?

View 9 Replies View Related

Extract Data By Type To Separate Workbooks

Mar 12, 2008

I have created a workbook for a co-worker. The way it works is data comes from an outside source with customer names in Column A and their Account Reps in Column H. This data is pasted into a template that I created with a very simple macro recording that filters on each Account Rep and is copied and pasted each into a different workbook. Then I had to go through each workbook (because of the info being sensitive) and create a macro that deletes the unwanted data because when the data is pasted it transfers all of the data and you can unfilter to see the full sheet.

I know there is a better way to extract data on each change in column H into a new workbook. Does anyone know how to do that? Another issue is the original data is not very clean so there are blank rows in the list. For Example:

CustomerTechnology SegmentOpportunity Dollars ($)Opportunity StatusAction Item(s)(ST) or (LT)Due dateAcct RepCustomer 1Action 1ST3/30/2008ACCustomer 2Technology 1Action 2LTTBDACCustomer 3Technology 2$10,000FindAction 3LTTBDACCustomer 4Technology 3FindAction 4LTTBDBHCustomer 5Technology 4FindAction 5LT1/31/2008BHCustomer 6$30,000Action 6LTTBDBHCustomer 7Action 7LTTBDMJCustomer 8Technology 5$10,000QualifiedAction 8ST3/1/2008MJCustomer 9Technology 6QualifiedAction 9ST2/28/2008CGCGCustomer 10Technology 7$18,000FindAction 10ST3/1/2008CGCustomer 11Technology 8$10,000QualifiedAction 11ST3/30/2008BHCustomer 12Technology 9FindAction 12LTTBDACCustomer 13FindAction 13LT3/15/2008AC

View 9 Replies View Related

New Collection Of User Defined Data Type

Jun 23, 2006

I can create my own new collection which is handy as it accepts uniques only and i can access using its "key"

But can i have a collection of "user defined data types"

View 5 Replies View Related

Data Type Of Coordinates Of 2-dimensional Array

Apr 29, 2007

I have a big array "DataArray" and want to access it:

For i = 1 To 4
variable = DataArray(SourceArray(i))
Next i

"DataArray" has two dimensions, so SourceArray has to consist of data like this:

SourceArray(1) = 1,2
sourceArray(2) = 2,4
etc

What data type does Sourcearray have to be? Integer doesnt seem to work, and DataArray doesnt like a string as coordinates. I have a workaround with two different arrays of integer for x and y coordinates, but this cannot be it.

View 4 Replies View Related

Wrong Data Type Supplied To UDF Error

Aug 31, 2007

My colleague and I are writing a udf which uses a "match/ index" combination to return a value to a cell from a named range in another sheet. The cell returns returns the error "value used in the formula is of the wrong data type". When we break down the code into a function which refers directly to the relevant cells, and a sub which returns the value in a message box, it works correctly: here's the code for the function and test sub:

Function MRL(PropertyType, ReturnType, Location, Year2) ....

View 7 Replies View Related

Excel 2010 :: After Applying A Data Filter And Sorting The Data / How To Revert Back To Original

Dec 20, 2012

I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?

View 3 Replies View Related

Checking Null In A Field With Text Data Type

Jul 10, 2014

The following code doesn't check null condition in Scannedby field. The scannedby has text data type. The code gives me all the data where scandate=20130722 but doesn't check that scanned by can't be blank.

[Code] .....

View 5 Replies View Related

VLOOKUP Formula Works But Not If Type In New Data In Cell?

Aug 23, 2013

I have a spreadsheet with a VLOOKUP formula that I have to send to a customer weekly. They are required to type a code in column E of what they invoice, and it pulls the rate into column G based on a rate table I have. The formula is: VLOOKUP(E2,Rates!A:B,2,0)

When I made the spreadsheet, the formula worked perfect. But when they type their codes in and send it back to me, it's messed up. All of the codes they've typed in do pull the correct rate. However, if I try to go to column E and type a different code on top of what they typed (a code that IS IN the rate table), it will tell me: "The value you entered is not valid. The user has restricted values that can be entered into this cell".

If I create a new tab and mimic the exact formula and columns as I am trying to work with, it works perfectly. But for some reason, once they've typed their data into my spreadsheet, something messes up even though the formula is perfect, dragged down, etc. All columns are formatted as text. And the range in the rate table is correct as well. When I try to mimic the formula in the other tab, if I click a cell in column E, there is a drop down list there showing all the available codes from my rate table to choose from. But on the spreadsheet that is messed up has no drop down list on the cells.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved