Increase Autofilter Field By 1 Across The Project

Jul 10, 2014

I'm trying to modify a worksheet and I'm having mental block. Basically I have the A:N columns full of data, and about 40 helper columns after that. All the helper columns being referenced by many macros.

I now have to add one more data column after the N column, so the helper columns will be cut and pasted one cell to the right.

The question is, how do I efficiently modify the code that looks at the helper columns to take into account the "1 field to the right" movement of the helper columns? Here is an example of the code

[Code] .....

There are tens of references across the project for every helper column so doing it manually is impossible.

View 2 Replies


ADVERTISEMENT

Converting Excel Formula To MS Project Custom Field?

Mar 20, 2014

A coworker is setting up an MS Project...project, and adding a custom field that would convert the Start date field into our Fiscal Quarter Fiscal Week schema, to display as FQFW, e.g. Q1W1. I've written the formula in excel, but I've never even touched Project until this week. Apparently it doesn't take formulas quite the same way as Excel does. I tried using the ''Switch" function in Project, and it worked, but it only accepts 14 arguments, and there are obviously 52 weeks we're dealing with. Would there be a way to do this using VBA? (I know next to nothing here as well.)

Formula where A1 is the Start Date:

Code:
=IF(AND(A1>=DATE(2014,2,1),A1=DATE(2014,5,3),A1=DATE(2014,8,2),A1=DATE(2014,11,1),A1

View 2 Replies View Related

Using Autofilter With Named Range - What To Put In Field

Apr 23, 2012

I am writing a code that will filter based on a named range. My named range is "Region," and it is currently in column B, but may be in Column C, D, E, etc., based on additional columns that the user of this sheet may add in the future.

I first had code like this (before using a named range):

HTML Code:
Sub CopyRegions()
Dim LastRow As Long

With ActiveSheet
LastRow = ActiveSheet.Cells(.Rows.Count, "A").End(xlUp).Row
End With
Application.ScreenUpdating = False
Range("B8").Select
ActiveSheet.Range("$8:$" & LastRow).AutoFilter Field:=2, Criteria1:="Europe"

But then I named column B to "Region," and so I want to do the same thing as above, but I don't know what to put where it has the number "2" - "AutoFilter Field:=2" because it may or may not be the second column in the future.

View 2 Replies View Related

Autofilter: Highlight Criteria Column/Field

Sep 15, 2006

When activating Autofilter in a wide table, the only way to determine which criteria field was selected is to try and identify which drop-down arrow has become blue. When working on a laptop, it is rather difficult to identify the difference between the black arrows and the blue ones. Is there a way to have a macro or event procedure that will cause the selected criteria field (or fields) change its background color (into yellow, for example)? This way, the yellow fields will "stand out"… No more searching...

View 3 Replies View Related

AutoFilter Macro With Changing Field To Filter By

Sep 3, 2007

I have a macro that automatically updates the field value if certain criterias are met. Now the user asked that TYPE be the first column in the spreadsheet. How do I change the last line in my macro to reflect that?

The range has now also changed from "A1:AT1000" to "A1:AG1000" ..

View 4 Replies View Related

Determine If Visible Dropdown Is True For Field In AutoFilter

May 9, 2014

I would like to determine if the VisibleDrowndown is TRUE in the attached table.

The current macro sets it to FALSE in field 1. However, I want to identify when it is TRUE so I can run this macro.

Attached File : V1.xlsm‎

View 1 Replies View Related

AutoFilter Multiple Field Automatically (and Show All If Criteria Is Blank)

Jun 11, 2014

I am looking to adapt a piece of code (originally created by Ger Plante) so that it autofilters multiple columns of a table. I have three data validated lists that need to search 3 different columns in the table and filter accordingly, but also show all if no hits are made (hence why Ger Plante's code) was perfect in most respects. I would ideally like to keep the code as a Worksheet_Change event, but can deal with it being run as a normal Macro via a button if this is necessary.

[URL]....

VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1")) Is Nothing Then
Range("A5:C5").AutoFilter Field:=1, Criteria1:=IIf(Trim(Range("B1").Text) = "", "<>", "=") & Range("B1").Text
End If
End Sub

View 3 Replies View Related

Generating List Of Project Parts Based On Project Duration And Time Window

Aug 4, 2014

I have a table of projects with 1) duration in year, 2) time window (number of years of our planning cycle), and 3) start year of the project. I want to generate a list of project parts of all projects where they may take place. This will serve as an input to an optimization program.

