Posts by dodger7

Re: Removing Color Filter Utility

You can either do as shg said above, or read my previous post carefully. You said "you cant see it there". I said ignore the box that pops up, and go to the Data toolbar as you would normally and delete it by right clicking on it and selecting delete. Then you can close the pop up box when you have finished.

The utility is very helpful and someone was kind enough to share it. Just because you do not know how to remove it, does not mean that it is not a good tool.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]or if you cant do that run this:

Sub Remove_Toolbar()
Application.CommandBars("Data").Controls("Color Filter").Delete
End Sub

Re: Removing Color Filter Utility

have you tried View>toolbars>customise
Ignore the box that pops up, and go to Data>right click on the item you want to delete, and select delete.
Close Excel down and it should be gone.

Re: Escape Excplamation Mark

if you use formulaR1C1 then you have to use the R1C1 reference style

i.e ActiveCell.FormulaR1C1 = "=Sheet1!R[x]C[x]"

Re: Escape Excplamation Mark

Range("A3").Select 
ActiveCell.Formula = "=Sheet1!A3"

Re: Filter Data Across Multiple Sheets

Thanks Ger

I did think that I had too much data for excel, but I also knew that you guys would tell me for sure.
A small sample is attached. Thanks in advance for any assistance.

Jamie

Re: Filter Data Across Multiple Sheets

Dave,

I couldnt see the benefit of having all the data in one sheet because there was more than 65,536 rows. If I am wrong I apologise.

Derk, I would have liked to use Access but unfortunately my knowledge dosnt allow me. Thanks though.

Hi guys

I have a workbook with 236,000 rows of data (accross 4 sheets). Each sheet is identical layout with differnet data. An example of the data is:

Policy Number Scheme Number Name Agent code Scheme Name
Kxxxxxxxxxx Jxxxxx Mr A example 5/xxxx Example Scheme
Kxxxxxxxxxx Jxxxxx Mr A N Other 6/xxxx Another Example

To do a search for a scheme number for example I would filter the 4 sheets using the scheme number I needed, then copy and paste the info from the 4 sheets into a 4th sheet so that I could work with the data.

I need to know if its possible to do the following:

create a useform to act as a GUI to that if a scheme number is selected it will search/filter the 4 sheets and present the results in a 5th sheet

I could upland a sample if ti would help

Many thanks in advance

Jamie

Re: Changing The Font Color Of Formulas

heres an example for you

Re: Changing The Font Color Of Formulas

try Edit>Go To...

click Special>formulas radio button

click Ok.

This will select all cells containing formulas. You can then change the font color and it will update all cells containing a formula.

If your want to do it automatically you could place the following code behind the required sheet:

Private Sub Worksheet_Calculate()
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    Selection.Font.ColorIndex = 3
    ActiveSheet.Range("A1").Select
End Sub

HTH

Re: Lookup/Reference Text From 1 Worksheet Into Another

see daves links for full explinations. The drop down list needs to be pulled from a list on the SAME sheet. On my example this is the list starting "AA6" (I have changed the font color to white so that the user cannot see this list). If you also see Daves link on dynamic ranges this will explain how to have the drop down list automatically updated if you add to the list under AA6.

Ive attached an example of a button that will clear your daily data. This deletes the food, portion, date and notes.

Cheers
Jamie

Re: Lookup/reference Text From 1 Worksheet Into Another

you could use data validation. This way the user can either choose the food from a drop down list or type it manually. If their manual entry dosnt match you can have an error message pop up explaining this.

I have uploaded an example
HTH

Re: Conditional Formatting - Change Cell Based On 2 Conditions

Conditional Formatting uses the first condition that is true and ignores the rest so you want to put the "blue" condition first.

So condition 1 would be:

Condition1 - formula is: =IF($B$1="Done","true","False") and format to be blue

Condition2 - formula is: =IF($A$1="Top Priority","True","False") and format to be red

HTH
Jamie

Re: User To Set Default Value In Userform

you could do this by storing the value in a cell. Say, cell IV1 for example (a cell that the user is not likely to see or change)

here is an example file

HTH

Re: Remove/Clear Duplicate Cells

Hi Ger

I will run this when I get to work and let you know how I get on.
Thank you for your input

