Using *.xls As Part Of A Loop: Skips The First Sub And Automatically Calls The PerthLocation Sub

Apr 15, 2009

I'm writing something similar to an auditing tool. I get a list of files from a pre determined directory. All these files are then listed on the spreadsheet. However, there is one file which has a different layout to the 88 other ones. I've written 2 subs (NonPerthLocation for the 88 stores, and PerthLocation for the 1 different sheet.) I'm trying to write a loop like the one below, however it skips the first sub and automatically calls the PerthLocation sub. In short, basically if the file has Loc 089 in any part of the filename, I want it to run the PerthLocation sub.

View 3 Replies


ADVERTISEMENT

Macro To Transpose And Merge Calls Automatically?

Jun 19, 2014

I wonder is there a way that I can transpose and merge calls by using macro?

View 6 Replies View Related

Automatically Replace A Part Of Formula

Jun 21, 2006

I've got a problem with my workbook.

It gets datas from another workbooks and contains cells with formulas like these:

='C:YEAR[2006-ab.xls]'!A12
='C:YEAR[2006-bc.xls]'!C18
et.c.

Is it possible to write a code in VBA to replace a part of formulas above?

View 4 Replies View Related

A2-A14=100%: Number Of Calls Column, With Number Of Calls

Jan 18, 2009

I have Problem column, with problems from A2 to A14. I have number of calls column, with number of calls from B2 to B14 and finally i have percentage column in which i want to automatically calculate problems percentage. I'm including my exel file.

View 5 Replies View Related

Sort Skips First Row

Jan 28, 2010

I am using the following code to sort a block of data by column B. The data starts in row 6.

View 2 Replies View Related

Copying Formula With Cell Skips

Jul 1, 2014

I'm annualizing monthly returns. I have the formula for a specific year and would like to drag the formula down to annualize the monthly returns for the following years of data (1982-2013).

The monthly returns start in cell C4, with January 1982 and go down monthly in the C column.

My formula for annual returns is
for 1982 is {=PRODUCT(1+C4:C15)-1}
for 1983 is {=PRODUCT(1+C16:C27)-1}
for 1984 is {=PRODUCT(1+C28:C39)-1}
and so on.

How do I copy or drag the formula to annualize the rest of the years while keeping the pattern above without manually typing each year?

View 1 Replies View Related

Rows Not Visible: Enumeration Skips

Oct 8, 2008

I have a spreadsheet that shows is a break in the enumeration of the rows. To elaborate, rows are enumerated from 1 to 15, then after row 15 is row enumerated as 25 and continues to be enumerated as expected. In other words:

1, 2, 3, 4, ..., 14, 15, 25, 26, 27, ...

How can I get back rows 16 to 24 be visable again?

View 2 Replies View Related

Drag Down Formula That Skips 48 Cells

Apr 4, 2014

I want to put in cell I3 the value from cell C1, to I4 the value of C49 and so on. How can I do it?

View 5 Replies View Related

Directory Listing: Skips .zip Files

Jun 10, 2006

I'm using a piece of code I found here on Ozgrid to create a list of files in a specific folder. And even though this worked fine for me a week ago, now when I run this same macro, the. zip files are left out of the list, even though I'm specifying msoFileTypeAllFiles. Archive files with other extensions, such as .tar.gz, do make the list. I get the feeling that my zip files are being considered folders instead of files. Do you have any idea why this is happening, or what to do to get that list of zip files?

Sub HyperlinkXLSFiles()
Dim lCount As Long
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "o:DataFeeds"
.FileType = msoFileTypeAllFiles.....................

View 2 Replies View Related

Sub Calls Sub Recup Value

May 22, 2007

I have two macros, Sub macro_Combs() calls Combin_6N()

In macro_Combs I must recover the values of the line in progress 500 step 28 to put them in vN(J) table of 10 élements.

The line in progress can go from 4 to 10 digits

if I make vN(J) = Sheets("feuil3").Cells(J).Value functions for 500 step 28 but with same the information of line 1 of feuil3

if I make vN(j)=cells(lig, col).value that does not function

Public lig As Integer
Public Col As Integer

>>>>1 ère macro

Sub macro_Combs()
For Lig = 1 To 100 Step 28
For Col = 1 To 1
Next..........

View 9 Replies View Related

One Macro Calls Many

Sep 30, 2009

I have this code (below) that calls a number of other subroutines. It begins the process just fine, but then gets hung up upon completing the first macro called, Run_Educator_Report. What could be causing this?


Sub Run_All()
Call Run_Educator_Report
Call Run_Non_Educator_Report
Call Construct_Database_for_Graphs
ActiveWorkbook.Save
End Sub

View 9 Replies View Related

Multiple Lookup: List Of Part Numbers Based On The Product Part Code

Feb 12, 2009

I'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.

=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})

