Macros To Achieve Objective

Oct 5, 2009

I created two macros to achieve my objective. I used the "record a macro" function, which probably creates very ugly codes. My first code formats raw data in a certain fashion so that it can be easily extracted; I understand this code fairly well and have been able to make alterations to it with ease.

The second macro takes values from the formatted data and plugs them into a table. Unfortunately, the code that Excel wrote is very confusing to me.

Essentially, the data follows a pattern (new entries occur every 14 rows), but Excel's automated code is tedious to program. Here's an example:

Columns("R:R").Select
With Selection.Interior
.ColorIndex = 11
.Pattern = xlSolid
End With
Range("T2").Select
ActiveCell.FormulaR1C1 = "Acct"
Range("U2").Select
ActiveCell.FormulaR1C1 = "Org Name"
Range("V2").Select
ActiveCell.FormulaR1C1 = "Pmt Type"...............

View 10 Replies


ADVERTISEMENT

Saving Solver Objective Function Value With A Macro

Mar 23, 2013

I have written this macro below to run solver multiple times. It saves the solver results on a line and then inserts a new line for the next solver run.

I would like to save the final Objective Function Value from solver in each line as well as its result. Is there any way to do this?

Sub run()
'
' run Macro
' run solver
'
' Keyboard Shortcut: Ctrl+n
'
Application.ScreenUpdating = False
Range("B4:T13").Select

[Code] .........

View 2 Replies View Related

How To Achieve The Result Of Cell Date Activation

Dec 8, 2012

I want to activate a specific cell on a specific day...namely today.

I have a excel spreadsheet with various dates that when it reaches today i want another cell to activate and be shifted to the active cell.

I have the following columns:

Date of transaction Earned Points this transactionActive PointsActive date of pending points Pending Points

07 November 2012 R 40.00 08 December 2012 R 40.00

Now, i want the pending points to activate and be shifted to active points on 08 december 2012. my active date of pending points columns is automatically calculated from 07 november 2012 by formula =C3+31

View 1 Replies View Related

Purchase In Order To Achieve A Particular Breakeven Poin

Jul 28, 2009

I trade the stock market and am trying to get excel to automatically calculate the required quantity of shares to be purchased in order to achieve a particular breakeven point. Question: How many shares required at a price of 37.30 to achieve a breakeven of 36.24 when I already have 8000 shares at a price of 34.73? I'm looking for the formula to solve this not just the answer.

View 9 Replies View Related

Using VLookup / Pivot Tables To Achieve Reused Formulas

Feb 13, 2013

I am tired of doing the same amount of repetitive work, I would like to know if it is possible to acheive a "template" where I just drop a database pull and it negotiates where the data should fall.

When I pull data, I have the following values I need -

Part number
Date - which is by day, I need grouped by Monday - Sunday to seperate amounts ordered by week.
Branch plant - East and West
Order Quanity which is placed by day
QOH - Quanity of parts I have on hand

I need the days grouped by weeks, 7 days by date equal 1 week.
I need summing part number amounts due within that 7 days into one total.

I would need 4 columns
1) Demand due
2) Supply due
3) QOH
4)Difference

I need each branch to have their own sheet, for a total of 4 sheets... Supply and Demand for both plants.

What it boils down to is I need to know what the demand is per week, and the supply I have to meet that demand, broken down like the attached sheet.

Seems my sheet is too large due to the amount of data for the site, but here is the link to the LARGE File...

[URL] .....

View 8 Replies View Related

Calculate Number Required To Achieve Target Percentage?

Mar 28, 2012

I am trying to create a formula to calculate the total number of calls that I need to receive to hit the target % if the current % is less than the target %. I have tried various formulae, but not having much luck. Also, it shouldn't show me any negative figures.

A = Departments
B = Calls Dropped
C = Calls Received
D = Current % (C/F)
E = Target %
F = Total (B + C)
G = Required (When I enter B, C and E, the rest needs to calculated automatically. G should tell me the amount of calls I need to receive in order to achieve the target specified in E)

Departments
Calls DroppedCalls ReceivedCurrent %Target %TotalCalls Required to hit TargetDept 1
21083.33%95.00%12 Dept 2
067100.00%90.00%67 Total
27791.67%95.00%79

View 7 Replies View Related

Number Of Promoters Needed To Achieve Target Greater Than 75%

Oct 5, 2013

I need to get right formula for below details:

Promoters
Neutral
Detractor
no.of Surveys
Percentage
Target
Promoter
needed >75%

Smith, John
5
2
1
8
50.0%
75%
???

