Serialize A Variable Without Knowing How Many Instances Are Required?

Sep 29, 2009

I'm sure there's a way to do this, I just haven't put my finger on HOW just yet. I have a list of stock tickers which can vary from 1 to say, 10. I need to use each of those 10 tickers in the subject line of a message. I thought I could do something with SPLIT, but that's not working for me. Here's what I have so far: u = the count of tickers. Column O contains the list of unique tickers

View 4 Replies


ADVERTISEMENT

Variable UserForms - Runtime Error 424 / Object Required

Jan 27, 2014

I am new to userforms and am trying to make the options in a ComboBox the entries in a list of cells. When I try to show the userform an error appears saying "Run-time error 424: Object Required".

I am only assuming that it is the ComboBox which is causing the error because I made one very similar to this ealier but without the independent ComboBox option. Is my method of assigning the options to the ComboBox correct?

The coding I am using to try to fill the ComboBox is below:

Code:
Private Sub UserForm_Initialize()
Dim n As Integer

With ComboBox1
n = 45
While Cells(n, ActiveCell.Column + 1) ""
.AddItem Cells(n, ActiveCell.Column + 1)
n = n + 1
Wend
End With

End Sub

View 1 Replies View Related

Object Required Error Setting Range Variable

Aug 22, 2007

code is getting an error on the bold line below. My error message is run time error 424 - object required.....

View 7 Replies View Related

Excel 2003 :: How To Count Cells With Instances Text Not Instances Of Text

Aug 17, 2012

I'm using WinXP with Excel 2003 - I have a column of highway sign description data (16k+ rows).

Example:

Curve Arrow Right
Curve Arrow Left
Turn Arrow
Reversing Curve Arrow Right
Winding Road Arrow(plus many more unique entries)

I'm using SUM and COUNTIF to total the number of times "Curve", "Reversing", "Turn" and "Winding" appear in the column.

My formula is:
Code: =SUM(COUNTIF($F11:$F16196,{"*CURVE*","*REVERSING*","*TURN*","*WINDING*"}))

Which works great EXCEPT what I really want is the number of cells with any of those key words, not the total count of those words. The example above should be 5, but since row four contains more that 1 of the key words I'm getting 6.

View 4 Replies View Related

Naming New Tab Without Knowing Its Name

Feb 16, 2012

Is there a way to name a new tab without knowing its name. For instance, when I create a new tab, it could be named "Sheet2" or "Sheet3", but there is sometimes no way to know. Is there a way to edit the name of that new tab without refering to it by name?

View 1 Replies View Related

Knowing The Exact Datas

Feb 19, 2010

My date of birth is 09/08/1970.I want to know that as on today,which years,months and days I have passed?

View 3 Replies View Related

Reference Workbook Without Knowing Name

Jun 26, 2007

I have a master workbook, meeting1.xls which is used on a daily basis. All of the macros saved in this workbook naturally refer to Windows("Meeting1.xls").Activate.