So a project of 2-year duration should have 2 parts over any year within the time window. I am including the "impossible" ones for my developer to tag them as "0" when we run it through his code.

View 5 Replies View Related

Consolidate 4 Excel Project Lists (Workbooks) To New Master Project List Using VBA

Sep 5, 2013

My task is to consolidate 4 Excel Project Lists (Workbooks) to a Master Workbook. The Project Lists has a different structure and almost different content. The relevant information is always on Sheet1 but it has completely different ranges. The only constant is the Project Number, which should be used to sort the information. Every Project should be listed only once with all the existing information.

I found a code written by Ron de Bruin which has already some components that I want to have in my VBA but I think there are still a lot of necessary adjustments to do.

Code:
Sub MergeSelectedWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim SelectedFiles() As Variant
Dim NRow As Long
Dim FileName As String
Dim NFile As Long
Dim WorkBk As Workbook

[code]....

The Master Project List should has the headers in Row1 and the information listed below. The Macro should automatically places the correct information to the correct column. Some of the information are in 2 or more of the lists but they should be listed only once in the Master List.

Project Number

Project Description
...
1111E.000000001

[code]....

I guess a problem is that the structures of the Lists are quite different so there must be a kind of sorting process.

In the end I want to have an Excel File with the Macro and a Command Button and by clicking the Macro creates a new Workbook with the Master List.

It would be better if there is a variable range instead of a defined. Like the Macro searches the last row and starts at this row and column.

View 4 Replies View Related

Get Excel To Open MS Project File And Then Run Project Macro On It

Jul 30, 2013

I am trying to get excel to open a MS Project file and then run a project macro on it.

What is happen is that when I run my code it works, and then excels VBA window freezes up and the run arrow goes away, and if I try to open project I just get a chiming noise and it won't open.

For now I have to select the MS Project file from a directory, but in the future I would like it to go through the folder and open all of the file in the folder

EXCEL CODE
Sub ImportMSProject()
Dim FileToOpen
Dim mpApp As MSProject.Application
Dim prjmacro As Object
'Identify the File to Open - START
FileToOpen = Application.GetOpenFilename("Microsoft Project Files (*.mpp), *.mpp")
If FileToOpen = False Then
Exit Sub
End If
'Identify the File to Open - END

[code].....

View 9 Replies View Related

Protect VBA Project Or Lock Project For Viewing By VBA

Apr 27, 2007

I have tried to record macro to protect VBAProject / lock project for viewing. But can not success by that way.

View 9 Replies View Related

IF Function Formula Logic: For Every Increase Of Of Mhz, Give 25.00 Per 100 Increase

Nov 2, 2006

I have a test due in the morning, and I really need this question answered ASAP, if anyone could. I need to create an IF formula for this situation: the standard Mhz is 500. Give 55.00 for that standard. But for every increase of of Mhz, give 25.00 per 100 increase.

View 2 Replies View Related

Tally Report Of X: Count On Column B (project $) Depending On The Criteria On Column A (project Description)

Aug 28, 2007

I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].

View 4 Replies View Related

Auto Populate Field With Date When Other Field Is Selected From Validation List

Jul 4, 2013

In the following extract, I would like to have today's date placed into Col K when an entry is made into Col J.

Entry into Col J is from a validation list of 3 options. What I desire is that when one of these options is selected ("Complete"), then I would like today's date to be written into Col K.

Currently Col K has a validation list which comprises dates from 1st of each month from July to June.

I need a fixed date as text preferably, so was thinking the VBA function "Date" on a Change Workbook Sub.

The information is currently part of a Table in Excel 2007 that has about 500 records.

IdeasList

*
J
K

2
Status
Month Completed

3
In Progress
*

4
Complete
Jul-13

5
*
*

6
*
*

Data Validation in Spreadsheet

Cell
Allow
Datas
Input 1
Input 2

J4
List
*
=ValidationList_Status
*

K4
List
*
=ValidationList_Months
*

View 2 Replies View Related

AutoFilter Method Of Range Class Failed - Yet Autofilter Works.

Sep 25, 2009

Im sure this is a very common problem. I tried searching for it but I havent found anything that solves this for me. Here is the code Im using:

View 3 Replies View Related

Macro To Preset Field With Text If Condition In Another Field Is True

Jun 9, 2006

I have a macro that imports a report. If the Charge Type in column A is BTOREPLX, I need to prefill the field next to it in column B with the text "REPLX", e.g. "REPLXCDROM". I've attached an example of the report.

