Optimise Repeated Code For Object
Aug 15, 2006
A while ago I was playing with an excel balanced scorecard which used various on change events of the cells to change the colour of an object (circle). Recently I have been looking at this again and have learned that I can lose some of the .select tags to improve & reduce the code. What we have now is 20 different PI's, each with an oibject assigned, called 'PISHAPE1' through to 'PISHAPE20'. Originally, the code checked every single controll cell, which could be either red, amber, green or no data, which would be black.
What I have works but I am sure there is a more efficient method, rather than repeating the case red, amber, green etc. The first part of the code is repeated 20 times for the different objects, just changing the shape number.
Dim cel As Range
For Each cel In Range("controlcell1").Cells
Select Case cel.Text
Case "Red"
ActiveSheet.Shapes("PISHAPE1").Fill.ForeColor.SchemeColor = 10
Case "Green"
ActiveSheet.Shapes("PISHAPE1").Fill.ForeColor.SchemeColor = 3
Case "Amber"
ActiveSheet.Shapes("PISHAPE1").Fill.ForeColor.SchemeColor = 51
Case "No Data"
ActiveSheet.Shapes("PISHAPE1").Fill.ForeColor.SchemeColor = 0
'etc.
Case Else
End Select
Next
SO following on from above, the next code for PISHAPE2 is as follows:........................
View 6 Replies
ADVERTISEMENT
Feb 18, 2009
I have some textboxes named value1 through to value10, and have got consistent, sequential coding in the change event of each box to perform some actions (enable some other textboxes.)
I was wondering if there was a way to tidy my coding so that I do not have 10 change events that are essentiallly the same except for their sequential numbering. This may be impossible, as I think I have to have a macro for each change event, but thought I ask just in case there is a way.
Private Sub value1_change()
If value1 "" Then
markup1.Enabled = True
Quote1.Enabled = True
Else
markup1.Value = ""
Quote1.Value = ""
markup1.Enabled = False
Quote1.Enabled = False
End If.......................
View 9 Replies
View Related
Jun 9, 2014
I need to identify repeated code with different text value. The codes in B column appears repeatedly in the list. However some codes have different text value (one time appears with public and another time appears with priv). I would like to identify those codes as 1 if they appear with different text value within the list. I highlighted one code 131775 for reference.
Repeated code with different text.xlsx‎
View 8 Replies
View Related
Jan 4, 2010
In colmn A:A i need returned, which numbers (if any) are repeated and how often each repeated number occured.
Admitedly... I'm lost on this one.
View 2 Replies
View Related
Feb 9, 2007
Is it possible to use VBA to call an existing Excel function? I want to have code that calls up the "Insert - Object" popup window in the same manner as it would if the user clicked the toolbar.
View 2 Replies
View Related
Dec 9, 2009
I'm trying to copy all the sheets into a new workbook but the following code does not copy the workbook object called "ThisWorkbook", it does copy ALL the other sheets however.....
ActiveWorkbook.Sheets.Copy after:=Workbooks(fileDst).Sheets(1)
The "ThisWorkbook" object appears at the end of the list names "Microsoft Excel Object" after all the sheets are listed in the VBA project explorer, like this.....
Microsoft Excel Object
<icon> Sheet2(Sheet2)
<icon> Sheet2(Sheet2)
<icon> Sheet2(Sheet2)
<Excel icon> ThisWorkbook
I have code in there that is needed such as Workbook_Open() so it's needed when I copy to a new file or I have to hand copy it everytime.
View 11 Replies
View Related
Nov 27, 2009
way i could stop my sheet from flickering everytime i change my cell selection via keyboard/mouse. i checked and this problem is only with code below that i run in view code of my sheet...
PHP
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler
If Target.Count > 1 Then GoTo exitHandler
If Target.Address(False, False) = "Z3" Then
Call GoToMatch
Exit Sub
End If
View 9 Replies
View Related
Aug 14, 2007
Is it possible to make a textbox or combobox the reference of a formula in a cell?
I know that you can just do this on VBA, but if possible I don't want to do that in this particular case.
View 6 Replies
View Related
Aug 14, 2008
I have written some code that copies certain cells from a spreadsheet constructed form back to a master spreadsheet database. The idea being to prevent users of the form from accessing the database and mucking it up. I write it the long way round and am now trying to modify it to not have to open and close the database everytime BUT whenever I change it I get errors that I think relate to the code not understanding the change of object...
' 3. send updated data to the database (all yellow boxes will update)
' set parameters for cells to copy from
Dim r As Long, e4 As Long, e6 As Long, e18 As Long, e20 As Long, e22 As Long, e24 As Long, _
e26 As Long, e28 As Long, e30 As Long, e32 As Long, e34 As Long, e36 As Long, e38 As Long, _
e40 As Long, e42 As Long, e44 As Long, e46 As Long
r = Range("D2")
e4 = Range("B4")
e6 = Range("B6")
e18 = Range("B18")
e20 = Range("B20")
e22 = Range("B22")
e24 = Range("B24")
The little blocks of code go on for 17 open/closes!
View 4 Replies
View Related
Jul 11, 2007
I recently wrote the following macro to drive several dimensions in a Solidworks assembly using cell values in excel. The strange thing is that I witnessed the macro work on several occassions with the exact same code, however now it always produces "Run-time error 91: Object variable or With block variable not set". I read in another forum that macros in excel can become fragmented with repeated editing, and unloading all the macros then loading everything back up again actually worked for a little while, however I would rather fix an issue with the code if there is one:
Note: In Debug mode the lines
Part.Parameter("D1@Sketch1@CORE INNER.Part").SystemValue = _
Sheets("AEM Core and No Load Loss").Range("B4").Value / 1000
are highlighted
Sub Generate_Core()
Set swApp = CreateObject("SldWorks.Application")
Set Part = swApp.ActiveDoc
Set SelMgr = Part.SelectionManager
Part.Parameter("D1@Sketch1@CORE INNER.Part").SystemValue = _
Sheets("AEM Core and No Load Loss").Range("B4").Value / 1000
Part.Parameter("D2@Sketch1@CORE INNER.Part").SystemValue = _
Sheets("AEM Core and No Load Loss").Range("B5").Value / 1000................
View 3 Replies
View Related
Feb 27, 2008
I need to identify the object that is triggering the current running macro. For example, i have 4 buttons say button 1 to 4 that all do almost the same thing. I have written 4 different macro for all 4 buttons but i want to simplify my code so that i can have a better leaner code by only using 1 macro for all 4 buttons. simply put is there a way to say identify which button/shape i pressed:
If "identity of button pressed" = "Button1" Then
execute some code
Else
End If
View 3 Replies
View Related
Sep 7, 2007
I have a macro that checks if a username is in a particular list, and if it is, it unhides certain sheets in the workbook.
The code runs fine if I just run it as a macro or off a command button, but I am trying to execute it when the workbook opens and I keep getting a 57121, Application defined or object defined error.
The code is below;
Private Sub Workbook_Open()
DoEvents
Dim Res1 As VbMsgBoxResult
Dim GovRng As Range
For Each GovRng In Sheets("Map").Range("GovernanceMembers")
If GovRng.Value = Application.UserName Then Goto 111
Next GovRng
Exit Sub
View 6 Replies
View Related
Apr 22, 2009
I have a sample spreadsheet (uploaded to this thread) in which I have 10 command buttons named 'Video 1' to 'Video 10'. Next to these buttons is a Windows Media Player Object.
I require the code that upon clicking any of the video command buttons, the relevant video is opened and shown in the windows media object within the same sheet (sheet1).
I'm sure the code for each button will be the same apart from the cmd button reference number/name and the link to the file to be played.
If we assume all the videos are called as per their buttons i.e. Video 1.wmv, Video 2.wmv ... Video 10.wmv; and the location of these files is under 'C:Films', can someone post up some code for button 'Video 1' making it clear which variables to change for linking to the different files.
View 6 Replies
View Related
Jan 17, 2005
I'm getting the following error:
"Object library invalid or contains references to object definitions that could not be found"
I wasn't getting that error last night and I'm not sure what I may have done to cause this error.
It seems to be cause by code running on one sheet of my workbook, but I'm not really sure about that. I'm still a bit of a novice at VBA.
I'm using Excel 2002 SP3 and I'm running MS XP Home as my OS.
Do you have any ideas what can cause this error and/or how to trace down the offending objects/code?
View 9 Replies
View Related
Sep 8, 2006
I need my program to:
- find the cell containing the string "Datum/Tid"
- record the column and the row of the found cell in two variables lCol and lRow
Here is my
Sub test()
Dim rFoundCell As Range
Dim lRow As Long
Dim lCol As Long
'Find method of VBA
Set rFoundCell = Range("A1")
Set rFoundCell = Worksheets("Sheet1").Range("A1:Z50").Find(What:="Datum/Tid", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
'for anyof the two lines down I get the message "object variable OR block variable not set"
lRow = rFoundCell.Row
lCol = rFoundCell.Column
End Sub
View 5 Replies
View Related
Apr 26, 2006
I am getting an 'application defined or object defined' error at the code coloured red. I'm trying to sort a list of names starting at c5
'Update Names on Sheets
If Sheets("Daily Visits May").Range("e1") = "y" Then Goto 10
If Sheets("Daily Visits May").Range("d1") > 38837 Then Else Goto 10
Sheets("Daily Visits Apr").Select
Range("b5:c660").Select
Selection.Copy
Sheets("Daily Visits May").Select
Range("b5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
View 3 Replies
View Related
Jul 22, 2014
I have two sheets, where I am trying to look up the value from one sheet and post it to the other.
On sheet 1 I have the Names of the Individuals, On sheet 2 I have the Names of the individuals in a different order and then in the next column i have the amount of dollars they spent on a product.
The reason I don't think I can do a simple vlookup here is that sometimes the names are listed Multiple times each because they made seperate purchases.
So for example
Sheet 1 SHeet 2
John Doe Jane Doe $1,000
John Doe Michael Jordan $2,000
Jane Doe John Doe $3,000
Michael Jordan Michael Jackson $4,000
Michael Jackson John Doe $5,000
If I did a vlookup, I assume that John Doe would return $3,000 for both. I would like to return $3,000 for one and $5,000 for another.
View 1 Replies
View Related
Sep 4, 2013
Is there a quick command that I can use to take
math
math
math
math
art
art
art
art
art
music
music
music
music
into
math
art
music
View 6 Replies
View Related
Mar 12, 2009
I am trying to solve a column total where rows that have a repeated column A value only have their column B value totaled once.
For example the table:
A B
item1 20
item1 20
item2 50
item3 30
The total I am looking for column B is 100 (20+50+30) and not 120, as item1 should only be added once.
I made a few forum searches I made and did not find my answer, though I suspect this is a repeat question. I am guessing there may be a sumif(frequency solution out there that I'm just not hip to yet.
View 9 Replies
View Related
Nov 7, 2006
In the attached file column A has ID's. In the attached example i have used 2 ID's 141020061 & 151020062. I need to find the last entry of each ID and sum the value from the column F. that is the last entry for the ID 141020061 is 40500 and for 151020062 is 0 so the total should be 40500.
View 9 Replies
View Related
Dec 9, 2013
Would like to have a formula for removing duplicate row values from the attached sheet.
In simple, in the first row, Name1 and Name2 are repeating. And the requirement is to remove the repeated value "A" from Name2.
View 3 Replies
View Related
Dec 9, 2013
I have 1 to 30 spread sheet plus a Sheet with TOTAL , each represents one day ,
on each sheet I have one column and 6 lines where I will please random numbers from 1 - 50 but six number on each sheet
The question is on the total sheet I would like to have automatically the number repeats the most on those 31 sheets .
View 1 Replies
View Related
Oct 28, 2009
i have one Q??
i have these times
07:00
07:43
04:55
07:43
is it possible to COUNTIF the repeated hours? the result should be 3 "07:??"
View 14 Replies
View Related
Sep 14, 2009
how to delete repeated rows and just keep the first one found?
View 13 Replies
View Related
Aug 11, 2008
I have a table with some reapeated words in, I wondered if anyone had any methods of counting words and displaying the total in number format. for example if I had in cell A1-Hello and A2-Hello and A3-Bye how can I get Hello = 2 and Bye =1
View 9 Replies
View Related
Sep 15, 2008
I have a list of products more than 5000 positions (rows)
Some positions are repeated (It could be 1000 rows with "Pen" position)
Based on this I need to create new short list which will contain the list of ONLY UNIQUE positions.
The same function is: DATA - FILTER - ADVANCED FILTER - UNIQUE RECORD ONLY
It is only filter, but i need the same functionality for new short unique list
View 9 Replies
View Related
Oct 6, 2008
I have 1-d array that gets populated from a number of sheets.
This array may contain duplicate entries... for example,
let's say this array contains animals.. cows, chickens, horses, pigs... etc.
What I would like to do is take this info from this array and create another where it would contain all the different types of animals but without all the repeats.
View 9 Replies
View Related
Nov 13, 2008
I have the regular headers in the first row, followed by rows containing my data. And at x number of rows, a new row containing headers appear again, followed by rows containing data. This goes on for the rest of report. What I need to do is remove the rest of the headers and lines because I only need to have one header in the first row.
Note that the header takes up two rows (one for the actual headers and one for the ====== lines. So removing the repeated headers is removing 2 rows.
For eg, part of the report looks like this:
PRODUCT PRODUCT NAME ==============================================12342EXAMPLE PRODUCT 122525EXAMPLE PRODUCT 2 35476EXAMPLE PRODUCT 3 53467EXAMPLE PRODUCT 4
PRODUCT PRODUCT NAME ==============================================135892EXAMPLE PRODUCT 5
View 9 Replies
View Related
Aug 3, 2009
I have a sheet "Pattern" with a series of statements in column A, starting at row 12. These contain a lot of common words like 'a', 'and', 'that' etc which I would like to automatically remove. On a separate sheet 'Library' in column D I have a list of these common words which I would like to look through one by one and remove from the statements on the 'Pattern' sheet.
The code I have at the moment looks like,
Sub CommonWords()
For i = 3 To 65536
Dim w3 As String
w3 = Sheets("Library").Cells(i, 4).Value
Sheets("Pattern").Range("A12:A65536").Select
Selection.Replace What:=w3, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i
End Sub
But this only achieves to remove the contents of the 'Pattern' sheet, column A..?? Where am I going wrong? Also, with this method if I find and replace 'a' with the 'LookAt:=xlPart' option, is this going to remove all 'a' 's, even if they are in the middle of a word?
View 9 Replies
View Related
Jan 16, 2008
I have a spreadsheet (>10,000 rows) with data referring to a grid cell map. Each grid cell on the map has an ID and associated data. About 10% of the grid cells have duplicate records, but with different data. I need to produce a spreadsheet with a single record for each grid cell that contains the data from each of the duplicate grid cells. Some occurrences may be >2. Example below.
Current
[TABLE]Grid#;Data
1;a
1;c
2;b
3;a
4;b
4;c
5;d
5;d
5;f
[/TABLE]
Needed
[TABLE]
Grid#;Data1;Data2;Data3
1;a;c
2;b
3;a
4;b;c
5;d;d;f
[/TABLE]
View 6 Replies
View Related