I have a worksheet with a header row.the data is from B2:B25.I have to accomplish two things.
i)I want to get only 8 records (viz record nos-5,6,11,12,17,18,23,24)from the entire worksheet.serial numbers are shown only for illustration purpose.i want to eliminate all other records from the worksheet.then i will get the records from seriel numbers 1-8 as shown in E2:E9.
(2)I want the content of row3 to come up in C2 and then row3 should be deleted. now the content of row4 should come up to C3 and then row4 should be deleted.it should continue.the result will be 4 records as shown in G2:H5
I am copying a WorkSheet and I want to set it directly to an Object. I don't want to use the activeWorksheet. I does work like this with .add so my idea was it should work with copy too.
[Code] .....
It does Copy the Worksheet, but afterwards VBA gets error 424
We have a form that has a dropdown list of selections but many people forget choose the reasons. How can I make these required fields that HAVE to be filled in in order to print. These are internal forms, nothing on the web. I just need to find a way to make sure that there is a way to stop someone from printing if these 2 fields are not chosen.
I have this Excel File which has some sheets. All sheets are the same format like this: "I have read-circled the cells of interest"
I want to have a macro that can read the data from all the sheets (regardless of their name) and from the specific cells "red-circled" and copy and paste them into new sheet named "Report" in table format. Like this:
I need to do sorting and filtering the similar records to new worksheet. I need you help to do this job, give some idea or command to use for the following steps.
I have attached the worksheet newtran.xls.
1. I need to find the last column, the column will be increased or decreases. so that we need to find the last column at time of running the macro. Now the value is there till column DD
2. I need to concatenate the column B, D, F to last column find out at 1 step. Insert a new column and place the concatenated value.
3. Entire row will be Sorted based on concatenated value column.
4. Find the similar row on concatenated column and entire row need to be moved to new worksheet.
I've found a great userform on this site that allows you to populate a datasheet as well as delete or amend datasheets. It was by dodger7 within Database. Very useful. I've adapted this to my needs and it works great apart from i cant amend the userform that shows data when you select delete or amend. I've had a go but don't understand how i can create my own feilds and set it up in order. When i go in to the code i can veiw the delete and create/amend userforms but this is a search function relating to a reference number. Once you select Find it brings up another form and that is the one which i need to adapt to show my new feilds. I have attached the sheet so you can see my problem. I was wondering if anyone can advise as i love this userform/database method but can't complete the changes to my needs.
I have a product Database with 11 columns. I am trying to build a Search/look-up userform.
I found a brialliant example of the same posted by Roy, UK. I have copied the same and its working well, but with a few little problems.
Here's what I've got:
I have a userform with a Combobox (called Combobox2) where it shows all the products from column B in the worksheet "ProductData". In the worksheet "ProductData" , I have 11 Columns with headings from B1:L1.
When user selects a product from the dropdown list (from Combobox2), all the details related to that product from other columns are displayed on the userform via labels.
This part is working fine....except that the values in Combobox2 are taken from activesheet and I want them to come from worksheet "ProductData". I have hidden the sheet "ProductData".
Second Part is, I have about 20 products in the Database and all of them have a Product Specification Sheet in PDF format. I am just wondering, if there is any way I can have a button on the userform that can used to open this PDF datasheet for the product thats being lookeed up by user.
What I mean is, if user selects productA from the combobox2, then it displays all the info about the the ProductA on the labels on the usewrform. and if user clicks on the "View Data Sheet" button then it opens the Datasheet PDF file for ProductA.
Below is my code for the first part:
Option Explicit
Private Sub ComboBox2_Change() Dim ws As Worksheet Set ws = Worksheets("ProductData") Dim Ncell As Range With Range("B2", Range("b65536").End(xlUp)) Set Ncell = .Find(ComboBox2.Value, LookIn:=xlValues)
It is so nice to check-in once in a while to see all these new ideas and solutions of people's problems. So, I would like to ask a question that really bothered me for some time, and it looks like I cannot find a full answer to it. So here it goes:
How can I import data which is either more than 65000 records long or it will sum up to be more than 65000 when imported? I need all my data to be on one worksheet, and I don't care if these data will be imported let's say in columns A B C, then once it reaches the 65K, it will be imported to the neighbouring columns D E F, etc.?
I searched the Ozgrid for an answer and I found a thread
Code: Sub Button1_Click() Dim cnt As ADODB.Connection Dim rst As ADODB.Recordset Dim stSQL As String Dim SNfound As String 'Your sqlserver 2008 connection string Const stADO As String = "Provider=SQLOLEDB.1;" & _ "" & _
[code].....
but i dont see where to put in the Database object...
The Database it needs to connect to in SQl is called
The Server name is SQLSRV when you expand databases the database is called SWHSystem the Table is called dbo.Credential and from that i need to get SELECT All from the Name and CardNumber from dbo.Credential and put that in a New Sheet titled Personal
I have a set of related variables that are split over multiple worksheets, and I need to be able to take specific information, duplicate certain values and produce an output sheet for use in a separate piece of software.
The variables are: Position Number (Sheet 1) Position Title (Sheets 1 and 2) Position Requirement (Sheet 2) Requirement Importance (Sheet 2)
The output sheet requires a list of all the requirements for each position number, which means the position number itself needs duplicating (in new rows) X number of times, where X is the number of requirements assigned. The appropriate requirements are then to be pasted in next to each position number (and the requirements can be found by comparing position number to title, and from title to requirements).
Normally, I’d be able to do this using lookups and so forth, but my problem arises when I have multiple position numbers with the same related title (in the attached example, there are three plumbers with unique position numbers). I can’t figure out how to say to Excel “a plumber has five requirements, and there are three plumbers, so duplicate each position number for each plumber five times, then insert the appropriate qualifications (and their associated importance values) next to the position numbers”.
I have a range of columns i.e. 23 columns (i.e. B through X). Someone can write records in these columns (starting from B21).
Duplicates are considered the rows with similar data in columns 3 and 11. I know about the removeduplicate method and works really well but i want the duplicates not to be removed. Instead another column shall be checked for date of entry (user will entry date in format dd/mm/yyyy). The newest entry will change the value of the cell in column 4 (islatest column)to TRUE while all other records will be FALSE. This will work with the filtering of data on a pivot table on another worksheet.
Book1 and Book2 are workbooks that I have modified in order to protect private information.
Book1 will have 11,000 records (my example Book1 has only 100). I need to rearrange Book1 such that it looks like Book2. Book2 has 20 complete records from Book1 combined into one single row, and my example Book2 has populated 3 rows only (3 rows x 20 records, making 60 records now appear on 3 rows only).
Macro for getting Book1 to Book2? 11,000 records in Book1 will take a lot of hours to transform into Book2 unless a macro can do the job for me.
example of a database user form that will allow me to list records in a sheet as well as search for records in a sheet. I know excel has a built in feature for this but it is menu driven and I need something that is button driven and will allow me to resize the form layout. I was not able to figure out how to do that with the built in form.
I have been given a worksheet which has 5000 rows spread along 13 columns. There are about 200 cells that I am interested in from this sheet, these cells being the subtotals of the cells above them.
I have extracted the individual cells containing the subtotal values to a new, empty column but they are spread down this column with blank cells in between.
Is there a function / formula to get rid of the 4800 blank cells so that the data I need is compressed into a stack of 200 cells all filled with data?
with the line "Sheets("Hidden Data").cells(rngfound.row, 9) = sheets("data").range("l18")" the error messages states Object Required, but i am not sure what this is trying to say.
any idea's or thoughts would be greatly appreciated.
at the top of the coding i have listed "Public rngFound"
I am creating a training matrix that will be used in a number of locations. Some only have a few staff members whilst others have lots. I want each location to print a list on a formatted sheet but I want to set it up so that the locations with fewer people don't end up with lots of blank lines.
Why should I set objects to nothing (ie clear them) just before ending a sub routine? Doesn't excel dump the variables after the sub is finished running?
I have two columns E, F in excel. Rows are dynamic. In the sense that sometimes only 2 rows appear or sometimes newer rows appear......
Now I want to lookup the last row and get the required numeric value. I have written the following formula to get the result. =VLOOKUP(CONCATENATE("E",SUM(COUNTA(E2:E16),1)),E2:F16,2). It returns value 60. But when I add 2 new rows.
7 F 70 8 J 90
It should return 90. But it is still showing up 60. when i checked CONCATENATE("E",SUM(COUNTA(E2:E16),1)). it is returning 'E8' correctly. But VLOOKUP unable to return correct value.
I recorded a print macro which prints to my default printer, others will be using this spreadsheet so I really need it to print to the same network printer. The printer address and name is: ironyan-fileBI44
I need a loop function (i guess a do while or do until code) so whenever the word 'Non-Current' appears in colum A enter a 1 in colum E until the word 'Total Non-C' is reached at which point the loop must end. Such as: A B C D E NON-CURRENT 6 4 5 1* ABSA 4 5 2 1* BARCLAYS 3 2 8 1* NED 0 8 6 1* TOTAL NON-C 4 6 7 0
The buttons are located on the PFM Tracker sheet with what I need them to do. In addition I'll give you a breakdown of what is required.
Currently when the workbook is opened the user is forced to activate the macros. One of these macros disables the Cut, Copy Paste features within Excel. I need the first button 'Enable Copy' to request a password for activation and once entered the Cut, Copy, Paste feature to be enabled. Then when finished the 2nd button 'Disable Copy' when clicked to re-activate the Cut, Copy, Paste Disabled macro.
Hope that explains it. I have left the PFM Tracker sheet unprotected for who ever wants to play with it. (Not that protection would stop some)
sum all my sheet data....I have around 200+ sheets with same format and I'm doing one summary sheet where I can see the sum of all sheets (column L) but not able to get the formula .....
Example: Currently using below formula
=Sum('Sheet 1'!L:L)
How to I change the Sheet 1, Sheet 2 ..etc If I drag the formula I'm not getting the results....
get the right formula to copy all the sheets column L:L sum details...
I have a cell in which I want either a Y or a N entering. I do not want this cell to be left blank.
I can add a validation so that nothing but Y or N can be entered but it doesn't stop it being left blank. I entered an N (as a default) forcing the user to change it to a Y if needed but I don't want them to be able to delete and leave the cell blank.
Sub Macro2() ' ' Macro2 Macro ' Macro recorded 7/28/2007 by i8ig '
If Target.Column = 1 Then If Target.Value = "Med" Then Rows(Target.Row).Interior.ColorIndex = 4 Range("H3").Select ActiveCell.FormulaR1C1 = "=IF(RC[3]="""","""",RC[3]-3)" Else If Target.Value = "Tasc" Then Rows("4:4").Interior.ColorIndex = 44 Range("H4").Select ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)" Else If Target.Range = "NBAR" Then Range("J5").Select ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-5)" Range("I5").Select ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)" Range("H5").Select ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)" End If End If End If End If End Sub I continue to receive an error '424' object required and I cant find it
i have created a workbook that looks for files and creates hyperlink within a worksheet to these files, to help the user I have included the title from the document properties.
To get the document properties i used the DSOFile DLL from Microsoft and added it in to my project and it works fine.
My problem is that i wish to distribute this to a large group of users within the workplace and I need a method of checking if the DLL is added in and registered, if not can it be added in and registered on the fly.
I have tried placing the DLL on a common network drive, but this could be further complicated b some users taking laptops out of office but hopefully the microsoft synch of files might take care of this.
I am trying to summarise training data in a spreadsheet as per the following layout:
A B C D E F 1 Trainer name # of trainees # of assessments Total hours Avg hrs per trainee Pass rate 2 Peter
3 Paul
4 Mary
The intention is to have this summary fixed on one tab, with the raw data being dumped into a second tab alongside the summary (named “the data”).
The data for the summary comes directly out of an Access database and is in the following format (I’ve put an X against the headings that aren’t relevant to the summary):
A B C D E F G H I J K L M N 1 X X PersID Name X X X X X Trainer Assessment X Result Hours 2
12345 Jimbo
Peter Excel Competent 2 3
54321 Nelson
Paul Access Not yet Competent 3 Notes:
-The data can be up to 2000 rows long
-There will be multiple entries for each trainer of course, and multiple entries for some trainees (i.e. they will complete several training types)
-Result can only be Competent or Not Yet Competent
So, of the 5 fields I need to populate for each trainer, I’ve currently only got the following nailed:
# of assessments: =COUNTIF('The data'!$A$2:$A$114,A2) Avh hrs per trainee = D2/B2
These were the easy ones.
What I’m having trouble with is the following:
For the #of trainees field, the calculation must determine how many people were trained by each particular trainer – so only unique entries should be counted (PersID could be the unique identifier). Total hours will also need to be linked to the individual trainer – in other words, how many hours in total has the trainer spent training?
Pass rate is another tricky one – for each trainer, I’d like to see the percentage of trainees deemed competent out of the total number of trainees they trained.
I have a table in a workbook with names in the left column and top row. The intersecting data fill the rows under and to the right of these named areas. I need to keep this table in its own workbook, because many sheets in other workbooks will access its info. I have linked this workbook information to a sheet in each of my accessing workbook. I call this sheet "DataLink" and it is a sheet that uses an If(Original Data Ref Cell="","",Original Data Ref Cell). This works well at bring the data into my active workbook.
My active sheet has 2 drop down lists. One for the top row of names on the DataLink Sheet, and one for the left column. I have used the Offset Function refering to cells with the countif and count functions in my Data Validation setup to make the addition of the names in the DataLink Sheet Dynamic. This also is working well.
In my Taget cell (CeIl where I will get my answers) I use the Indirect(row) indirect(column) to retrieve my intersecting data relate the the row and column. This works well as long as I establish my defined names of each column and row with Ctrl-Shift-F3 and choose (Top and Left)
Now my problem. Although I can dynamically add the names to my drop down list I cannot retrieve the data from new entries on my DataLink Sheet(remember coming into active workbook from outside source) unless each time I return to my DataLink Sheet,and highlight the entire range of cells and do a new Ctrl-Shift-F3, which then ask me if I want to replace each name one by one. I have some 700 names defined. And this is disconcerting to say the least. Is there a way to add code to automatically add the contents of a cell as a defined name. This way as my information from my original data workbook enters the DataLink Sheet in each of my Active workbooks, new names that appear in the left column and top row will be defined as have intersecting data under them.
I have two worksheets one called "invoice" and one called "tenants" "Invoice" has a userform where I want to enter a tenancy number and then lookup the tenants name and address from "tenants"
I have put the following macro in
Sub lookup() Dim res As Variant res = Application.VLookup("A1", Tenants.xlsx("Sheet1"), "$A:$H", 2, False) If IsError(res) Then MsgBox "not found" Else MsgBox "found at pos: " & res End If End Sub