View 5 Replies View Related

Change/Move Pivot Table Row Field To Column Field

Apr 23, 2008

In building my pivot table my data that I want to show in the column area is showing up as rows stacked on top of each other. In the column section I'm trying to show Total Budgeted Amount next to Total Actual Amount but on the layout it's showing the two stacked on top of each other is there some kind of hidden key that I'm missing?

View 3 Replies View Related

Getting Zero Formula Field When Data Field Blank / Empty

Feb 13, 2013

I have a simple formula ='info page'!b2&'info page'!b5 in places that on my sheet adds a company prefix to a item number, prefix in b2 and item in b5, we have a client that we cant use prefixes but now when I leave the field blank its giving a zero and thats not good either. I'm pretty sure an IFERROR or something will work but can get the syntax right.

View 5 Replies View Related

Auto Populate A Field In One Tab By Comparing Another Field In A Different Tab?

Jan 25, 2013

I have a tab called 'Tab1' in which I have column called 'Col1' and 'Col2'.

I have another tab called 'Tab 2' with columns 'ColA' and 'ColB'. When I enter a text in Col1(Tab1) I would like that text to be compared to data in 'ColA' from Tab 2 and when match found then get the corresponding value from ColB and autopopulate it in Col2 of Tab1.

View 2 Replies View Related

Changing A Numerical Field To A Date Field

Dec 28, 2006

in coverting these fields into a date field.

Example 91306 to 060913

I have encloed the file.

View 14 Replies View Related

Counting Per Date Field And Data Field

Apr 29, 2006

I am trying to count the number of times that data is in a field and in any field the date is greaert than today's date. This date field could be blank and that is where I am having the problem.

Example

See Attached

View 4 Replies View Related

Copy Top Cell Post Autofilter And Reapply Autofilter Based On Cell Value

Aug 20, 2014

I can select the top cell in column "F" after filtering by multiple columns using VBA and arrays, but now want to I want to use the top cell in column "F" to search for all other equipment that uses this item.

E.g. remove filter, and reapply autofilter to column "F" based on selected cell as per below VBA

Note: Row 1 contains command buttons and row 2 Headers.

View 4 Replies View Related

Concatenate Two Text Fields BUT Left Adjust First Field And Right Adjust Second Field

Jun 22, 2012

I want to concatenate two Cells into a single cell BUT have the first field left justified and the second cell right adjusted.

A1 = "John Williams", A2= "Single"

A3 = "John Williams Single"

View 1 Replies View Related

Set Autofilter To All Without Removing The Autofilter

Jan 16, 2007

I can set the autofilter, change it, remove all using Macros. What I need to know how to do is reset a filtered column to ALL while leaving all other filters on.

View 2 Replies View Related

Vba For Project

Jun 30, 2007

I am looking for a forum or where I can find advice on using VBA with MS Project.

View 2 Replies View Related

VB Project Protection

Feb 12, 2009

I'm trying to protect my project so that others can't unhide a sheet I have tagged as veryhidden within VB.

I followed these steps:
1. Select project in the projects window
2. choose Tools
3. Project Properties
4. Protection tab
5. checked lock porject for viewing
6. entered passwords twice
7. clicked ok and saved

On another file this has worked perfectly as I wanted it to.

However on another very large file with multiple VB projects it is not "taking" on the project I need it to.

I can open the file, the VB project and change the setting on any sheet I need to without entering a password.

View 14 Replies View Related

Add Project To 'database'

Mar 9, 2009

Add Project to 'database' I recorded my marco.

View 3 Replies View Related

Project Not Closing In VBE

May 7, 2008

I have a macro in a workbook (WB1) that opens another workbook (WB2). After performing several other macros through user invocation with buttons, I close WB2 with the following statement:

Windows("WB2.xls").Activate
ActiveWorkbook.Close (False)

While the WB2 workbook closes okay, the WB2 project is still open in the VB editor. Does anybody know why this code isn't closing the project in the VBE?

The WB1 process can occur multiple times while Excel is open and each time a separate occurrence of the WB2 project is left open in VBE.

View 9 Replies View Related

Start And Name A New Project Using VBA

Nov 28, 2007

how to start and name a new project in Excel VBA. Will someone explain how to?

View 3 Replies View Related

Project Supplies Tracker

Apr 11, 2014

Shee1 , sheet2 raw data from system extract.

I need the supplies of each code to fall into boxes sup1 sup2 and so.

While I also need when they were supplied created below the qty supplies table.

View 14 Replies View Related







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