Formula to get Percentage: =(Promoter-Detractor)/Neutral

Need to know how many numbers of Promoters needed to get 75.00%

View 1 Replies View Related

Formula For Number Of Perfect Ordered Needed To Achieve Certain Target

Jun 11, 2014

I was working on a chart to calculate my Amazon seller rating to see how many more perfect order I need to hit my next target grade. Here is how the calculation works.

Order with no problem gets 100 points per order
Minor problem gets 0 point
Moderate problem minus 100 points
Severe problem minus 500 points

Grade are evaluate by:
<84.5 Fair
<96.5 Good
<98.5 Very good
Above is Excellent

Total points / total orders = final grade (or percentage)

As of now i got : 53800 (points) / 624 (orders) = 86.22(%)

Now that I know what grade I've got so far and I also want to know how many more perfect orders I need to achieve my next targets or in case some bad orders come by and how far they pull me down.

Attached is my work sheet so far and I'm just missing the calculation for the orange shaded part.

Amazon seller rating calculator.xlsx‎

View 3 Replies View Related

Formula To Determine Required Values To Achieve Target Percentage?

Aug 24, 2014

Data is;

83300 - hypothetical number of times I have fired my gun at target.
43209 - hypothetical number of times I have scored bullseye.

So, my bullseye percentage = 43209/83300, or ~51.87%.

Need formula to determine how many more consecutive bullseyes I need to shoot, in order to achieve 70% ratio.

Since each shot from now on will be a bullseye, both values (hits & shots) will increment together.

View 5 Replies View Related

Conditional Formatting To Achieve Traffic Light Results For 3 Different Values?

Jan 7, 2014

I am wanting to have conditional formats so the cell fill in a range on sheet 1 will change to:

- red if the value of a cell on a seperate sheet (date) is greater than 18 months old
- yellow if the value of a cell on a seperate sheet is 'In progress'
- green if the value of a cell on a seperate sheet (date) is less than 18 months old

View 5 Replies View Related

Encourage Enabling Of Macros: Disable Macros When Opening Then The Worksheet Menu Bar And Other Command Bars Are Still Available

Aug 26, 2009

I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.

View 6 Replies View Related

Enable/Disable Macros When No Macros Are Present

Jul 28, 2008

I have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.

I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.

No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?

View 9 Replies View Related

How To Delete Another WorkBook Macros Using Macros

Jul 23, 2005

What is the commands or script for deleting a macro automatically using
another workbook macro.

View 9 Replies View Related

Hiding Macros From The Run Macros List

Jul 14, 2008

Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.

View 9 Replies View Related

Macros Really Slow The Another Macros Is Run

Dec 14, 2009

I have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)...
For k = 1 To numrows
Selection.EntireRow.Insert
Next k

Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...

For l = lastrow To 12 Step -1
If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then
Rows(l & ":" & l).Select
Selection.Delete Shift:=xlUp
edelrows = edelrows + 1
End If
Next l

Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.

View 11 Replies View Related

Version 4 Macros And Vba Macros In Them

Apr 13, 2007

how to use excel 4 macros. I do not even know where to put them. I have inherited some spreadsheets that have both Xcel 4 Macros and vba macros in them. (Well, I get a warning that I cannot switch them off when I disable macros on opening a workbook). I cannot even find the offending version 4 macros so that I can re-code them in VBA.

View 2 Replies View Related

Macros From One PC To Another

Feb 22, 2008

how do I install a macro from my laptop to another?

View 9 Replies View Related

Saving The Macros.?

Apr 23, 2009

I'd like to be able to just open the spreadsheet up and have the macros already saved in there. Is there a way to save these macros?

View 2 Replies View Related

How To Run Macros Without Excel

Jul 15, 2014

I am currently volunteering for a small organisation. I have spent the last week designing a userform to be used by other staff members in their office. However, when I asked the manager 'Do you have Microsoft Office?', I got a very firm and definite 'yes' in response.

It turns out they are using LibreOffice, which is a free alternative. I have also tried to get my userform to work with Open Office - but cannot seem to get either to work.

I have changed the settings in both suites of software to 'Load/Save Basic Code' and enabled macros to run via the security options.

My userform is not that complex. I have limited knowledge of VBA and zero experience using Basic, so have come up stuck.

View 2 Replies View Related

USe Of Wildcards In Macros

Mar 5, 2007

I need to write a macro whereby it queries a cell and if there is any value (it will be either blank or contain characters) in the cell a new row is to be inserted directly above the cell.

View 13 Replies View Related

Colour Macros

May 11, 2007

i have a matrix with all possible values.

