# Finding Component Parts Of A Sum

Apr 2, 2007

I often come across a situation where I know a given total, and I know it comes from numbers within a given list, but I do not know which particular numbers they are. The onyl way to find out is to add every single combination of all the numbers in the list. I want to know if there is any formula within Excel which would help me to do that, given that the numbers of combinations are:

Number of items in list Number of combinations
1 1
2 3
3 7
4 15
5 31
6 63
7 127
8 255
9 511
10 1023
Obviously, I would like to be able to do this for any number of items.

## Sorting In Spreadsheet Where Lines Are Sequenced By Finished Good And Component Parts

Jul 29, 2013

I have a very large inventory spreadsheet (30,000+ lines) where finished goods are listed on one line and the raw materials (parts) that make up that finished good are listed below. After the last raw material for a finished good is listed, the next finished good is on the next line (and so on). First, I need to sort the lines to eliminate certain common raw materials. Next I need to take the lowest raw material inventory level and have that as the default inventory level for the finished good.

## Finding The Largest Numerical Component From An Alphnumerical List

Jul 20, 2006

I have a small problem with a list of event codes in the form XX###, (Xs are letters, # numbers), I need to be able to find the largest numerical component in the list, and am having problems finiding a neat way of doing it.

I could do it by using the RIGHT command to get the numerical bit in a seperate column, then convert it to number, then use LARGEST, but this seems somewhat cumbersome!

## Print Parts In Portrait Parts In Landscape

Dec 21, 2006

I have a document needed to be printed with some pages in the middle in landscape page type, the rest in portrait. If using Word it would be easier, but in Excel I cant find the section break to chage page setup separately. Is there anyway to do it. Currently I'm printing the document separately in portrait and then landscape with some page break added and page number modified. However it's quite troublesome and easy to make mistake.

## Lookup Function: Compare Every Part In The All Parts Worksheet To See If The Part Number Exists On The Active Parts Sheet

Dec 10, 2008

I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.

I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".

## Search Parts Of Columns Looking For Matches In Parts Of Other Columns In A Row

Mar 1, 2014

I have sheets with names of people in columns....some married...some not. When they are married, here's a sample format...

Jones, Donald T | Baker, Sarah Jane | Jones, Sarah Jane | Smith, Sarah J | Jones, Sarah Jane Smith

In this example, I would like to be able to determine which of the Sarah's belongs to Donald w/o having to visually look at each record ( 100,000's of records). (FYI: the names for Sarah would/could be her Maiden Name and possibly a name or two from a former marriage). What I need to be able to do is match and extract the names of Jones, Donald T and Jones, Sarah Jane and Jones, Sarah Jane Smith and eliminate Smith, Sarah J and Baker, Sarah Jane.

In my example, Donald is in the first column, but can be in any column on a row so the name positions are random across the columns. However, the format for each column is then same...Last Name, First Name Middle Name(or Initial) with a comma always after the last name in each column. The length of the last name also varies.

VBA or Formula that will search the cells in the columns of each row and return the names (complete contents of the cells with matching last names) that have a matching last name for that row.

## Active X Component Error 429

Nov 29, 2009

I keep getting this error message: Error 429: ActiveX Component Cannot Create Object

I fond a site that explained the problem: "If Microsoft Excel 97 is installed after an application created with Microsoft Visual Basic 5.0 that uses DAO is installed, Excel does not add the DAO design-time licensing key. This licensing key is required by Excel, but is not required by applications created with Visual Basic."
But I can not figure out how to fix it. I changed the registry and added the licensing key, but I still got the error.

## VB Component: Remove Before Close

Apr 15, 2005

I have been deleting some VBComponents by using the .remove command and they get tagged for deletion but do not actually get deleted until after my VBA sub ends and control is returned to excel.

I would like to save the changes before control is returned to the user. I'm not really sure why the deletion is being held up until execution ends, it even waits until all calling functions have also finished their execution.

## Nested IF Statements To Incorporate Another Component

Jul 1, 2009

I have this nested if statement, but my last IF statement needs to incorporate another component.

## UserForm - Component Not Correctly Registered

Feb 19, 2012

When I try to open an Excel-file from my employer with Excel UserForms I recieve following error messages

Code:
Component not correctly registered
and
Code:
Unexpected error (336)

I can see the code behind the UserForm, but not the UserForm. I've made the same mistake myself last time I created an UserForm, but I don't know what to do different.

## Formula With A Variable Sheet Component

Jul 25, 2007