so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.

View 3 Replies View Related

Trying To Chart Calls Per Minute / Per Day

Dec 6, 2013

I'm trying to use some call data for some predictive staffing. I need to see the highest number of calls happening around the same time each day. I'll have to do this for a number of different lines, so working out a formula that could apply.

I have two columns, date and time stamps of the calls. A small sample is below.

7/1/13 5:50 AM
7/1/13 9:49 AM
7/1/13 10:33 AM
7/1/13 10:53 AM
7/1/13 11:19 AM
7/1/13 11:41 AM
7/1/13 12:07 PM
7/1/13 3:58 PM
7/1/13 4:03 PM
7/1/13 5:35 PM
7/1/13 6:27 PM
7/1/13 6:39 PM
7/1/13 7:37 PM

View 2 Replies View Related

Find Non Blank Calls

Nov 25, 2008

I need to create a formula that counts the number of blank cells to the right and then actually uses the value in the non blank cell.

The result requiired is that the formula will find the difference between 1 value, the next non blank cell and then will average the cells between to get the interval figures.

eg. Value1 is 50.
Value2(the next nonblank) is 60.
There are 4 empty cells in between.

I need a formula that will find the next Nonblank (Value2), calculate the difference between Value1 and Value2, then divide that by the number of blank cells + 1 and add the value to the left.