However, I want to be able to open Meeting1.xls save it as a new name (for today's details) and then populate it.....as well as have the Macros pick up that new file name (rather than always looking for Meeting1.xls.)

I've tried saving Meeting1 as a template file and that didn't seem to work either. When I save it as a new name, the macro always searches for the original name.

View 9 Replies View Related

Macro To Format The Auto-Sum Row Without Knowing What Row# It Will Be

Mar 20, 2009

I currently have a macro setup that puts Auto-Sum data in the row below the bottom-most row of data. What code would I need to format this Auto-Sum row with the following parameters automatically...

1. Merge & center F & G in the Auto-Sum row with the text "Totals".
2. Change the row height of the Auto-Sum row to 44 pixels.
3. Center the vertical alignment of the Auto-Sum row.
4. Put the thickest border around the Auto-Sum row in cells A-K.

View 14 Replies View Related

Macro Knowing Currently Opened Sheet

Mar 19, 2007

below you will see my macro. I have a slight problem, everything works perfect, except when I change file names. Basically I keep an electronic backup every day of the file I create. So today's file will be named DOH031907, tomorrow DOH032007, Wednesday DOH 032107, etc. etc. I use the same sheet, including the same Macro every day so it does the same thing. Basically everywhere you see "DOH031907Test.xls" I want it to put the current workbook file name there. I'm new to this kind of stuff but it seems like there is an easy solution.

Sub DOHComplete()
'
' DOHComplete Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
ChDir "K:PICDailyMetrics"
Workbooks.Open Filename:="K:PICDailyMetricsGMDEMAND2.XLS"
Windows("DOH031907Test.xls").Activate
Sheets("EDI Demand").Select
ActiveWindow.SelectedSheets.Delete
Windows("GMDEMAND2.XLS").Activate
Sheets("EDI Demand").Select
Sheets("EDI Demand").Copy Before:=Workbooks("DOH031907Test.xls").Sheets(3)

Added Code tags - Moderator

View 9 Replies View Related

Knowing The Worksheet Number Or Letter

Apr 5, 2007

Without using VBA code, is there a way to display or find the worksheet number of the active worksheet you are viewing? All my sheets have names, and I have a lot of them.

When I want to loop through a set of them with code, I want to know what numbers they are beforehand.

View 9 Replies View Related

Deleting Sheets Without Knowing The Names

Jan 19, 2009

Is it possible for a macro to delete any sheet that within a workbook that does not match a specific list of sheets. For example a workbook will always have Sheet1, Sheet2, Sheet3, Sheet4... but it can have many other sheets added to it. The names of the sheets can be completely different however the sheets that I want to keep will always have the same name.

View 9 Replies View Related

Formula Rectified And I Find Myself Not Knowing How It Works

Mar 20, 2007

I have a nifty formula brought to me by one of the excellent members on this board. It works perfectly for what I want, but I must admit I cheated, I have a problem that was similar to the problem this formula rectified and I find myself not knowing how it works.

=IF('IIC Transfer Status'!O139="x",MIN('IIC Transfer Status'!K139+10-WEEKDAY('IIC Transfer Status'!K139-{1,3})), "" )

What Im wondering is if someone could enlighten me as to what is happening in this function, so Im not as blind to assume it is magic.

Eventually I need it to be customed tailored to work in this way: I have a column of dates, I want it to look at column A and if there is a date in it ADD 4 WEEKDAYS (business days) and return the resulting date.

i.e date in column a is march 19 plus 4 business days. Result in column B march 23.

If someone can explain how this string works that would be great, or if someone could just post a suitable function, that would work for now.

View 9 Replies View Related

File Download Without Knowing The Full Url (only Indirect Url Available)

Aug 13, 2008

have a link such as www.abc.se/downloadfile?file=12345. When opening this link in a browser the DownloadFile window popups and the file can be downloaded.

I would like to be able to download the file without involving the DownloadFile pop up (since some users have auto open enabled thus the downloadfile pop-up is not always shown)

I have tried URLDownloadToCacheFile and similiar methods but they fail as they dont have the direct link to the file.

So my question now is how can I extract the direct url?

There should be a way to capture it since it will be used in the downloadfile dialog.

View 9 Replies View Related

Lookup Data Without Knowing Return Column

Aug 25, 2006

Got a bit of a stinker here. (Well, it is for me anyway. I haven't used Excel in months)!

I want to use a VLOOKUP function (I think) to retrieve data from a list of Exchange Rates on a separate sheet.

The user enters a 'Period' number, selects an 'exchange' variable (the blue text cells). The hope is to lookup the data on the 'FX RATES 06-07' sheet and return the result in the green cells.

I've tried using VLOOKUP but one of the arguements specifies that you nominate a column to return the information - that's the problem, we don't know that information when asking Excel.

View 4 Replies View Related

Formula To Calculate Speed Knowing Distance, Destination Time And Checkpoint Times

Dec 2, 2008

On my spreadsheet i have a bunch of variables:

A1 = Arrival time at Checkpoint 1 - 5:53:08
A2 = Time to destination from Checkpoint 1 - 1:10:18

B1 = Arrival time at Checkpoint 2 - 6:00:56
B2 = Time to destination from Checkpoint - 1:02:30

C1 = Total distance from Start - 2.83
C2 = Arrival Time at Destination - =SUM(A1,A2)

D1 = Speed determined from the other variables
D1 is SUPPOSED to equal 25.00

It also is where I am stumped!!

Not only can I not figure out the math but also how to format the function to get the right answer.

ps: Arrival Time at Destination is only included in case it would be useful to find the answer!

View 7 Replies View Related

Copy Down Formula Macro Without Knowing How Many Rows To Copy Down

Feb 18, 2008

I have a macro that inserts a formula on the first row and then copies it down. The problem is the row count is variable but I know it won't exceed 5000 so I just copy it down 5000. For the blank rows I get the ugly #NAME, #VALUE, etc errors.

I know there are ways to supress those error messages, but how about a way so that the macro checks to see how many rows are populated so it doesn't copy down on the blank rows.

View 9 Replies View Related

Lookup First Three Instances

Jan 26, 2009

With the following formula, I can lookup all instances of D2 and get the average of all its corresponding values in column A (see attached). But how do I lookup a specified number of instances (say the first three) and get the average for those values in column A? For now, we’ll just have to assume that there are more than three instances of D2 on the worksheet I attached.

=AVERAGE(IF($B$2:$B$26=$D$2, $A$2:$A$26))

Would the numbers need to be in an order by date to lookup just the first three instances?

View 6 Replies View Related

Multiple Instances Of A Name?

Jan 13, 2013

I have a large dataset with a company name in Column A and a Town in Column B, e.g.

ABC Company London
ABC Company London
XYX Company Swindon
STJ Company Bristol
STJ Company Bristol
STJ Company Bristol
DEF Company London
DEF Company London
DEF Company London

and I need to identify every instance where the same town appears for each instance of each individual company and add in Column C incrememnt the town name, so the above should end up looking like this:

ABC Company London London
ABC Company London London-1
XYX Company Swindon Swindon
STJ Company Bristol Bristol
STJ Company Bristol Bristol-1
STJ Company Bristol Bristol-2
DEF Company London London
DEF Company London London-1
DEF Company London London-2

I'm thinking I need to use some sort of lookup, but because of the volume of the instances of each company I have no idea how many instance appear in the data set. How could I go about achieving this and incrementing the town names in Column C automatically for each instance?

View 6 Replies View Related

Return 2nd And 3rd Instances

Feb 24, 2010

I have text strings containing 3 commas at all times (see below for example). All instances of the commas are names at either end of the comma (Last, First). My request is how would I be able to retrieve *only* the 2nd and 3rd names (in separate cells) and omitting the rest of the text?

Example:
A1 - Source
2/1/2010 Random - Text - Containing 1LastName, 1FirstName 2LastName, 2FirstName 3LastName, 3FirstName Some Other Text 555555556 1234567890 1 0 0

B1 - Requested
2LastName, 2FirstName

C1 - Requested
3LastName, 3FirstName

View 9 Replies View Related

Get The Right Most After A Character Having Multiple Instances

May 28, 2014

What I want to do today is, I want to get the extension (Type) of a file from the string. I have managed to get formula for that.

PHP Code: 

=IFERROR(RIGHT(C12,LEN(C12)-FIND(".",C12)+1),"") 

But the problem is, if the name of the file also have any dots, it is not giving correct result.

For Eg, if the string is ABCD_V1.0.mpp, I am getting 0.0.mpp not .mpp

NB: The file name may or may not have dots. So cannot tell that, it is always the second instance. Need to find the last dot and trip the things before that

View 11 Replies View Related

How To Count Instances Of A Row Of Several Cells

Dec 13, 2013

Basically, I have a table with rows as person, and variables as features. If a person meets a feature, the that cell writes 'X', now, I hope to create a master variable that capture all the features, basically counting how many features a person has met. For example, Joe has three features; "have balls," "cool guy", "good with ladies". In the cell in question would be "X" "" "X". And if I count, then it should be 2.

View 1 Replies View Related

Match And Report All Instances?

May 9, 2014

I have four columns with data. I need to Correlate columns 1 and 4. eg. Column 1 = All instances of Column 4. I must do this by matching the values of Column 2 to Column 3. There will/can be multiple instances of the same values in all columns.

View 1 Replies View Related

Find Multiple Instances Of A Value

Apr 28, 2009

I have four different sheets with email addresses in an excel file. There are some email addresses which are present in more than one sheet. I wanted to find these email addresses and remove its multiple instances.

I can't use the simplified version of vlookup, as I can't enter each email address to see if there is another instance.

Is there a way where the excel sheet can search within itself for duplicate values and if not delete/at least point them out, so that I can delete them manually?

View 3 Replies View Related

Cannot Paste Between Two Different Instances Of Excel

Jul 2, 2013

I am having trouble pasting between two spreadsheets in different instances of Excel.

I am copying a range of cells and then attempting to paste using ctrl + v/ right-click + paste in a workbook in the other instance of excel. It just selects the area where the cells would be pasted but nothing appears.

The only way I can get it to work is by using the clipboard task pane and clicking paste from there however this isn't a long term option as I need to code this into a macro. I also don't understand why there is a difference between pasting from the clipboard panel and pressing 'ctrl v'.

There are not macros running at the time and pasting into another program e.g. notepad works fine.

View 2 Replies View Related

Search For All Instances Of A String

Jul 11, 2009

I'm using the following code which is working well. It finds an instance of the string "A83". Once it finds it, it moves down x number of cells and populates it with a new string. It then moves down another x number of cells and populates it with another new string, etc.

The problem is, it is only looking for "A83" one time and performing the appropriate actions at that point. What I need to do is look for *every* instance of the string "A83" and perform the appropriate actions.

View 10 Replies View Related

Close Workbooks In Other Instances

Feb 5, 2010

I have one main workbook that, when opened, opens three other workbooks each in its own instance of Excel. I'd like to know how to close all three of these upon closing this main workbook.

View 2 Replies View Related

Vlookup For Same Name, Multiple Instances...

Jan 7, 2005

I want to return the 2nd field for Jim in a single list:

Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want to pull Jim's accounts to another worksheet:

HP
Toshiba
Lenovo

When I use vlookup, it just returns HP and stops. How do I tell it to
return the next instance of Jim?

View 14 Replies View Related

Instances That Occur In A Sequence

Mar 14, 2008

I have a problem, I have a formula which counts the number of instances that occur and assigns the value as 1 for every instance, however I want the formual to also recognise that if a number of instances occur in succession a value of 1 should also be assigned.

E.g. if a person is absent for 1 day the formula assigns a value of 1
if a person is off for 3 days in succession the formula assigns a value of 1

View 13 Replies View Related

Trying To Count #N/A Error Instances

Apr 14, 2009

I need to count instances in a column where the error #N/A is returned. I thought that the following formula would work, but I'm getting #NAME?:

View 2 Replies View Related

Copy Between Different Instances Of Excel

Feb 10, 2012

I am trying to copy a range from one workbook to another. The complex part is that the the two workbooks are different instances of excel and one of them is hidden.

In the below code inSheet is a worksheet on a work book that is hidden.

Code:
batchSheet.Select
Range("K" & lr).Resize(1, 10).Copy
inSheet.Select
inSheet.Range("O7").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

A formula like this is working fine. The above gives me an error.

Code:
inSheet.Range("C16") = batchSheet.Range("D" & lr)

View 9 Replies View Related







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