Posts by simpsonc2

simpsonc2 · Feb 16th 2006

Re: Auto print document when opening

pmorris, check that your macro security setting is on medium or low or the macro won't work

Tools>Macro>Security

:rock:

Colin

simpsonc2 · Feb 14th 2006

Re: Repot for total sum by date

Thanks for all your help carlmack, this works great

:thanx: :thanx:

Colin

simpsonc2 · Feb 11th 2006

Re: Repot for total sum by date

Thanks for the reply and example. I was going to try and have a form with start date and end date fields to submit to the query, this is where I get stuck. I can build the form but I have problems with the code.

Any Ideas?

:?

Colin

simpsonc2 · Feb 10th 2006

Hi Guys n Gals,

I have an Access database with a table named 'tblAppeals'. I have already created a report that totals up the FinancialGain for the whole database. What I want to do is be able to select a date range and see the FinancialGain total for that range (i.e. select a specific month or quarter)

There are fields named 'FinancialGain' and 'End_Date'. When the 'End_Date' is entered they would also enter a figure in the 'FinancialGain' What I'm looking to do is run a report that would allow you to choose between a from date and to date range (based on the 'End_Date' field) then show the total sum from the 'FinancialGain' from that range.

Any Ideas how I can do this?

Cheers

Colin

simpsonc2 · Feb 7th 2006

Re: Macro to sort rows

Roy,

Sorry about this but I'm still stuggling with this sorting, can you look at my code and tell me what I'm doing wrong?

:thanx:

Colin

simpsonc2 · Jan 23rd 2006

Re: Macro to sort rows

Hi sorry for not being around lately, been in hospital. Better now.

Still working on this loop thing, trying to get it to loop

Code

Sub Macro1()
Dim Row, Range
Row = 2    ' I want to start at Row 2.
Range = "All_Numbers"   'This is Range(B2:G1151)
For Each Row In Range
    Row.Select
    Selection.Sort Key1:=Range("B"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
        Exit For    ' Exit loop.
Row = Row + 1
Next
End Sub

Display More

This code stops on the 'For', what am I doing wrong?

:?

Colin

simpsonc2 · Jan 17th 2006

Re: Macro to sort rows

Cheers Roy,

I had already tried the record macro method but I'm having trouble making it loop to automating it. here is the code I recorded.

Code

Sub Macro1()


    Range("B2:G2").Select
    Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
    Range("B3:G3").Select
    Selection.Sort Key1:=Range("B3"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
    Range("B4:G4").Select
    Selection.Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
    Range("B5:G5").Select
    Selection.Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
End Sub

Display More

Thanks

Colin

simpsonc2 · Jan 17th 2006

Hi everyone,

I've downloaded the lottery numbers and imported the .csv file into excel but they are in 'drawn order' and I would like them in numerical order.

When imported column A is the date then columns B : G are the main balls and H is the bonus ball. All I want to do is starting at row 2 select columns B:G and sort left to right, then go to the next row sort the same and continue till we hit a blank cell.

Can anybody help :?

Colin

simpsonc2 · Dec 21st 2005

Re: Conditional format within a chart

Kris,

Excellent thread but the chart colours are preset and don't change when the cell colour or chart column value changes?

:?

Colin

simpsonc2 · Dec 21st 2005

Hi everyone,

I've done a search on this subject but found that all the results had no attached examples for me to look at.

I have a chart in a worksheet that shows the values in column H. In the worksheet I have 3 conditional formats in column H so that if the value is:

=0 then .ColorIndex = 37
Between 1 - 9 then .ColorIndex = 50
>9 then .ColorIndex = 3

Is it possible to assign the same conditional formatting the the columns in my chart??

:thanx:

Colin

simpsonc2 · Dec 7th 2005

Re: Create report between two dates

Thanks for the reply. Yes the =NOW() is a default value.

I am looking at the Northwind database for examples and found the 'Sales by Year' the closest. When you choose this it brings up a userform with the beginning and end dates to be entered. I have gone into the VBA code but cannot find how the form is created, or how the dates are used for the report? It maybe too early in the morning for me or something?

:duh:

Colin

simpsonc2 · Dec 6th 2005

Re: Create report between two dates

Sorry this should be moved to Access Help. :?

Colin

simpsonc2 · Dec 6th 2005

I have created a report which gives me totals from a table but I would like to run the report and specify between two specific dates. In the table I have a 'created date' field that has the formula '=NOW()', I assume could be used to select the dates in the report. Can anybody tell me how I can set up this function?

:?

Colin

simpsonc2 · Nov 30th 2005

Re: VBA to color cells

Excellent that is great, thanks

:thanx: :thanx:

Colin

simpsonc2 · Nov 30th 2005

Re: VBA to color cells

It may be becaues it was in Excel 2003, I have saved it in Excel 95, try it now.

:guitar:

Colin

simpsonc2 · Nov 30th 2005

Hi everyone it's me again! Wonder if you can help with this one.

I have created a bonus ball checking workbook for a syndicate at work (with the help of another forum buddy John Roberts).

Nothings wrong it all works great but what I would like to do is, If the players entries are matched with the drawn ball, I would like the cell background to change color.

:gift:

Colin

simpsonc2 · Nov 29th 2005

Re: Counting colored cells

Thanks RoyUk,

I did another search and found this:

Code

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
	Dim rCell As Range
	Dim lCol As Long
	Dim vResult


''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com


'Sums or counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
  
	lCol = rColor.Interior.ColorIndex


	If SUM = True Then
		For Each rCell In rRange
			If rCell.Interior.ColorIndex = lCol Then
				vResult = WorksheetFunction.SUM(rCell,vResult)
			End If
		Next rCell
	Else
		For Each rCell In rRange
			If rCell.Interior.ColorIndex = lCol Then
				vResult = 1 + vResult
			End If
		Next rCell
	End If


   ColorFunction = vResult
End Function

Display More

This works great, thanks again

:thanx:

Colin

simpsonc2 · Nov 29th 2005

I have a workbook with 12 worksheets, each worksheet has 20 columns (which have names in and start at column 'D') and 499 rows. Each cell can be colored either one of 6 colors (ColorIndex 1, 3, 6, 11, 46 or 48).

What I would like to do is total each column in each worksheet for each color. That way I can create graphs and reports against each name. I have searched through the threads but haven't found anything I can use. Any Ideas??

:party:

Colin

simpsonc2 · Nov 18th 2005

Re: Counting nonblank Cells

Thanks WillR, for some reason I was thinking of DCOUNTA????? I feel such a fool now :? :?

:thanx:

Colin

simpsonc2 · Nov 18th 2005

Sorry guys, having a Friday brain freeze.

I've got 2 worksheets, each have 200 rows and 250 columns of data but not all cells contain data. Is there an easy way to count the number of cells that contain data (not based on criteria)??

:?

Colin