I have a control sheet that needs to fetch numbers from a different worksheets depending on the worksheet name that I type into a cell. So if I type "sheet1" into cell A1 then I want the formulas on the sheet to automatically change fetch the data from sheet1. Then if I type in "sheet2" for example, the formula must then look at sheet2 for example. The source sheets are all identical in layout so not bothered about the actual cell ranges. I can do this in VBA but would like to keep this simple in Excel formulas.

## Run-time Error '429' ActiveX Component Can't Create Object

Jan 26, 2010

I am trying to generate excel report output from access by click of a button. Part of the code is as follows:

Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xlc As Object
Dim DBS As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean

blnEXCEL = False

'If we do not want the first row we set Header to false

'Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear

I worked on development on a server and worked fine. However when I have moved the code and database to the desktop of mine I am having
the following error in the line

Set xlx = GetObject(, "Excel.Application")

Run-time error '429'
ActiveX component can't create object

## Runtime Error 429 - ActiveX Component Can't Create Object

Dec 8, 2012

Following bit of code runs fine if placed in Word VBA:

Code:
For Each tsk In Tasks 'Gives RTE 429 when run through Excel
Debug.Print tsk.Name
Next tsk
End Sub

So I referenced 'Microsoft Word 12.0 Object Library' in Excel and ran the code and it gave me this error 429. Noticeable part was, no instance of word was running at that time.

So I modified the code as below:

Code:
Dim wdApp As Word.Application

[Code]....

## Countif Statement - Calculation Performed For Range Component

Dec 14, 2012

Is it possible to insert a formula or have a calculation performed for the range component when using the countif statement.

I am trying to take the entries in the rows in column B and divide its corresponding row in column A and then count how many meet a certain criteria.

## Component Life Cycle Management - How To Extract Correct Data

Apr 30, 2013

I am trying to create a life-cycle management sheet that is simple for the user but gives specific data.

The user would input "Date", "Machine type", "Component Set No.", and "Component". This would happen over a period of time and build up a picture of how often each component was lasting and it's average life in days.

I am fine from the user input side but am struggling to figure out how to extract ONLY the data I need to display in a graph.

For example: If I want to see the average no. of days "component X" is lasting before being replaced I've not been able to extract just that data. I have tried using a pivot chart but this won't do exactly what I'm after.

See the attached for a clear explanation : Component LifeCycle Example.xlsx

## Error - Number:429:' GetObject. ActiveX Component Can't Create Object

Jan 4, 2007

VBScript code snippet below is being used in an ASP page but the call to GetObject never attaches to a running instance of Excel. Since visible property is set to True, I can see the instances accumulate in the TaskBar each time the ASP page is called, so there are obviously instances to attach to.

Dim excelApp
Set excelApp = Server.GetObject(,"Excel.Application")
If (err <> 0) Then
Set excelApp = Server.CreateObject("Excel.Application")
End If
excelApp.UserControl = True
excelApp.Visible = True
excelApp = Nothing

GetObject errors out with the following error: Error - Number:429 Source:Microsoft VBScript runtime error Description:ActiveX component can't create object

## Run Time Error 50034 Method Export Of Object Vb Component Failed

Jan 30, 2010

I keep getting this error on this line

[code]ThisWorkbook.VBProject.VBComponents("Module2").Export FileName[CODE]

of this

## Compare Multiple Column Of Data And List Out Common And Unique Component In Adj Columns

Jan 23, 2006

I am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.

BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34

AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12

## Vba Error Run-time 429 Activex Component Can't Create Object - Excel &amp; Outlook 2007

Aug 11, 2008

I have some VBA code that in one portion of it an Outlook e-mail is created and the active workbook is attached and sent. This code used to work on Excel & Outlook 2003, but now in Excel & Outlook 2007 I'm getting the error:

Run-time error '429':
ActiveX component can't create object

and is stopping on the line

Set OutApp = GetObject(, "Outlook.Application")

FY If I comment out the Set OutApp... and If OutApp.... lines and uncomment the Set OutApp create object line, I still get the same error.

Dim OutApp As Object
Dim OutMail As Object
Dim SigString As String
Dim Signature As String

'Set OutApp = CreateObject("Outlook.Application")
Set OutApp = GetObject(, "Outlook.Application")
If OutApp Is Nothing Then Set OutApp = CreateObject("Outlook.Application")

OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

## How To Move Parts Of A Row

May 30, 2014

I am trying to move some parts of a row (From G:AE) where text is found in G Column. It moves it back 1 column from G

