Posts by Smallman

Smallman · Dec 15th 2015

Re: Linking Table Slicer Selection to Chart Title

Hi Moe

Slicers became available in Excel 10 and are very handy. Attached is an example using your 3 states in Oz as an example

Take care

Smallman

Smallman · Nov 29th 2015

Re: from website to excel sheet

Hi

I know what you mean, when you download racing data from the web it comes in a very odd format and requires a lot of work to get it just right. From experience though there are key words which you can trap and isolate the important information and move this to a database.

I carried out an exercise here in Oz for a client to generate racing information. When I was done the user only had a to list Date, Venue and Race Number and the program would go and get the results for all of the races they entered in one hit. To complete the task it took me close to a month.

I feel your pain - it is not easy but there are programs out there which gather data from the web. It is just a matter of working with the format when it comes in.

Take care

Smallman

Smallman · Nov 29th 2015

Re: Autofilter macro for multiple cell values

No worries

Smalltown out!!!

Smallman · Nov 27th 2015

Re: Autofilter macro for multiple cell values

Hi

Pretty sure I know what you mean. You need something like the following.

Code

Private Sub InputFolderNames() 
Dim ar As Variant
 Dim ws As Worksheet
 Dim i As Integer
 
ar = Sheet9.[a1].CurrentRegion
Set ws = Sheet3 'Folderlist
    For i = 2 To UBound(ar)
        ws.[a1:A1000].AutoFilter 1, ar(i, 3) & "*"
        Sheets(ar(i, 1)).[F4:F4000].ClearContents
        ws.Range("A2", ws.Range("A65536").End(xlUp)).Copy Sheets(ar(i, 1)).[F4]
    Next i
    ws.[a1].AutoFilter
'Call CreateFolders
End Sub

Display More

I have attached your file to show workings. It is rather dynamic now.

Take care

Smallman

Smallman · Nov 27th 2015

Re: Autofilter macro for multiple cell values

Hi Paspuggie48

I am not entirely sure what you are attempting. I had a look at your file - fixed up the sheet names, reduced your file size considerably and rewrote your coding so it is more succinct.

Let me know what I missed, seems to work OK.

Take care

Smallman

Smallman · Nov 25th 2015

Re: Hyperlink

Hi Mada Black

There is another way which may be useful to you.

Insert comment in cell E3 right click the outer border and choose

Format comment
Colours and lines
Colour drop down
Fill effects
Picture tab
Choose picture

The formatting of the picture will probably need to change a bit but it is simple and relies on no coding. I have included an example.

Take care

Smallman

Smallman · Nov 24th 2015

Re: Suppress Calculation to Display Shape

Hi Dunnunu

Welcome to Ozgrid

I guess one of the most concerning parts of your post is this

Quote

This new start date starts calculations that take 20-30 sec for Excel to perform

Your file is lagging pretty heavily I see. Might need to rethink the file.

On your other problem - maybe just have the shape appear for 30 seconds then disappear? Would this be a workable solution? Seems you are running the same code if cell B6 or B7 changes which can all be done in one if statement.

Take care

Smallman

Smallman · Nov 17th 2015

Re: Dynamic unique row records formula or macro sample provided

Hi

That makes things so much clearer. This should get you closer. File attached to show workings.

Code

ption Explicit
Option Base 1
Sub GetUnique()
    Dim ar As Variant
    Dim arr As Variant
    Dim i As Long
    Dim j As Long
    Dim txt As String
    Dim n As Long
    Dim k As Long
    
    With Cells(1).CurrentRegion
        ar = .Value
        ReDim arr(UBound(ar), 6)
        
        With CreateObject("Scripting.Dictionary")
            .CompareMode = 1
            For i = 2 To UBound(ar, 1)
                For j = 1 To 6
                    txt = txt & Chr(2) & ar(i, j)
                Next
                If Not .exists(txt) Then
                    .Item(txt) = Empty
                    n = n + 1
                    For k = 1 To 6
                        arr(n, k) = ar(i, k)
                    Next k
                End If
                txt = ""
            Next
        End With
    End With
   Sheet2.Cells(2, 1).Resize(UBound(arr), 6).Value = arr
End Sub

Display More

Take care

Smallman

Smallman · Nov 17th 2015

Re: Dynamic unique row records formula or macro sample provided

Hi

You have not made yourself very clear. Your formula already gets unique values so why not point it at columns E & F??? Or did you mean you want the concatenation of the 3 relevant columns and only to get the unique items for this concatenation. A sample output on your file would have taken this confusion away.

Take care

Smallman

Smallman · Nov 16th 2015

Re: Loop through files in folder chronologically

Why not just bring the data in and sort your data by date??? I think the coding required would be minimal.

