Routine For Automatic Distr. Of Point
Oct 22, 2008
I have a number of countries that will distribute an amount of points. And then each country distribute that amount of points( % taken of total points to distribute between countries) between his cities, BUT here the distribution depends on the importance of the city.
Now when I have two cities, for example Madrid and Sevilla, it doens´t matter which one get the most point (as they are in the same importance of the city), but they must have more points that a city in category "B".
here is an example
Here I have the countries with there % of distribution of the points
Spain25,25
France50,64
Belgium12
Holland12,11
Total100%
Total points to give away522
Amount to distribute
Spain25,25132
CityImportanceAmount distr
Madrid A 48
Barcelona B 20
Sevilla A 34
Granada C 7
Valencia B 23
SUM 132
France50,64264
CityImportanceAmount distr
ParisA ….
LyonB ….
BordeauxA ....
OrlansC ….
I thought maybe using some kind of command as RANDOM() or so to do this.
View 9 Replies
ADVERTISEMENT
Feb 22, 2007
I am in the process of re-building a major project due to bugs. In the process, I plan to re-organize my sub-routine grouping within modules. In the source file, I have 18 standard modules, with 10-30 routines in each one.
What I would like is a printout of each routine name and which module it resides in, so I can check off each one as I copy it and paste it into the new file. I see that I can get a psuedo tree-view of my project in th eObject Browser, but can't figure out how to print that.
View 9 Replies
View Related
Dec 10, 2008
I have a pivot table as shown below which is pretty straight forward however I am trying to create a macro that will automatically change the point name to match the point name in column E then copy that resulting dispaly to another sheet then pick the next point name in line and do the same thing and repeat for 50 rows, so my end result will be 50 pivot tables ready for printing. I can do this manually but I am trying to make it automatic...BTW the point names in column E change everyday but the pivot table supports the name changes.
Option Explicit
Sub PointName()
Dim Ws As Worksheet
Dim Rng As Range, Cel As Range
Set Ws = ActiveSheet
Set Rng = Range(Cells(2, 7), Cells(Rows.Count, 7).End(xlUp))
For Each Cel In Rng
Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
Ws.Columns("A:B").Copy
Sheets.Add
With ActiveSheet
.Paste
.Name = Trim(Cel)
.Range("A1").Select
End With
Next
Ws.Activate
End Sub
I turned it off for a while and when I turned it back on I am getting an error
Unable to set the _Default property of the PivotItem class
Debugger is highlighting
Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
View 9 Replies
View Related
Jun 7, 2014
Here is what I am trying to do: I have 30 duplicate worksheets that I enter data every 3 months. I have a macro that clears certain fields and copies data from one field to other and I run this macro after selecting each sheet one at a time. How can I create a macro that will run this macro from the 1st sheet through to the 30th sheet in one click. My macro is called Clear_data.
View 12 Replies
View Related
Jun 24, 2014
I have a macro file
when I run the macro it activates a sub macro called: Find_Empty_cells_win
when this is run it picks up a empty cell in Column G
When this happens I get a Msgbox prompt and then I click on OK.
After this it continues on to the rest of the code.
How do I completed exit the vba coding right after I click on OK
View 5 Replies
View Related
Feb 20, 2009
All I want to do is to stop a routine when I press the Cancel key on the user form that the routine called. I have tried "stop", "quit", "end", "abort", et. al. This seems so simple but I cannot figure out how to do it.
View 7 Replies
View Related
Jun 13, 2014
I have an add-in that can run the code below. It works fine, however, I need it to run whether there is just one row of data, or 100 rows of data. I have headers, so the first row of data starts in row 2, and the last no matter what will be in row 100 (so 99 rows of actual data technically). There are usually only 10 rows of actual data as the rest are usally set to blanks with iferror. The code below works, but I need it to run for however many rows of actual data there are.
View 1 Replies
View Related
Feb 4, 2014
I've got three workbooks that end users will be using for data entry. The VBA code for the three workbooks is identical.
I'm trying to encapsulate the code into an add-in so I only have one copy of the code that I have to debug/maintain.
Furthermore, I've also encapsulated the code in the add-in into a class. The class isn't really a "usual" object modelling some data; rather, it's a utility class processing Worksheet_Change and Worksheet_Activate events. Hopefully this isn't a really bad idea, rather than keeping the code in standard modules in the add-in.
The application is called "Midas". Here are some code excerpts:
Midas.xlam:
Standard Module:
[Code] ......
Class Module:
[Code] .....
And in the end user workbook:
[Code] .....
The Wbk_SheetChange event handler is triggering, but it's looking for the code in the end user workbook, instead of the class. Is there a way I can change
[Code] .....
to call the private routine "Cases_Changed" (where "Cases" is the sheetname) residing in the class instead of looking for it in the workbook?
View 4 Replies
View Related
Nov 3, 2009
I have 2 subs routines in a mod. I have declared the two variables at the top of the sub. However when I call the second sub the variable's are not passed along.
Here is an example script. All in one mod. I have taken out the junk in between to help edit the problem.
I'm looking to pass the same bnumber and dnumber to the secound sub.
View 2 Replies
View Related
Nov 6, 2009
How do I have a workbook execute VBA code when I hit 'Enter' anywhere on a specific sheet? I don't need the code to execute when I hit 'Enter' on any of the other sheets in the workbook, just a specific sheet.
View 11 Replies
View Related
Dec 12, 2011
I've written a Sub that separates words in a sentence into columns in an excel worksheet and it works perfectly. I can't however convert the convert it to a function procedure. when I do so I receive a Circular referencing problem or upon making necessary corrections to prevent circular referencing, I get the Excel #Value error. Here's the code:
Function TextToColumns(Txt) As String
'
' Enters Text Separated By Spaces Into Columns
'
Dim i As Long
Dim k As Long
Dim cell As Range
[code]......
View 1 Replies
View Related
Dec 17, 2012
I currently have a macro that runs every 10 seconds. This macro calls 5 sub-routines.
Code:
Public Sub Copy_Data()
*****code****
*****code****
*****code****
*****code****
Call SortData
Call Create_OutOfStock_File
Call Create_NearEmpty_File
Call Create_InStock_File
Call Create_Other_File
Application.OnTime Now + TimeValue("00:00:10"), "Copy_Data"
End Sub
I need to keep the current code to run Copy_Data every 10 seconds, however, i would like:
Code:
Call Create_OutOfStock_File
Call Create_NearEmpty_File
Call Create_InStock_File
Call Create_Other_File
To be called/run every odd minute, for example, 00:01:00, 00:03:00, 00:05:00
View 6 Replies
View Related
Apr 8, 2014
I am writing a lot of code and have separated each section in different modules For example, I have one module titled OpenR2D2 and another titled StoreName
I want the sub in StoreName to run at the end of OpenR2D2 but I get this compile error "expected variable or procedure, not module"
Here's the end of the code where I put it. What have I not thought of?
ThisWorkbook.Activate
Range("B9:G9").PasteSpecial
R2D2data.Activate
Range("B7:G7").Copy 'Sch Actual VLH
ThisWorkbook.Activate
Range("B12:G12").PasteSpecial
Call StoreName
R2D2data.Close Savechanges:=False
Application.ScreenUpdating = True
End Sub
View 4 Replies
View Related
May 2, 2007
I am trying to develop a routine that will save the file I am currently working on, then save the current tab as a *.csv file with the name equal to cell "C1" in the same directory as the original file. Then I would like it to close the .csv file and reopen the original file all while being generic with the naming so that if the file name and location changes, the script will still work. I recorded a macro to get me started.
View 9 Replies
View Related
Oct 9, 2007
I have a macro that formats an imported report and I have inserted it's code into several other routines I have written.
Since there is always the possiblity of changes occurring, is there a way to refer to this macro in a second (or third or fourth) routine so that if a change has to be made to the original macro, I won't have to change it everywhere?
View 9 Replies
View Related
Sep 29, 2009
I received a great little routine from you guys to a question which was a follows
Can Excel do this?
I have a huge spread sheet - The formulas in each cell reads as follows:
='[1.xls]Community Libraries'!$A$9. I would like to copy the cell all the way down the column, but only 1.xls must change to 2.xls and 3.xls etc. Can Excel copy this way?. I'm using Excel 07 on this pc
The response was:
Sub PutFormula()
For i = 1 To 80
Range("A" & i).Formula = "='[" & i & ".xls]Community Libraries'!$A$9"
Next
End Sub
Can this be modifed to:
A) Start on row 6 and end on row 85 of each Column A to CZ
B) Modify the end bit of the formula as follows Community Libraries'!$A$9&"/10"
View 9 Replies
View Related
Feb 27, 2010
I have a useform with about 30 Text boxes for user input.
when the user exits the box I want a validation check routine.
I need to verify 4 things.
1. That the entry is numeric.
2. Thew at the entry is above the minimum.
3. The Entry if below the maximum.
4. Number of decimal places.
Since I have 30 boxes and will be adding more in the future I need a Subroutine.
Here is what I have (I know it does not work - I need help with the syntax.
Code: .....
View 9 Replies
View Related
Apr 10, 2007
Userforms Causes Sub Routine To Exit ...
View 9 Replies
View Related
Feb 11, 2009
I am trying to complete a toolbar that will lock and unlock all the sheets in a workbook. While I can set the password automatically I'd like to have the macro ask for the password instead of hardcoding it.
Here is the code I currently have and works so long as I have already set the password in the workbook to match what is in the macro
View 3 Replies
View Related
Apr 14, 2009
I have a Sub test where I think can be setup in an Array or a shorter sub routine, how to shorted this routine. Ths routine was designed to check your Computer and depending if you are one of the selected on will the unprotect the sheet.
View 2 Replies
View Related
Dec 10, 2009
I want to thank you all for the solutions/support I received in completing my (for me) difficult workbook.
I still have a tricky problem. In the example below, can Multibeep "know" when it is being called from Beep versus being excuted directly? And if so can I direct a different sequence?
View 10 Replies
View Related
Nov 1, 2012
In my code I am using several variables to create a concatenated string and this is making my code quite lengthy..Now I have manged to use it in the SELECT CASE but it still remains big..
SO I was thinking if it were possible to break it into smaller parts and create a Sub-Routine which can be called in the Main code in the select case for the specific condition this way I can use it in Multiple places without hassles..
find the code which I need passing the variables..
Code:
Sub Condition_I()
Dimm Search_Value as string
Dim INo As Long
Dim Whole_Text As String
Dim Lookup_No1 As String
Dim Lookup_No2 As String
Dim Condition_I_Text As String
Dim WksI As Worksheet
[code].....
I need to pass the Search_Value from the Main Code and also the whole_text back to the Main Code..
View 9 Replies
View Related
Aug 16, 2007
How can I edit the following code. If the "Dload" tab is not there the code errors out. How can I make it so that if the "Dload" is not there the macro says "Dload tab missing. Do you want to check file and try again" If the user says yes. then the macro lets the user pick another file. If the user chooses No then does similar quesiton like I have here starting with
OpenA.Activate.
ChDrive "E:"
ChDir "E:2007 WorkAnalytical ReviewBalance Sheet"
View 9 Replies
View Related
Aug 23, 2008
I've posted the simple sheet elsewhere and others report that the sheet and routine works fine for them. The exact same sheet that is failing for me.
The algorhithm itself works for me too - everything is parsed correctly and the proper range successfully receives the .ClearContents command. It's just that, on my sheet, nothing happens.
I have the entire book and sheet unprotected, all the cells unlocked, and permission for all macros to run. I.e. I've removed every possible "excel-level" roadblock I can think of. I've tried restarting Excel, even rebooting -- all with no luck. The page still doesn't work.
What other possible settings might be causing the consistent failure when I try to run the sheet whereas other users (reportedly) have no such problem with my VBA routine and their identical copy of my sheet?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Long
Dim clearTable As String
Dim clearTrigger As String
Dim clearRegion As String
With ActiveSheet
clearTable = Range("ClearRegions").Text ' Get clear table.................
View 9 Replies
View Related
Apr 15, 2006
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Application.EnableEvents = False
If Intersect(Target, Range("E:E")) Is Nothing And _
Intersect(Target, Range("H:J")) Is Nothing Then
Exit Sub
End If
Is there a way to make this run a little slicker , as at the moment it is taking almost 10 mins to update RngDate is $L6:$FB1038
View 9 Replies
View Related
Jun 29, 2006
I was able to get my four conditional statements in VBA to work. This covers one complete row of data (week), but I need it to cover an entire year. Rather than cut and pasting 365 times (and some UGLY code at that), I have been trying to figure out how to add a loop or something to cover a range of different rows. Basically Columns B though V are good for row 21. Now every 14th row I need this routine to work for (B35:V35, B49:V49, etc) for 52 weeks. I was playing around with the idea of two for statements taht would count shifts (21 = 7 * 3) and weeks (52). Then step the rows at 14 and the columns at 1 (Rows = Rows + 14) for 52 iterations. I'm sure there is a better way to distribute this routine.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim changeRange As Range
Select Case Mid(Target.Address, 2, 1) 'Column Letter
Case "B"
Set changeRange = Range("B21")
Case "C"
Set changeRange = Range("C21")
Case "D"
Set changeRange = Range("D21")
Case "E"
Set changeRange = Range("E21")
View 9 Replies
View Related
Feb 15, 2007
I've wriiten the following routine :
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' Unhides all columns prior to saving
ActiveSheet. Unprotect password:="test"
Cells.Select
Selection.EntireColumn.Hidden = False
Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, password:="test"
Response = MsgBox("Have you considered all the items on the checklist?", vbYesNo)
If Response = vbYes Then
Go_To_Checkbox
I want the routine to stop without saving (after running Go_To_Checkbox) if the answer to "Have you ticked the checklist checkbox?" is 'No'. I know the 'Stop' function will do it but I just want to exit the routine without saving, rather than the routine stopping and calling up the Visual Basic editor.
View 3 Replies
View Related
Sep 26, 2013
I'm using a Private Sub Worksheet_Change(ByVal target As Range) macro on Worksheets(2) to perform certain actions when a selection from Worksheets(1) is pasted to Worksheets(2). Before ending the Worksheet_Change sub I'd like it to deselect the copied selection on Worksheets(1). I've tried to do this by selecting Worksheets(1) and moving the cursor, but VB ignores the "With Application.Worksheets(1)" instruction. Removing "Private" from the sub heading makes no difference.
Is it possible do this with a Worksheet_Change routine? If so, how?
View 7 Replies
View Related
Oct 17, 2013
Here's an array I have that is filled with values that come from a worksheet. I'd like to have it so that once this array is full the code that fills it up (see below) gets skipped on subsequent runs of the subroutine. What's a good way to do this? Seems like when I Dim the Array it gets blanked.
Code:
'Fill Bulb Array with House number (j) and Bulb Wattages
j = 1
Do While j
View 3 Replies
View Related
Jul 18, 2009
For Each wksht In ActiveWorkbook.Worksheets
i = i + 1
ReDim Preserve wkshtnames(1 To i)
wkshtnames(i) = wksht.Name
x = wksht.Range("A1:B2").Value
Next wksht
View 9 Replies
View Related
