Solved

Excel Automation

Posted on 2011-09-28
7
163 Views
Last Modified: 2012-05-12
Hello Experts,

A clever expert assisted me with the following formula. However, he appear busy and I was wondering if someone could show me how to make it run dynamically without having to hit F5 or click run?

Sub compile_duplicates()
    colStart = "AW"
    rowStart = 5
    cellStart = colStart & rowStart
    For Each c In Range(cellStart & ":" & colStart & (Cells.SpecialCells(xlCellTypeLastCell).Row)).Cells
        With WorksheetFunction
            colnum = .CountIf(Range(cellStart & ":" & colStart & (Cells.SpecialCells(xlCellTypeLastCell).Row)), c)
            If colnum > 0 Then
                yaxis = .Substitute(Left(Cells(1, colnum + Range(colStart & 1).Column).Address, .Find("$", Cells(1, colnum + 1).Address, 2)), "$", "")
                If .CountIf(Range(yaxis & ":" & yaxis), c) < 1 Then
                    xaxis = .CountA(Range(yaxis & rowStart & ":" & yaxis & Cells.SpecialCells(xlCellTypeLastCell).Row)) + rowStart
                    Range(yaxis & xaxis) = c
                Else
                End If
            Else
            End If
        End With
    Next c
End Sub


Cheers

Carlton
0
Comment
Question by:cpatte7372
  • 4
  • 2
7 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 36716790
Carlton - you could make it run every time a cell is changed, or selected, or double-clicked or right-clicked etc. What would you like? If the former, which cell(s) should activate it?
0
 
LVL 37

Accepted Solution

by:
Neil Russell earned 250 total points
ID: 36716829
You could just add

Private Sub Worksheet_Change(ByVal Target As Range)
        Application.EnableEvents = False
        compile_duplicates
        Application.EnableEvents = True
End Sub

Into the worksheet  code module IF you wanted it fire on EVERY change to that worksheet
0
 

Author Comment

by:cpatte7372
ID: 36718053
Hello guys,

Sorry for the long delay in responding.

Neil, I'm not sure if it would be a good idea to have formula fired on EVERY change as the spreadsheet is literally changing every second.

Therefore, Stephen, it would be great if you could assist in having the spreadsheet just fire when cell chnages in column AW.

Cheers
0
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 

Author Comment

by:cpatte7372
ID: 36718061
Stephen, the cells tha would activate it would be cells AW5:AW17

Cheers
0
 
LVL 24

Assisted Solution

by:StephenJR
StephenJR earned 250 total points
ID: 36718096
Just add this line in Neilsr's code before the false line.
If Intersect(Target, Range("AW5:AW17")) Is Nothing Then Exit Sub

Open in new window

0
 

Author Comment

by:cpatte7372
ID: 36720399
Neil/Stephen,

Cheers guys
0
 

Author Closing Comment

by:cpatte7372
ID: 36720402
Cheers guys
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question