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
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?
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!
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.
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.
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
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?
-"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.
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.
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.
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.
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.
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 .
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'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
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.
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.
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.
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.
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.
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
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.
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?