Counting Colours And Losing The Will To Live
Oct 20, 2008
I've got a function called countcolor (which I take no credit for) which counts a particular color in a range.
---------------------------------------------------------------------
Function CountColor(rColor As Range, rSumRange As Range)
Dim rCell As Range
Dim iCol As Integer
Dim vResult
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = vResult + 1
End If
Next rCell
CountColor = vResult
End Function
I have then set up a couple of macros which, when ran, fills a range of cells with a particular color. These colors are closely related but as you can see from the two macros below they ARE different. I'm finding my countcolor function is counting them twice though even though as you can see the color values are different.....
---------------------------------------------------------------------------
Sub pegcharged()
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 25600...........................
View 9 Replies
ADVERTISEMENT
Nov 9, 2006
My Request is in Two Parts.
Firstly, I would like to be able to List ALL the Colours Numbers in One Column & the Corresponding Colours Next to them Please in a Sheet Named "Summary" & Starting in Cell T3.
Secondly, I would like to Count How Many Cells in a Range on a Sheet Named "Network Points" and in Cells F4:Q179 has a Particular Colour Please. I would like the Totals to go on the Sheet Named "Summary" Starting in Cell W3.
View 9 Replies
View Related
Jan 31, 2013
I wish to count the "number" of numbers in a cell i.e.
Cell A1 has the following data: 12 25 25
This should return 3
Simillarly 4546546545646456 1 2
Should also return 3
I will have data such as the above in a row so i need the count to be able to count a range as opposed to the single cell example above.
Furthermore I need a separate count to count the number or red numbers in a cell. Same parameters as above.
View 4 Replies
View Related
May 6, 2014
I'm sure this will be an easy query.
These are marks scored by 5 people in 10 exams
Name of the student
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS
MARKS[code]....
Range
A1 to K6
In each row top 5 MARKS should be filled with BLUE,VIOLET,YELLOW,RED AND PINK
If its not possible. Top 3 with dark colour, remaining 2 with light colour using conditional formatting
View 1 Replies
View Related
Oct 29, 2007
The idea came about after wanting to show a 'gold' 'silver' and 'bronze' background colour, but having to plump for yellow, grey and orange looked a little crap :f
If not, can a workaround be created by using code and a picture or 2?
View 9 Replies
View Related
May 30, 2006
I have a list of line entries for which I need to insert a variable amount of empty lines per line. (the variable amounts listed in a column to the right of my Active Row.
Unfortunatly I loose the ActiveCell's Value. During Debug it shows the correct cell and value, but after the Debug Step Over, the value shows 0 again. I include my
[code]
Sub IP_Insert_rows()
'
' IP_Insert_rows Macro
' Macro recorded 2006/05/29 by Joseph Clark
'
Dim GrpCtrVar As Integer
Dim LnCtrVar As Integer
GrpCtrVar = 3
LnCtrVar = Range("B8").Value
View 4 Replies
View Related
Aug 17, 2009
i have the following code for sending a worksheet to a closed workbook
View 14 Replies
View Related
Jan 26, 2012
I wanted to know how to download live data (stock data from a a website) through VBA.
View 1 Replies
View Related
Jun 9, 2008
I have a cell (for example, "A1") which is inserted with a WINROS formula to retrieve some data into my spreadsheet. And it is running live at all time.
Actually, I can't think of any formulas to capture the value from cell "A1" to "B1". Because I do not want the value that I captured into cell "B1" running live. I just want the value "B1" fixed after captured.
View 9 Replies
View Related
Nov 26, 2008
I have two tables that pull data from two different SQL tables via an ODBC connection and refresh every 5 minutes. The table headers etc are all identical (it is support call information FYI) but what I want to do is display those tables as one as opposed to two seperate tables - can this be done relatively easily and hopefully without the use of VBA? (Unless anyone is willing to provide the code of course!!)
View 9 Replies
View Related
Nov 30, 2008
I'd like to log live update data continuously,i.e. as the data in the cell changes. The problem I'm facing is that the data comes in at uneven intervals-- ranging from 30 to 50 ticks per second. So I can't really use a timer function. I need to use some function which saves the data as the cell value changes. So, for example, if cell A1 gets updated continuously, cell B1 could save the first value of A1 and then cell B2 could save the second value of cell A1-- and so forth.
View 9 Replies
View Related
Nov 29, 2009
I am looking to get a Live Clock changing every minute..
Lets say I put the Total Time in the Cell A2 = 15 minutes and I have a Command Button next to it "Start"..
On the Click Event I want the Actual Time time-stamped at that moment in the Cell A3 and in Cell A4 the Time should keep on Ticking...
Can someone attach a file in Windows 2003 i.e .xls format and also explain the code if possible.
View 9 Replies
View Related
May 5, 2006
In the attached file (I'm sorry it is zipped, it was just too big), I gave you a snippet of my database. The actual one has 9000+ records. Over in Y4, you'll see the Sum of all the amounts (column X). Run the first macro, that is, Copy Dups, and you'll see it splits the item up by the numbers in column N. This works perfectly (or so it appears.) Then, run the Summary macro (the only other macro in there) and you will see that it pulls the Sums of each spreadsheet and places them on one nice overview page. The summary page. The only problem is, the total of the subtotals on the summary page do not match the total on the main (unadultered) page. I hope i am explaining this right. Somewhere in the macros, I am losing data from my spreadsheets so that the summary page is not equal to the un-macro'd data.
/scratches head
View 9 Replies
View Related
Jul 28, 2006
As you can see this macro copies a worksheet from one workbook to a new one. It is 14 columns wide by just under 700 rows long. I am losing information out of cells all over the place.
ChDir "H:HOMETIMCEXCELCOUPON_COUNT6_JUN"
Workbooks.Open Filename:="H:HOMETIMCEXCELCOUPON_COUNT6_JUNJUN_2006" + ".xls"
Calculate
Sheets("MONTH_END").Select
Sheets("MONTH_END").Copy
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False .........................
View 2 Replies
View Related
Dec 7, 2006
I know that Excel has a function to import currnecy rates from the MSN Money web site. But MSN Money doesn't support all currencies. I would like to build an excel sheet where a user can maintain the currency codes and the currency rate is being fetched from the internet. Does anybody have a suggestion from where I could get currency rates into Excel?
View 9 Replies
View Related
Jan 6, 2014
In cell L7 I have (80/60)/24 and formatted h:mm to get 1:20. In cell M11 I have 1:00 (h:mm). In cell M12 I have L7-M11 and get the result 0:19. Why I'm losing a minute and not getting the result 0:20?
View 5 Replies
View Related
Apr 11, 2014
I have a spreadsheet set up in the following way;
Excel Sheet Set Up.JPG
What I'm trying to replicate in column A is similar to the live bullet point numbering that can be found in Word documents where if a line is deleted the section numbering (think bullet points in Word) automatically fall into line and update i.e what was 1.4 automatically becomes 1.3 and so on.
So far I've tried the simple formula of = cell above +0.1 which works fine until i have to remove lines.
View 2 Replies
View Related
Jun 19, 2009
eg having a live rate of intrest or exchange rate from the internet that is updated live whenever you load up excel and you are connceted to the intenet.
View 14 Replies
View Related
Jan 17, 2014
I am not good at excel. I would like to split buy and sell orders from the live feed and updating when new feeds come in.
LIVE FEED
BUY/SELLQTYPRICE BUY QTY PRICESELL QTY PRICE
B 4150
S 6200
S 4300 CHANGE TO
B 3350 B 4 150
B 2180 B 3 350 S 6 200
S 2220 B 2 180 S 4 300
B 1120 B 1 120 S 2 220
View 6 Replies
View Related
Jul 14, 2010
I work with different currencies in my company, now I would to get an up to date state of the cask book. So I have $250, and 500EUR, now I want a formula (connecting to internet) that automatically multiplies the $250 with the current exchange rate, so I know how much I have in Euros in total.
View 5 Replies
View Related
Nov 14, 2008
I've got two columns:
A, B
abc, def
qwe, rty
asd, fgh
zxc, vbn
And I need to bring these together in one column so it looks like this
C
abcdef
qwerty
asdfgh
zxcvbn
View 2 Replies
View Related
Jan 21, 2009
i try create formula or macro, which will search in data during writing in cell.
F.e. i have column
A
1 dano black
2 dodo red
3 phil blue
4 black jack
I want something, what will be look in this column "A" up, during my writting in cell B1 and show results in C1. When i write „b“, it will show in B1 „dano black“, if i add „l“ (it will be written „bl“), it shown again „dano black“. If i add „u“ (it will be written „blu“) , it shows „phil blue“
View 3 Replies
View Related
Apr 25, 2014
I'm attempting to remove erroneous noise from a list of data by removing 20% of the highest numbers in that list.
For example: I have 2000 pieces of data, summed. when a new piece of data is added, the last one drops off. This is a rolling sum. When seen on a graph, there are occasional spikes of erroneous readings, causing large fluctuations in the graph. Of the total 2000 peices of data, I want to find and remove the highest 20% (400) of them, leaving behind 80%. Removing those erroneous spikes would show a softer version of the original data when looked at in a graph.
This needs to be done in such a way as to allow me to do it continuously. If I add live data points, I want to be able to find and remove the highest 20% automatically, or by copying the contents of this cell.
View 9 Replies
View Related
Mar 31, 2009
I need to make an excel sheet with checkboxes (around 50) which each add a value to 1 cell when clicked.
If you click them again the value should be removed again from the cell.
So lets say I got cell A2 and I got checkbox 1 to 5. Checkbox 1 has value 1 checkbox 2 value 2 etc. up to 5.
So if I click Checkbox 1 and 4, cell A2 should display "5". If I check Checkbox 1 off it should display "4". Now I tried to do this but I really cannot get cell A2 to display "4" again (clicking Checkboxes again just adds another value).
I know why this is the case, I defined checking the checkbox as click, not as "check" but I have no idea how to do this correctly.
Little info:
I have little to no experience in VB but got some programming experience next to it. I am using Excel 2007 (forced to, work).
View 9 Replies
View Related
Aug 28, 2009
I have set an excel spreadsheet set up to update live data from a URL every 5 minutes. However I am wanting to do try and save a bit of time if possible.
Basically I am monitoring horses bought on a game website. Each horse has the same URL except for the number at the end which goes up in 1 increments. The URL is already set up and when the horse is bought it changes from being blank to having the horses details etc.
At the minute I am able to autofill cells with each uinique URL using fill, then series menu. This is so I dont have to manually enter each URL, as hundreds may be bought each day so it would take too long to do this.
My question is this, am i able to have a automatic series linked to the 'data from web' function in data tab, instead of having to manually adjusting each URL for each individual horse.
View 9 Replies
View Related
Dec 30, 2009
How To Connect Live Data in an Excel Sheet?
I want to track changes in some Stocks, I have made a sample file in Excel and would like to make it Live by Connecting it to a website..
However, I do not know how to go about the same?Can some please provide some examples as well as a File if possible explaining how it was done..
View 9 Replies
View Related
Jun 8, 2006
I have an application where by the user only sees userforms which then writes the data to the back end worksheets. So the Menu or front useform I have set at vbModeless, which also means that the user can now open up or use an already opened spreadsheet. I believe I am correct in saying you can't do this if the userform is modal. Anyway this is what the user wants, i.e to be able work on multiple spreadsheets, whilst my app is open. The problem is when they click on one of the other open spreadsheets and then click one of the buttons on my application, you get an error.
I believe is because I use code such as
frmMetrics.Show
Where I haven't specified the actual workbook, so excel doesn't know where to go to find the userform. So how do I specify the workbook, without constantly having to change the line, when version numbers changes ?
eg
Application.workbooks("Book1")
For the next revision the file may be called Book1.1
Application.workbooks("Book1.1")
View 2 Replies
View Related
May 22, 2007
This is probably a simple one but I can't figure it out. I am building a workbook from scratch. It has about twelve of thirteen tabs at the bottom. On one of the tabs I would like to copy and paste a stand alone worksheet. The worksheet functions properly when it is stand alone. However when I copy and paste the worksheet into this new workbook none of the formulas transfer with it. I do get the cells filled in, but I would like to get the formulas.
I have done this before and it worked fine. In fact I can open a new workbook and copy / paste the worksheet into it and everything works fine. Again when I copy / paste it to this new workbook the formulas do not transfer.
View 4 Replies
View Related
Jun 12, 2007
Here's all I'd like to do~I'm a small business owner and I need to create an excel list of potential work contacts. The contacts are listed on a web page like this
aerographics Los Angeles 310. 240. 3308
Now, on the web page if you click on aerographics it takes you to the website. I'd like to copy and paste those same three entries from the webpage in to an excel sheet~and be able to click on aerographics and have it take me to the site. I searched this site and couldn't find the problem replicated. I've tried paste special~it formats the entries I want as URL's, but when I click on them nothing happens. I can type www.googl.com in to a cell and it instantly works as a link. And I have about 500 of this type entry/copy and paste to do so making a hyperlink is impractical.
View 6 Replies
View Related
Dec 5, 2007
Running excel 2002, windows vista (some machines on network are running xp). I have a spreadsheet that I want to be able to edit from all computers on the network so that it shows real time changes on all PC's.
It would be like using google docs, I can't use google docs because my file size is 2.5MB and it is too much for it.
View 5 Replies
View Related