Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

Excel Automation

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
cpatte7372
Asked:
cpatte7372
Advertise Here
  • 4
  • 2
2 Solutions
 
StephenJRCommented:
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
 
Neil RussellTechnical Development LeadCommented:
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
 
cpatte7372Author Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Sign Up
 
cpatte7372Author Commented:
Stephen, the cells tha would activate it would be cells AW5:AW17

Cheers
0
 
StephenJRCommented:
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
 
cpatte7372Author Commented:
Neil/Stephen,

Cheers guys
0
 
cpatte7372Author Commented:
Cheers guys
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Access It Now
  • 4
  • 2
Advertise Here
Tackle projects and never again get stuck behind a technical roadblock.
Join Now