[Code] .....

Attached File : ozeform.csv‎

## Cutting File Name In Parts

Mar 28, 2014

I have some files on disk where I need to copy a part of the file name to make a search in our database. The files are PDF. They can have a long name of short. The only thing that does not change is the last part.

The last part ends like this:
1234_vw golf_Es_1234567_20140321.pdf

I'm interested in the part in red to be taken out and put in a cell to the right of the file name.

I used the below code that I found googling. Now I just to cut the part out in red to be able use it in my search in a easier way.

[Code] .....

## Seperating Parts Of A Phrase Using Vba

Aug 3, 2009

I have a phrase in a listbox formated as such: "Company Name / Company ID # / City, State"

I need to be able to seperate the values out into seperate categories:
Company Name
Company ID #
City
State

Is there any way to do this within the code?

## Copy Only Used Parts Of A Range

Dec 19, 2009

I'm making a project where there's a list of skills and next to each skill is a cell where the user can put how many skill points are in that particular skill. Now, on a separate worksheet is the place where it shows the consolidation of what skills they know, and how many points are in each. My question is, how can I copy over these skills and how many ranks are in each, without copying over the skills in which they put no points.

## Combining Parts Of Two Cells

Jul 27, 2009

I have a list of dates in column A as follows:

11/1/2015
5/1/2012
8/8/2013
10/22/2015
4/1/2014
4/15/2014

In column B I have set of data as follows:
C 2010@101.0
C 2011
C 2011@101.0
C 2012@101.0
C 2013
C 2013
C 2014@101.0

I need to combine the day and month only in column A with the year (in positions 3-6) in column B

As an example I have "11/1/2015" in column A and "C 2010@101.0" in column B. The desired result in column C is "11/1/2010".

I have tried using the left function in column A with the mid function in column B but haven't had any luck so far.

## Cutting Columns In Two Parts

Nov 28, 2013

I have some values in column A which looks like those in table which is shown down bellow. I have somewhere in the middle of that column one empty cell. Of course i have empty cells after the last value. I want formulas or macro or anything which will cut that column in that empty cell and all cells bellow the empty one put in column B from A1, and all cells above the empty one put in column C from A1. I tried everything but i have problem because i dont know how to eliminate empty cells bellow the last cell with value.

ABC
117
228
339
44
55
7
8
9

## Create A Userform That Has Several Parts To It

Aug 15, 2007

I want to create a userform that has several parts to it.

1stly i want to copy information into the text boxes from a macro.

2ndly the number of text boxes could be infinate (in theory), i want 1 for each set of info

3rdly i want a little option tab box thingy for each box,

4thly the boxes and userform to adjust to fit the optimal size

## Protecting Parts Of A Sheet.

Dec 20, 2008

I am going to use Excel sheets as computer exam forms. What I need to know is: Is there a way of protecting parts of an excel worksheet from alteration? I want a sheet that will accept answers in specific areas only, and will not accept entries or alterations in other areas.

## How To Select Parts Of Text

Mar 1, 2010

Does anyone know of a way to select fragments of the following text?

The string is "toby.cameron1".

I need to be able to select just the "toby" and "cameron" parts.
Also this string is variable in that it might be "james.brown" or similar without the number at the end.

I can't use a Left or Right statement because of the varying length of names and the uncertainty of having a number at the end.

Is there a way I can copy the start of the string until the period. And do the same from the end but ignore the number whether it's present or not.

From that, is there anyway to copy the numbers off the end of a string?

i.e. my string is ASY-623 or DRGN-12 or WDSR-7

The string will always be letters a dash and then numbers.

How do I copy only the numbers?

## Separate Decimal Parts

Mar 1, 2007

I have to make a report which calculates hours worked by employees over a weekend.The hours are stored in variable ,part of code is as follows:

For j = 4 To 32
startday = Weekday(thisDate, 1)
If startday = 1 Or startday = 7 Then
dummy = 0
tempnd = Cells(i, j)
weekndsum = weekndsum + tempnd Else
dummy1 = 1
tempdys = Cells(i, j)
weekdysum = weekdysum + tempdys End If
thisDate = thisDate + 1
Next j
Windows("Report.xls").Activate......................

## Save Parts Of Workbook

Mar 27, 2007

way to save just a work sheet insteed of the whole workbook.

it is because i have 9 sheets a main menu and 8 differant departments the idea of it is is for a stock take and i want to save them daily so i can goback over them at a later date.