The cell in A2, calculated manually at the moment would be:
=A1+((A6-A1)/(4+1))
This would then be altered across the page so that A3 would be:
=A2+((A6-A2)/(3+1)

etc., etc.

Therefore the result returned across the blank cells would be:

A1 A2 A3 A4 A5 A6
Value1 BLANK BLANK BLANK BLANK Value2
50 52 54 56 58 60

View 9 Replies View Related

Count Hours Where Calls Are Received?

Jan 27, 2014

I am trying to count hours where calls are received between 8am and 5pm Mon-Fri. If just one call is received between 8am and 5pm Mon-Fri then only one hour would be counted. Calls in excess of one call in the same hour do not count more than one. So if two people called from 8am - 9am then that would equal 1 and if on call came in from 9-10 then that would also be 1. Attached is a sample output.

View 11 Replies View Related

Automatic Phone Calls From A Spreadsheet

Aug 17, 2009

Is there anyway to make phone calls automaticly from a spread sheet

I'm calling old customers and it would be easy to just press a button and it will dial for me

View 13 Replies View Related

Share A Toolbar That Calls Macro From An Add-in

Mar 14, 2008

I have created some macros that need to be used in several workbooks by more than one users, so I put them in an add-in and gave them instructions on how to get the add-in. I also created a toolbar with buttons calling those macros, attached it to a workbook the users can open, and instructed them to open that workbook once to get the toolbar.

Both the add-in and the toolbar workbook are on a network drive that my users can access, but when they get the add-in, it gets copied to their own add-in folder.

Here's my problem:
When a user tries to click on one of the buttons in the toolbar, the button tries to call the macro using the path that would be valid on my computer to get to my add-in folder. This path includes my user name, so it doesn't go to their own add-in folder: it goes to my add-in folder on that computer if I ever logged in on that particular machine, or it goes nowhere.

When I created the button, I attached the macros by referring to the add-in workbook by name only, without referring to a path, so I don't know why MY path ended up defined on that toolbar.

How can I go around this? I manually relinked all the buttons for that user, and I can do it for the other users (there aren't that many users nor that many buttons), but I'd rather avoid it: I'll have to re-do it if the toolbar is updated and it just seems like more work than should be necessary.

I'm also opened to suggestions on a better way to share those macros. They can't be attached to the workbooks because we use new workbooks every year - they're emailed to us by the people who create them and for a variety of reasons, pasting everything into a workbook of our own is not a good option.

View 11 Replies View Related

Sequential Lookup Calls In One Cell

Aug 27, 2012

I'm updating someone's spreadsheet and so don't won't to add new cells or anything to it if possible. The problem I have is that I need to perform hlookup's of 3 cells, average them (some responses are non numeric) and then hlookup the output to give a final value.

The problem I have is that putting the lookups in one cell and then averaging them and looking up the average in another works, but all three steps in cell won't, it keeps choking on the non numeric responses.

Are their any work arounds to this, as the sheet has quite a bit of VBA code attached and I don't really want to have to add columns and then change the VBA code too.

View 2 Replies View Related

Concurrent Calls With Start And End Time?

Nov 23, 2012

I have a set of call data including start time and end time, with all calls on the same date. Now, how would I show in a sepearate column the concurrent calls being made. i.e how many calls are being used at the same time. This relates to how many channels I can suggest to someone with the maximum concurrent calls being made.

I have attached someConcurrent CaConcurrent Calls sample.xlsxsample.xlsx sample data.

View 3 Replies View Related

User Form Event Calls

Jun 1, 2008

if you can prevent an event macro from running at will.

The problem is occuring when I have a userfrom with various controls, and the change of one control triggers an event macro that changes other controls, this then in turn triggers further control change macros.

I have tried altering the macro name to things like optionbutton1_change, select, enter etc.

The only other way around it I can think of is to introduce a binary variable, but that will mean ensuring it os always the right value and then coding an IF statement on each event macro.

Therefore, I'm thinking there must be an easier way, some kind of switch ??? Something that says ignore any event macro when the control is changed as a result of different event macro.

- I'll include a small example below just to clarify what I am talking about.

private sub optionbutton1_click()

userform1.textbox1.value = "Test Change"

end sub

private sub textbox1_change()

msgbox "Change taken place"

end sub

What I am trying to achieve is getting excel to ignore the second macro when the control is changed by another macro rather than a user entry.

View 9 Replies View Related

When Dragging Formula Down The Formula Skips X Number Of Cells

Jul 22, 2014

Is it possible to write a formula that skips x number of cells and have the formula autoincrement as usual. For an examples lets say I want to skip 3 cells and I have entered in the cells listed:

A1= "1"
A2= "2"
A3= "3"

Then I type in the simple formula in the cell
B1= "=A1"

and then drag down but it skips 3 cells so then

B5= "=A2"

B9= "=A3"

If it didn't skip then
B1= "=A1"
B2= "=A2"
B3= "=A3"

this is just an example as I will be writing more complex formulas and need some thing that I can write into the formula so that it skips.

Something similar to this B1 "=A1(skip3cells)"

View 4 Replies View Related

Number Of Calls Made By A Person In A Month

Sep 5, 2013

The first column is "Person Name" and the second column is date in format "6/27/2013". I want a formula to calculate the total number of calls a person made in the month of June.

View 2 Replies View Related

How To Calculate A Number Of Calls Logged In A Shift

Jan 7, 2014

calculating the number of calls logged in a shift, based on a 3 shift system in a day. shift 1 from 08h00 to 16h00, shift 2 16h00 to 24h00, and shift 3 00h00 to 08h00. each shift receives a number of calls based on the time the call was submitted.

View 2 Replies View Related

Macro Calls A Unwanted Procedure During Runtime

Jan 14, 2010

I have a VBA application in Excel.
In a certain time, when the code is executed in the


Private Sub CB_ValidaHoras_Click()

View 11 Replies View Related

Creating Main Menu On Userform Using API Calls

Jun 17, 2011

I am trying to create a "mainmenu". On a Userform using API calls.

Created a Userform and named it Form1.

Added a command button to the form and named it Command1.

In THISWORKBOOK code:

Private Sub Workbook_Open()
Form1.Show
End Sub

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

I open the file and Form1 is shown with its command button. When I click the command button I get an error:

Run-time error 49:
Bad DLL calling convention.

What is the correct convention?

View 4 Replies View Related

Count Number Of Calls (C In The Rows) Of Each Provider?

Feb 13, 2012

Want to be able to count number of calls (C in the rows) of each provider by DOW (labels in the column).

View 4 Replies View Related

Count Occurrences Of Phone Calls Made?

Jul 3, 2012

Count number of occurrencesI have a large spreadsheet that list customer and call dates. Each call is displayed in a separate row with the corresponding customer next to it - so each customer may show up multiple times with different call times. I want an equation that will count if 4 calls (occurrences) have been made to each customer and if it is the answer is "1". I have been trying to make it working with COUNTIF but not getting the results.

View 4 Replies View Related

Get Median (and Average) Value Of Total Number Of Calls?

Apr 1, 2013

I have a team of 8 people who take calls. I need to get the median (and average) value of the total number of calls for these people for a specified date range, say 1/1/2013 to 1/24/2013.

When i use the following f(x):

{=MEDIAN(IF((Call_Data!$A$2:$A$13000>=$E$8)*(Call_Data!$A$2:$A$13000

View 1 Replies View Related

Managing Two Forms / One Form Calls Action From The Other

Jun 27, 2013

I`m making an Costumer Database for my business in excel, I already made a form in witch I can insert, change or exclude a client. In this form I have a button that hides the actual form and show another one to perform the search.

The search is working fine with the listbox, what I need right now is that i can search someone and then I click on the client i want in the listbox and somehow get the reference and open the other form with that reference.

The first form work with an reference number that is defined as a variant, so i have a macro that see this number and bring the client with that code. In the other form I needed a macro that gets the value of the selected reference in the listbox, then hide the form, call the other one and run a private sub.

View 4 Replies View Related

Retain OptionButton Choice Between UserForm Calls

Feb 6, 2008

I have two option buttons on a userform and when the user selects an option button I would like it to stay selected when the userform is called again.

View 2 Replies View Related







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