Jamie[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Hi Ger

The code run very quickly (1 minute exactly) however its not working as expected.
The code returned around 25,000 unique ids (cant remember exact figure) however I know this is incorrect because;

each column is a unique list, i.e there is no repeat of a user id in the same column. Each column averages around 35,000 user IDs so I would expect this to be the lowest figure returned from the code. To clarify, each column represents the Unique IDs logged in for that month.

Thanks
Jamie[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Hi Ger

Re: Remove Duplicates

Hi Ger

Thanks for your reply. The code I have was from ozgrid search, remove duplicates. here it is:

Sub KillDupes()
Dim rConstRange As Range, rFormRange As Range
Dim rAllRange As Range, rCell As Range
Dim iCount As Long
Dim strAdd As String


    On Error Resume Next
    Set rAllRange = Selection
        If WorksheetFunction.CountA(rAllRange) < 2 Then
            MsgBox "You selection is not valid", vbInformation
            On Error GoTo 0
            Exit Sub
        End If




    Set rConstRange = rAllRange.SpecialCells(xlCellTypeConstants)
    Set rFormRange = rAllRange.SpecialCells(xlCellTypeFormulas)


    If Not rConstRange Is Nothing And Not rFormRange Is Nothing Then
        Set rAllRange = Union(rConstRange, rFormRange)
    ElseIf Not rConstRange Is Nothing Then
        Set rAllRange = rConstRange
    ElseIf Not rFormRange Is Nothing Then
        Set rAllRange = rFormRange
    Else
        MsgBox "You selection is not valid", vbInformation
        On Error GoTo 0
        Exit Sub
    End If
   
    Application.Calculation = xlCalculationManual


    For Each rCell In rAllRange
        strAdd = rCell.Address
        strAdd = rAllRange.Find(What:=rCell, After:=rCell, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Address
          
        If strAdd <> rCell.Address Then
            rCell.Clear
        End If
    Next rCell


  
    Application.Calculation = xlCalculationAutomatic
    On Error GoTo 0
End Sub

I may have a look at pivot tables, thanks

Hi Folks

I wonder if anyone can point me in the right direction. I have a workbook with 12 sheets, 1 for each month of the year. Each month has around 30,000 user IDs on it (this is a log of each time a user logs into our website).
What I need to do is find out how many unique users have logged into our site for the whole year, or in other words, remove duplicate user ids accross the workbook.
I realise that this will probably destroy my processor, so was wondering the best way to do it?
I also have all this information in ONE spreadsheet if it would be easier? I just copied and pasted each month into a column (Jan is column A, feb in B, mar in C etc)

I ran some code on this sheet to remove dupllicates from column A:E but this was taking AGES!

Thanks in advance for any help

Jamie

Re: Create Math Worksheet For Kids

can you upload an example of something you are looking for? Would be much easier to assist you?

Re: Extracting String Within Brackets From A Relative Cell

you can do it by using data>text to columns... then choosing delimited, and using other character as "(". Then repeat for ")", This will split the text and ann the text between ( and ) will be in a separate column.

you can place the following code in a module and assign a button to it:

Sub textToColumnsExample()


    Range("V36:V42").Select
    Selection.TextToColumns Destination:=Range("V36"), DataType:=xlDelimited, _
        Other:=True, OtherChar _
        :="(", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Range("W36:W42").Select
    Selection.TextToColumns Destination:=Range("W36"), DataType:=xlDelimited, _
       Other:=True, OtherChar _
        :=")", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True


End Sub

make sure you change ranges accordingly.

HTH

Added by Admin

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)

Re: Autosort

no your not stupid, its probably because we are using different versions of Excel. What you could do, which helps when learning vba, is this:

* Go into the VBA editor (F11), view the code of the relevant sheet, and delete all the code except for sub and end sub. You will be left with this:

Private Sub Worksheet_Change(ByVal Target As Range)


End Sub

* close the VBA editor, then go to tools>marco>record new macro. Sort your data in the normal way (data>sort..) then stop the recording.
* go back into the VBA editor, and it will have created a new module. View this code.
* copy all the code between sub and end sub, go into the relevant sheet code, and paste it between the worksheet_change sub and end sub.

This code should then run each time a change is made to the worksheet.

Re: Autosort

your quite a gifted man... sorry joking aside...

did you look at my example I uploaded for you? compare it to yours, make sure you are placing the code in the correct module - very important.

To reiterate, pref F11 to go into the VBA editor. Under Microsoft Objects, right click on the sheet that your data is in and select view code. Then paste all of the code I posted earlier into here. This code runs everytime the sheet is changed, so its imperitave it goes onto the correct sheet module.