Passing Columns As Paramater To Work On List Object
Apr 21, 2009
I'm trying to write a remove duplicates sub that can be passed the worksheet name and columns on which to check. It's pasted below. Above the key line is a commented out line of code that worked. So it works to pass theh worksheet, but I'm hung up on how to pass varying columns to it.
Sub RemoveDuplicatesSub(wksht, cols)
'Remove duplicates.
'Assumes that the data range is in a table
'Assumes the header row starts at row 7
Dim WS As Worksheet
Dim TableName As String
Set WS = Worksheets(wksht)
TableName = WS.ListObjects(1).Name
'WS.Range(TableName).RemoveDuplicates columns:=Array(3, 4, 5, 6, 7, 8, 9, 10), Header:=xlYes
WS.Range(TableName).RemoveDuplicates columns:=Array(cols), Header:=xlYes
End Sub
View 9 Replies
ADVERTISEMENT
Sep 28, 2007
I will post my code and point out where the error occurs.
Sub GetProd()
'Averages Daily Production over the month
Dim WellRange, MonthRange As Range
Dim Month_ As Integer
TotWells = Sheets("R").Cells(1, 2)
TotMonths = Sheets("R").Cells(1, 4)
Month_ = Sheets("R").Cells(2, 4)
For Flag = 1 To TotWells
Set WellRange = DefineWellRange(Flag)
WellRange.Select <-----Used this To check If Range gets passed back properly.
For c = 1 To TotMonths
Set MonthRange = DefineMonthRange(Month_, (WellRange)) <---Error Here
Next c
Next Flag
End Sub ...
So the error occurs in the main "GetProd" Sub at the point when it calls the DefineMonthRange Function, its a 424 "Object Required" Error. Just before this is called I have a "WellRange.Select" which I was using to make sure that WellRange is in fact a range, and it does select the appropriate area.
So My question is of course, why I get an object error even though I am passing a range to a function which is expecting a range?
View 4 Replies
View Related
Jan 20, 2009
we query SAGE, and have account codes begining xx-xxx. We use the project ledger which attaches a letter on the end depending on the works, i.e. 00-001A, 00-001B, etc
I need to set a paramater to say any that any that begin/contain (i.e. LIKE %) 00-001 filter out, regardless of the letter.
I know the basics of [paramater] or ?, but the ? doubles as a wildcard in the like criteria. Whenever I try to put a paramter value in it shows nothing. I can get it to work for exact, >< etc but not contains or begins. Even if I play with the SQL, nothing!
View 9 Replies
View Related
Oct 12, 2008
I'm trying to open a file and perform various tasks. This is the structure:
Dim xlApp As Object
Dim xlWb As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWb = xlApp.Workbooks.Open(BackupFile) ' backupfile evaluates to c: esting est.xls
xlWb.Worksheets(1).Activate
Debug.Print xlWb.Worksheets.Count
Dim ws As Worksheet
For Each ws In xlWb.Worksheets
Debug.Print ws.Name
ws.Activate
Next ws
I think I'm totally missing something. The above works, except for the loop. None of the names show up in my immediate window, and none of the worksheets are activated. xlWB evaluates to nothing, which is where the problem has to be...but why would my first .Activate and Debug.Print lines work? Also, If I declare xlWb as Workbook then absolutely nothing works after the file is opened.
Basically what I want to do is open the file, do a bunch of a stuff with it, then save and close it. I have this all working without creating another object, but the opening of the file is still visible to the user. Seems like turning off ScreenUpdating doesn't truly work.
View 9 Replies
View Related
May 19, 2014
I have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.
View 5 Replies
View Related
Apr 13, 2007
I have some spreadsheets that rely on data being pulled in from external querys (from an SQL database).
the below line of code is part of a Workbook_Open() sub, which refreshes the data.
Selection.QueryTable.Refresh BackgroundQuery:=False
I have just got my hands on acopy of Excel 2007 (Yes!!!), but when i open my sheets my code now fails
Ater some messing around Ihave found that if I add ListObject" as below, it now works.
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
trouble is it doesn't work for the rest of the office, who didn't get Excel 2007
is it possible for me to have this so that it works for both flavours of Excel?
View 9 Replies
View Related
Dec 5, 2012
In my example you will see my name list that changes with a array to data validation. Seems like you can find ways around things most of the time but this time
View 4 Replies
View Related
Aug 30, 2012
I need the sum of a column in a table. In the sheet I am using "=SUBTOTAL(109;[Total])", but I need the absolute total in VBA. How is that possible?
View 1 Replies
View Related
Dec 7, 2007
I am trying to rename a text box, but it doesn't let me. The naming convention is fine, as it works with others. But it thinks that name is taken or something. So how can I see the names of all objects on a sheet?
View 9 Replies
View Related
May 13, 2013
I have a workbook with three sheets:
-"DB" database sheet containing multiple tables (20 to be exact, named as "CityA", "CityB", etc.)
-"Threat Data" reference sheet containing a "City_Ref" table with the list of tables names in the "DB City ID" column and the unique city name assigned to each table in the "City" column (the city name is populated in a "City" column of each table in the database sheet).
-a dashboard sheet containing an interactive userform for which to populate the database tables.
In this userform, there are two comboboxes:
-a "CbxCity" combobox which lists all the city names from a "City" column in the "City_Ref" reference table
- a "CbxAsset" combobox which should automatically list all the values in the "Asset" column of the selected city table based on "CbxCity"
My issue lies in filling "CbxAsset" based on the selection in "CbxCity" ; how do I dynamically select a ListObject name based on a selection?
The code I am working with is:
[Code] .....
In the CbxCity_Change() sub, I am not sure how to name the ListObject and my code currently gets an error at r = Me.CbxCity.Value
Which is strange because that is showing the selected city name when I run the cursor over the bug.
View 14 Replies
View Related
Nov 3, 2011
I have a huge ss and my sumproduct function works only up tp certain columns and starts returning #value! error. here is the formula:
=SUMPRODUCT(--(Detail!$A$2:$A$2971>=$R$4),--(Detail!$A$2:$A$2971
View 4 Replies
View Related
May 19, 2014
I know how to get the row number of the cursor in a spreadhseet (Activecell.Row), but how do I get the cursor location in a list object so that I can then insert a new row for the user at that point?
BTW, I am using tables (ListObjects) because when a row is inserted, all of my formulas are automatically inserted.
View 1 Replies
View Related
Jan 20, 2008
I have been working on large project using Excel VBA for several days. My code seems to be working correctly, but I have more to do and now, when I drag an object from the toolbox onto a UserForm, the object is not added to the list of objects on the form. If I go back to versions of the project that I was working on several days ago, there is no problem. If I run "Workbook Rebuilder", the objects that I have dragged onto the form are then added to the object list, but I still can't add new objects to forms from within the VBA editor. Is the project corrupted, or is there some other explanation, and are there any fixes? The code runs about 50 pages, and there are over 20 forms, so redoing from scratch is only a last resort option.
View 3 Replies
View Related
Aug 26, 2013
I have written code in which cell color changes according to cell value. To change cell value I have used drop down list (Data/Validation/List). Main question/problem is code works well when cell value is changed by typing from keyboard only.
Code doesn't work when cell value is changed using drop down list.
Attached is the test file : Test_26082013.xls
Also refer link [URL] .....
View 1 Replies
View Related
Apr 15, 2013
Anyways, the issue is that when I have some cells selected then use the text-to-columns, it works fine.. but when I select an entire column, it "acts a fool"...
I made this video to show the effect on my computer.
View 14 Replies
View Related
Oct 22, 2008
I have three columns where my workers record the amounts of work they do during the day. The columns are labelled as follows:
Column A – “Correspondences’’
Column B – “Linking’’
Column C – “Allocating”
When a task has been completed an “X’’ is put in the relevant column (which is then summed at the bottom). I use these sums to calculate each workers productivity in the following way – number of “x’’ divided by number of days worked. This is a simple formula, however, each “x” now equates to time – which is causing me problems. An “x’’ is Column A equals 1 hour, Column B 1 hour and Column C 2 hours.
I am looking for someone to help me create a formula which will calculate all possibilities in the above situation above. Please bear in mind that a worker can do one of these tasks during the course of a week, two or all three .
View 9 Replies
View Related
Feb 25, 2014
I need a macro to sort on five columns (Column A, L, P, X, and Y) out of about 33 columns. Sort is all lowest to highest.
The code I have so far is:
Sub Macro1()
Range("A2:AG").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("L2") _
, Order2:=xlAscending, Key3:=Range("P2") _
, Order3:=xlAscending, Key4:=Range("X2") _
, Order4:=xlAscending, Key5:=Range("Y2") _
, Order5:=xlAscending
End Sub
However, I get an error "runtime error 1004 - method range of object global failed".
View 1 Replies
View Related
Jan 29, 2014
I have 3 calculations I would like to make based on data in the spread sheet and I can't seem to get them to work with data from the two separate columns.
I tried a few of the index match max formulas I found here and could only get them to work with one column of data.
I have the spread sheet attached and the 3 calks I want to do are blank on the bottom.
I am using Excel 2011 for Mac
View 7 Replies
View Related
Jun 11, 2013
I'm creating a spreadsheet to keep track of my costs of production in an online game. Within the game there are a range of spawned resources that appear for only a short time before being unobtainable these resources have specific types that is shared between multiple spawns of the resource but each resource spawn has a unique name.
My first worksheet lists all the resources and their various qualities and the later worksheets are meant to allow me to choose from a list resources matching the requirements of the item I'm looking to craft. The example i have shown in the second picture requires Tatooinian Fiberplast and Lokian Wild Wheat to craft so in the Chosen Resource column I would like to have a drop down list allowing me to select the named resource type i would like to use - for Tatooinian Fiberplast the only thing on the list should be Omnitwixi and for the Wild Wheat it should show Fizi and Krad
[URL]....
[URL]....
I am aware there are people with more pressing problems than computer games and as such
View 7 Replies
View Related
Apr 17, 2013
I have a workbook that has worksheeets for every day of the month. The data in the worksheet consists of columns (employee #, name, clock in/out times, and break penalty).
What I am trying to do is create another worksheet that searches all the other worksheets for a "yes" in the "break penalty" column and then create a list of all the employees that received a break penalty for the entire month. I would like this to be able to auto populate throughout the month as data is entered and not have to use a filter every time I want to compile this list.
View 3 Replies
View Related
Oct 15, 2013
I have a need to combine multiple columns into a long list of results in 2 final columns. Here is my example...
Current layout with varying number of data points per row...
ColumnA | ColumnB | ColumnC | ColumnD | etc...
example1 | Test1 | Test2 | Test3 | Test4
example2 | Option1 | Option2
example3 | Number1 | Number2 | Number3 | Number4 | Number5
Desired Result
ColumnA | ColumnB
example1 | Test1
example1 | Test2
example1 | Test3
example1 | Test4
example2 | Option1
example2 | Option2
example3 | Number1
example3 | Number2
and so on and on and on until all rows have been filled in down the file...
Is this possible?
View 4 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
Dec 17, 2012
how to list values from multiple columns in a dropdown list based on lookup value of 1st column as below.
This is how the table looks like.
Product MOLD1 MOLD2 MOLD3
4" AB1 AB2 AB3
6" ZA2 zd4 -
This is how the dropdown list should look like for Product 4"
ab1
ab2
ab3
View 6 Replies
View Related
Mar 3, 2014
It's quite easy, I am trying to do a droplist (that's easy) but I want that drop list to take infos on 2 different columns and fill the 2 columns I want in the main sheet (see attachment)
On tab 'dealers' I have dealers name and account numbers. On tab 'Mileage' I have a cell call "NAME' and a cell call 'Accnt #/NB'. Here is what I want to do:
On sheet 'Mileage' I want to choose from a drop list a dealer name and I want Excel to populate the cell "Accnt#/NB" automatically by chosing the account number that is appropriate to the dealer choosen which is column 'B' in the dealers tab.
View 2 Replies
View Related
Jan 11, 2009
i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
View 4 Replies
View Related
Mar 3, 2009
In a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....
I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.
From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.
View 9 Replies
View Related
Sep 9, 2013
I saw a post with a formula of =LOOKUP(9.99999999999999E+307,SEARCH(" "&KeyWords&" "," "&$A1&" "),KeyWords) which I thought might work, but it returns an error.
I'm trying to search on A1 and return the correct name from a named range called KeyWords.
A
B
C
D
1
Chicago
#N/A
Chicago IAP
[Code] ......
View 4 Replies
View Related
Aug 24, 2009
i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook
the current name is
AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls
i have attached the code in notepad ...
View 8 Replies
View Related
Feb 13, 2010
In column J, Cad Hrs. How to make to display (0), instead of (-0,02) when I just do other work than Cad Work? I need to separate type of work according to price hour, overtime, and so on.
View 2 Replies
View Related
Jul 7, 2006
How do I pass values between procedures? I have two command buttons in the worksheet.
If I have a procedure A that asks user to input a value "x", when a command button is pressed.
Then another command button is pressed that passes the value onto another procedure B, which iterates a msgbox as many times as the number x,
How do I pass that value x from one procedure to another?
View 7 Replies
View Related