i need to develop a macro, which will change the color of the cell according to the value of the cell.

for example if a specific cell in the matrix has a negative value then the macro should change the color of the cell to blue. if its value is positive then the macro should change the color of the respective cell to red.

View 9 Replies View Related

New To Macros - How Do I Create And Run One

May 18, 2007

I have an API that continuously gets stock price. The price appears in one cell at all times - let's call it A1

I want to record the value in a different cell every time it changes, so I can analyze the data.

so A1 = 15
B1 = 15

little later
A1 = 15.2
B2 = 15.2

I did some googling and came to a conclusion that I needed to write a macro.

I see that there is a function or event called Worksheet_Calculate()

Apparently it is supposed to do whatever when a value changes (per calculation)

How do I write a macro, and then execute it?

View 10 Replies View Related

Making Add-in Macros Available

Aug 2, 2007

I've created an Add-in (.xlam file) in Excel 2007 and installed and activated it (it shows up under "Active Application Add-ins" on the Add-in menu for new workbooks).
I can see the macros and code when I go to the VBA editor, but the macros don't show up in the regular macro list.

I know that I can write code for the new workbook that will reference the Add-in code, but I want other users to be able to install the add-in and run the macros in it right away without any knowledge of VBA. Is there a way to make this possible?

View 11 Replies View Related

Where To Place The Macros

Nov 6, 2007

I have created a excel spread sheet to be set up like a schedule, including multiple macros through the sheet, when I started it put the macros into "Module 1", now if I wanted to use this same worksheet and copy it and create multiple sheets in one file via the tabs does it make a difference where the macros reside? if it remains in "Module 1" it will only be in one spot will this create problems later on or should the each reside in the sheet objects.

View 9 Replies View Related

Using Macros Across A Sheet

Feb 3, 2008

how do I use a macro across a spreadsheet?I can record a macro OK,but when I try to use it again on the same spreadsheet all I get is the macro calculating using the same data as when I recorded it.I have several groups of data across one spreadsheet and I want to use the same macro over the entire sheet.How do I do this?

View 14 Replies View Related

Macros On Server

Nov 5, 2008

I have a spreadsheet which I used in a previous job, which was set up by our old IT Department. Basically, it allows me to save information onto it, which I can then retrieve, (in any conditions/search fields i.e. in a date range etc) but the problem is; when I try to use it on my laptop, I look at the editor used for macros, I can see that the spreadsheet is linked to the servers of my old company. Therefore, I cannot use it myself

I do not know a lot about macros etc. but I can send a copy of the spreadsheet to anybody who is willing to help!

I think it is just a case of changing the macros/strings of where to draw and save information to/from, but I don;t know.

View 10 Replies View Related

Matching Using Macros

Feb 26, 2009

I have some text in $a$1.

In Column B I have all kinds of stuff, including the text in $a$1.

How do I find and select the matching cell in Column B?

View 6 Replies View Related

How To Combine 2 Macros

Mar 14, 2009

I probably have a odd request, but then I think I'm just different ?

I have an Excel Workbook with Col. A approximately 1/8" wide. The formatting of this col is that the cells are set to have the typing fit into the cell, therefore it becomes so small that no one can read it. Just what I wanted. If the user needs to read what's in the cell, they can click on the cell and read it in the formula bar.

Col. B is approximately 2" wide

1st, the user enters the Path and File name, with extension, into cell A34(example).

2nd, the user types in a File Name that they will best understand into cell B34.

The Workbook is now saved.

When the user wants to open that file, they click on the Name . . . Col B34 - Easily recognized by the user.

They then click either the Button that say's "Open Excel File" -or- "Open Word File.

The macro automatically moves the selection over to column A, then the file is opened.

Now what I need

I want to use just 1 button and I will name it "OPEN"

The 2 below macros work excellant and I still need the functionality of each. What I want is that 1st it trys to see if it is an excel file and if so, opens the file and stops.

If the macro determines that it is not an Excel file, the macro then goes to the Word macro and determines if it really is a Word file and if so, opens the file and stops.

NOTE: I want to point out that I still need all the checking etc that the 2 macros currently do.

Here are the macros

OPEN EXCEL FILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

View 10 Replies View Related

Restricting Macros

Apr 6, 2009

I created a Macro to do some copying and pasting. Can I restrict that Macro so that it can only be used from my PC?

View 6 Replies View Related

Set Up And/or Edit In Macros

Jul 16, 2009

how to set up and/or edit my macros so that I can use them in various outside workbooks without having to open the specific workbook I put the actual macros in?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved