Solved

Excel Automation

Posted on 2011-09-28

Microsoft Excel

2 solutions

167 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

Comment

Question by:cpatte7372

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

7 Comments

LVL 24

Expert Comment

by:StephenJR

ID: 367167902011-09-28

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?

LVL 37

Accepted Solution

by:Neil Russell

Neil Russell earned 250 total points

ID: 367168292011-09-28

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

Author Comment

by:cpatte7372

ID: 367180532011-09-28

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

Author Comment

by:cpatte7372

ID: 367180612011-09-28

Stephen, the cells tha would activate it would be cells AW5:AW17

Cheers

LVL 24

Assisted Solution

by:StephenJR

StephenJR earned 250 total points

ID: 367180962011-09-28

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

Author Comment

by:cpatte7372

ID: 367203992011-09-28

Neil/Stephen,

Cheers guys

Author Closing Comment

by:cpatte7372

ID: 367204022011-09-28

Cheers guys

Featured Post

Question has a verified solution.

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

Automating Microsoft Excel from Access (Part II)

Article by: Dale Fye

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.

Dynamic Data Vaildation

Article by: Rob

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.

Building Probability Models in Excel Part 5: Modeling an Investment Using Discrete Random Variables

Video by: Toby

The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…