Smallman · Nov 15th 2015

Re: Inserting text from a cell with formulas

You are most welcome Len. Teachers like yourself are invaluable in society, you shape the lives of so many. Keep up the good work!!!

Smallman

Smallman · Nov 13th 2015

Re: Inserting text from a cell with formulas

Hi Len

Sorry for the delay in getting back to you. I have 3 teachers in my immediate family and I teach XL. I think your colleagues have a point – the file is a bit too busy. I have streamlined it a bit, taken some of the noise out of your file and I think there is no value lost. I have fixed your formulas so it accounts for absence. Let some of your colleges view it and get their opinion. Hope it helps.

File attached.

Take care

Smallman

Smallman · Nov 13th 2015

Re: Data Compile

Hi Kenneth

Is the "D" in a single column or the same column in each sheet?

Take care

Smallman

Smallman · Nov 10th 2015

Re: Inserting text from a cell with formulas

Hi Len

Welcome to Ozgrid.

It is always best when posting to provide a sample workbook. The people assisting will need to make one so it helps everyone if you go to a little effort up front.

Have a look at the attached. The concept you will be able to use for both. Change the numbers in your data table to suit your needs and if you change the numbers in Column D you will see the results in col E change too.

Take care

Smallman

Smallman · Nov 9th 2015

Re: copy row automatically to another sheet if value is in column G for several sheet

Hi Kitty

Welcome to Ozgrid -

This should not be too complex if you are using VBA. It should be something understandable and that you can scale. Here is one way which you should be able to adapt and understand.

Code

Sub Consolidate()
Dim sh As Worksheet
    Sheet5.[a1].CurrentRegion.Offset(1).ClearContents
    For Each sh In Sheets
        If sh.Name <> "Total" Then
            sh.Range("G1:G" & sh.Cells(Rows.Count, 7).End(xlUp).Row).AutoFilter 1, "VO"
            sh.Range("A2:G" & sh.Cells(Rows.Count, 7).End(xlUp).Row).Copy Sheet5.Range("A65536").End(xlUp)(2)
            sh.[g1].AutoFilter
        End If
    Next sh
End Sub

Display More

Sheet5 is the Total sheet and all sheets which are not named Total will be uploaded into the Total sheet if VO is in COl G.

I will post a workbook shortly to prove workings. If you need to post your file please feel free.

Take care

Smallman

Smallman · Oct 29th 2015

Re: Sorting multiple spreadsheets

Hi

Is there any reason why you need to use code to conditionally format the cells when you have conditional formatting for that sort of thing? If you were to take that step out and apply a simple loop the code could be written in only a few lines for all sheets.

Take care

Smallman

Smallman · Oct 29th 2015

Re: Changing Excel shapes automatically based on cell values

Hi

What you need to do to achieve this, Actually there are a couple of ways, the VB way and the non vb way. If you were setting up a non vb solution you would name your three identically sized shapes, Red, Amber and Green. You could add the shapes into 3 named range cells (one called Red, one Amber and one green). Now create a formula in the named range section and it should refer to a cell with a drop down containing the 3 colours (Red, Amber and Green).

If we now set up a new named range and it should look at the drop down. For demonstration purposed I will call it Test.

=Indirect(A2)

Where A2 contains the drop down.

Now copy one of the shapes and use this shape as your ‘host’, click on your host and name the shape Test

=Test

Now as you change the drop down the colour of the shape will change too. I have attached a file which should help.

I hope that helps with the non VB. I have plenty of examples of the way to colour shapes with VBA on my site. Here is one method which I do my best to explain.

Heatmap

Take care

Smallman

Smallman · Oct 26th 2015

Re: adding Subtotals below

Hi Luis

It is very difficult from your picture to work out what the criteria is to add a subtotal. The data in Column H, I or J does not distinguish itself. A file with your data would help your cause immensely.

Take care

Smallman

Smallman · Oct 20th 2015

Re: Sumifs is really slow

Hi Kay

As a start don't use the whole column in your formula. This is probably what is slowing things down.

=SUMIFS(Raw!$H$2:$H$73,Raw!$G$2:$G$73,$E2,Raw!$F$2:$F$73,G$1)/1000

Give the above formula a crack and see if that speeds things up.

Take care

Smallman

Smallman · Oct 16th 2015

Re: VBA to sum certain rows based on month

Late tot the dance but worth a look.

Code

Sub Testo()
    Range("F1:F" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter 1, "Oct"
    [U3] = Application.Subtotal(9, Range("J2:P" & Cells(Rows.Count, 1).End(xlUp).Row))
    ActiveSheet.AutoFilterMode = False
End Sub

File attached to show workings.

Smallman