Defining a variable to = MATCH

Dim thingy As Integer

thingy = "=MATCH(""DiceC"",qperiodagentperformance!A:A,0)"

ActiveCell.Formula = _
"=INDEX(qperiodagentperformance!D" & thingy & ":D13000,MATCH(""Agen
Summary"",qperiodagentperformance!A" & thingy & ":A13000,0))"
Range("D26").Select

my thingy doesnt seem to work..... no comment

Any suggestions on correcting this?
Mik

--
Message posted from http://www.ExcelForum.com

0
8/30/2004 8:27:44 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
271 Views

Similar Articles

[PageSpeed] 16

Try with

thingy = Application.Match("DiceC",
Sheets("qperiodagentperformance").Range("A:A"), 0)

and define thingy as long instead...

-- 
Regards

Juan Pablo Gonz�lez

"mpjohnston >" <<mpjohnston.1btj86@excelforum-nospam.com> wrote in message
news:mpjohnston.1btj86@excelforum-nospam.com...
> Dim thingy As Integer
>
> thingy = "=MATCH(""DiceC"",qperiodagentperformance!A:A,0)"
>
> ActiveCell.Formula = _
> "=INDEX(qperiodagentperformance!D" & thingy & ":D13000,MATCH(""Agent
> Summary"",qperiodagentperformance!A" & thingy & ":A13000,0))"
> Range("D26").Select
>
> my thingy doesnt seem to work..... no comment
>
> Any suggestions on correcting this?
> Mike
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
JuanPablo1 (72)
8/30/2004 8:42:49 PM
Altough that will cause an error, 'cause thingy is dimmed as an integer...

-- 
Regards

Juan Pablo Gonz�lez

"Erin Searfoss" <ErinSearfoss@discussions.microsoft.com> wrote in message
news:CD96ECD4-4A25-4E62-8D0E-DC302BCD6093@microsoft.com...
> Try taking out the "=" in the thingy = line i.e.
> thingy = "MATCH(""DiceC"",qperiodagentperformance!A:A,0)"
>
> "mpjohnston >" wrote:
>
> > Dim thingy As Integer
> >
> > thingy = "=MATCH(""DiceC"",qperiodagentperformance!A:A,0)"
> >
> > ActiveCell.Formula = _
> > "=INDEX(qperiodagentperformance!D" & thingy & ":D13000,MATCH(""Agent
> > Summary"",qperiodagentperformance!A" & thingy & ":A13000,0))"
> > Range("D26").Select
> >
> > my thingy doesnt seem to work..... no comment
> >
> > Any suggestions on correcting this?
> > Mike
> >
> >
> > ---
> > Message posted from http://www.ExcelForum.com/
> >
> >


0
JuanPablo1 (72)
8/30/2004 10:00:47 PM
Another:

dim Thingy as Variant

Thingy = application.match("dicec",worksheets("qperiodagentperformance") _
            .range("a:a"),0)

if iserror(thingy) then
  msgbox "not found"
else
  'msgbox "found in Row: " & thingy
  'do the rest of you stuff here.
end if


"mpjohnston <" wrote:
> 
> Dim thingy As Integer
> 
> thingy = "=MATCH(""DiceC"",qperiodagentperformance!A:A,0)"
> 
> ActiveCell.Formula = _
> "=INDEX(qperiodagentperformance!D" & thingy & ":D13000,MATCH(""Agent
> Summary"",qperiodagentperformance!A" & thingy & ":A13000,0))"
> Range("D26").Select
> 
> my thingy doesnt seem to work..... no comment
> 
> Any suggestions on correcting this?
> Mike
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10083)
8/30/2004 10:21:23 PM
Reply:

Similar Artilces:

Adding variables to a static text control (displaying contents of array)
Hello, I was wondering if the following was possible ? I have a static text control in a dialog application. I also have an array which contains about 5 items. I would like the text control to display the contents of this array and then have a button which would move to next position into the array and thus the static text control would display something different ? How could achieve this? I'm using Visual Studio 6.0 and I'm quite new to MFC Many Thanks. >I have a static text control in a dialog application. I also have an >array which contains about 5 items. I would lik...

CListBox and ownerdraw (variable height)
Hi, Iam writing a control deriving from a CListBox and using customdraw (variable height). The problem is with InsertItem and MeasureItem. Indeed when I call InsertItem : int InsertItem(const ILBITEM* pItem) { .... int iItem = InsertString(pItem->iItem, pNewItem->pszText); //---->MeasureItem is called if( iItem>=0 ) SetItemDataPtr(iItem, pNewItem); } MeasureItem is called just after and since I didn't have time to associate my new item with its index I cannot retrieve its fields. void MeasureItem(LPMEASUREITEMSTRUCT lpMeasureItemStruct) { PILBITEM pOrgItem = (PILBI...

Collections Management
Dynamics GP v9 - Collections Management Users are unable to use "user defined letters" (Word Documents) for mass mailings. They are limited to only using predefined letters, or printing each letter individually for every customer. Error received when users attempt to use a custom letter: -------------------------------------------------- Unhandled script exception: Cannot find report "COL_Reminder_UpcomingDue". EXCEPTION_CLASS_SCRIPT_MISSING SCRIPT_CMD_REPORT -------------------------------------------------- ---------------- This post is a suggestion for Microso...

Application-defined or object-defined error
Hi, I find myself stumped by an incredibly easy piece of code and one that I have used before. I am getting the error: Run-time error '1004': Application-defined or object-defined error I am getting the error when I run the following code: Private Sub Workbook_Open() With Application .ScreenUpdating = False .DisplayAlerts = False Workbooks.Open "\\depot02\rel\www\internal\business_areas\edg\Metrics\Phones\HighHoldTimesDetailed.xls" Workbooks("HighHoldTimesDetailed.xls").Worksheets("data").Cells.Copy _ Workbooks("phoneholdtime...

runtime error 2465: application-defined or object-defined error
I'm trying to use a button on a main form to change the sort order of a sub- subform.When I execute the following, I get runtime error 2465: application- defined or object-defined error. opting to debug, I find the line with orderbyon highlighted. Anybody see what's wrong? Private sub timesort_click() Forms![control]![worklist]![approved].Form.OrderBy = "Forms![control]! [worklist]![approved]![time]" Forms![control]![worklist]![approved].Form.OrderBy0n = True End Sub -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/a...

how to do self-defined regression in excel?
Hi, I want to do regression ananlysis in excel using a sigmoid function, which is not offered in the general 6 functions in excel(by adding trendline). How can I do it? Thanks. The Analysis ToolPak - VBA addin has a regression function and probably everything else you want. Tools > AddIns then checkmark Analysis ToolPak - VBA Go back to Tools dropdown and it should be listed near the bottotm of the dropdown. >-----Original Message----- >Hi, I want to do regression ananlysis in excel using a >sigmoid function, which is not offered in the general 6 >functions in excel(by a...

SafePay Transaction type matching code ?
Hello, there is a button at the top of the safepay configuration screen (GPv10) where you enter a matching code for checks, voids and eft types. I'm confused on this. What are these matching codes? Can someone please explain this little screen to me. Thanks. JPP Nevermind, I realize this comes from the bank. ...

Defining a variable to = MATCH
Dim thingy As Integer thingy = "=MATCH(""DiceC"",qperiodagentperformance!A:A,0)" ActiveCell.Formula = _ "=INDEX(qperiodagentperformance!D" & thingy & ":D13000,MATCH(""Agen Summary"",qperiodagentperformance!A" & thingy & ":A13000,0))" Range("D26").Select my thingy doesnt seem to work..... no comment Any suggestions on correcting this? Mik -- Message posted from http://www.ExcelForum.com Try with thingy = Application.Match("DiceC", Sheets("qperiodagentperformance"...

Adding third lookup value to index/match
I understand how Index/Match works with double lookups... But I cant quite get the third lookup down. Looking to add a third lookup value and range to this formula (range z) =INDEX(table, MATCH(x,range x,0), MATCH(y,range y,1)) Data is laid out like so... (Vertical column range is x) A B C D E F G H 1 range yyyyyyyyyyyyyy -> 2 range zzzzzzzzzzzzzz -> x x x x x x any suggestions...? How does the 3rd range relate? Need more specific details. -- Biff Microsoft Excel MVP "J.W. Aldridge" <jeremy.w.aldridge@gmail.com> wrote in mess...

Where do I initialize variables in a report?
Hi, Where do I initialize public variables in a report? I have a public variable where I put a sequential item number on each print line. In the On Open event, I initilalized this ublic variables to 0. I then do a print preview and a print. The print preview works fine, but the print picks up the count from where the print preview left off. I moved my initialization the the Report Header On Print event and it works fine. Is this the right way to initialize this variable or is there a more appropriate event to initialize my variable. I tried On Activate, but that di...

User-Defined type not defined?
I recently imported all my data into a blank DB now I am getting this error on: Dim wrk As Workspace Is that something to do with me importing into a new DB Thanks for any help...........Bob Sub SelAllNone(Optional SelectAll As Boolean = True) On Error GoTo stoprun Dim sqlStr As String Dim wrk As Workspace Dim db As Database Set wrk = DBEngine.Workspaces(0) Set db = CurrentDb sqlStr = "UPDATE [tblHorseInfo] SET [Worksheet] = " & SelectAll & ";" wrk.BeginTrans db.Execute sqlStr, dbFailOnError wrk.CommitTrans Exit_Here: Set wrk = Nothing Set db = Nothing ...

passing data from variable arguments fonctions
Hi, How can I pass data from a variable argument fonctions to another one. For instance I would like to do something like : int Log::Debug(LPTSTR tszFmt, ...) { ULONG bytesWriten = 0; TCHAR tszBuffer[2048] = {0}; va_list args; int iIndex = 0; iIndex = PreFormat( tszBuffer, tszFmt, args ); } with Preformat defined like this : int Log::PreFormat(LPTSTR tszBuffer, LPTSTR tszFmt, va_list p_args) { int iIndex = 0; SYSTEMTIME tm; TCHAR tszTime[ 32 ] = {0}; if (m_bAddTime == true){ GetLocalTime(&tm); // Get TimeStamp _stprintf(tszTime, _T("%.2x:%.2x:%.2x\t&quo...

"too many fields defined" error
I am trying to add a field to my table and keep getting an error message "too many fields defined" and it will not let me add the new field. Does anyone know what I have done to get this error and how I can fix it so I can add fields to my database? How many fields do you have in your table? The maximum number for a single table is 255. Note: it's rare that you would need more than 30 fields in a table if you've properly normalized your table structure. -- Ken Snell <MS ACCESS MVP> "Rachel" <Rachel@discussions.microsoft.com> wrote in ...

How to create an initialised object declared as a class member variable?
I am working on a VC6++ project that involve an object to be initialised by a 'this' pointer pointing to another object. I encountered, however, a syntax error. I wonder if someone can help. See the code below. What I couldn't figure out is it is OK with CMyClassA B2(this); in fileB.cpp but a SYNTAX ERROR with CMyClassA m_B2(this); in fileB.h. What should I do in order to create a initialised object of CMyClassA if I really need a class member variable (m_B2) rather than a local (B2)? Thanks for any help. /...

Index, match, multiple IFs query
I have the following individual functions =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,ONE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,TWO,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,THREE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FOUR,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FIVE,0),5) I need to combine them all in the same cell so IF F7 matches data in ranges ONE, TWO, ETC it will return whatever is in column 5. I’ve done some searching on the net and also looked at Microsoft’s help on this. I can see how to create a multiple function if I want it to be ONE & TWO but not ONE or TWO. I’ve ...

sheet variable
I am looking for a way to use a list value from a data validation (cell D9) to use as a value in =OFFSET(Merry!C6,COUNTA(Merry!C6:C364)-6,-1) So Merry would be removed and the variable put in it's place. Each time I reference the cell D9 I get a #VALUE! error. Any help would be appriciated. maybe... =OFFSET(indirect("'"&d9&"'!C6"),COUNTA(indirect("'"&d9&"!C6:C364"))-6,-1) wagz wrote: > > I am looking for a way to use a list value from a data validation (cell D9) > to use as a value in =O...

user defined fields 01-12-10
I have created a user defined field "GHINno". Using copied code I can emumate user defined fields and find "GHINno". I have code the Sets folder to the "Folder"- "Set MyContact = MyFolder.Folders("Seven Hills Mens Club")". But I can't figure out how to get to the value(s) in "GHINno", either the first value or loop through all values. Any help with UserProperty or Userproperties or whatever would be appriciated. Joe See http://www.outlookcode.com/article.aspx?ID=38 for info on property syntax. You can use a F...

Can't find source of Application-defined or object-defined error!?
I won't post all of the code here because it's too extensive but here's a watered down version (all variables declarations are not listed): Dim i as integer Dim PortName As String Dim ReviewSht As String Dim ModelSht As String Dim HoldingsSht As String Dim Model As Range PortName = Right(ActiveSheet.Name, Len(ActiveSheet.Name) - 15) ReviewSht = "Price Weight - " & PortName ModelSht = "Model Data - " & PortName HoldingsSht = "Holdings - " & PortName Application.ScreenUpdating = False With Worksheets(ModelSht).Range("A9") Range...

Out of context error in user defined function
Apologies to anyone who has already read this in Functions. I realised after a few daysa of no response that I had posted in the wrong stream. Excel 2007 query Error received in VBA window (Debug) = <Out of Context> Error on Spreadsheet = #VALUE# I have 'formatted' this function in much the same way as my twenty or more other functions in this spreadsheet, yet I end up with Out of Context. I have tried various approaches including an 'On Calculate' called sub-routine, but all to no avail. The code for the function is stored in Module1 of an xlam, w...

Mix and Match report
I need a report that shows me how many items were sold as mix and match and not as singles. I hope that makes sense. thanks in advance Doug The Price Source column in the detailed sales report can be used to filter for items that were discounted because of a quantity discount like a mix and match Let's say you have a mix and match called "Footballs 2 for 10% off"; Run the detailed sales report and filter for Price Source CONTAINS "football", it should show you all the instances an item was discounted because it met the mix and match criteria. Hope this help...

Value of cell Defines Range
Hello, I am trying to set a picture to a range of cells which I can do by setting the picture to a range like =$A$1:$C$5. My problem is that the range can change anytime. It will always start in $A$1 and end in column $C but the row for column $C can vary. I know what row column $C should be but I cannot not change the range automatically but have to do it manually. I can put the value for the row in column $C in a cell like D1 but I cannot think of a way to put that in the range automatically (See Example below). Any ideas? Example Range is =$A$1:$C$?. D1 contains the value of 8. So I want ...

Public Variable
I have encountered an interesting issue that I have no idea how to fix. I have a form that has a button to launch a report based on the current record that is being pointed to. The report runs from a query that is filtered to that specific record number - the code declares a public variable, sets the variable to be empty when the form opens and then sets the variable = to the record info needed for the query to filter. Runs perfectly fine on my desktop but I can't get it to run on my laptop...I keep getting a dialog box to enter a parameter. Both systems are using Access 2003. ...

User-Defined Type Not Defined
Seemingly all of a sudden I get the message User-Defined Type Not Defined every time I enter data into any spreadsheet cell. I do not use VB or macros or anything fancy. I don't remember hitting an odd key or ending up in an odd area of Excel. I tried opening a new spreadsheet and the problem persists. I use Excel 2002. How can I stop this from happening? Thank you. -- lg Do you have any add-ins loaded? If so, it is probably an add-in misbehaving. Go in to the VBA Editor, and for each project listed in the Project Explorer (CTRL+R to view it), go to the Tools menu and choose...

Write-off err "No documents were found matching the criteria selec
I receive the following error message when I try to write off documents by using the write-off process in Microsoft Dynamics GP: "No documents were found matching the criteria selected". The error is received no matter what options are selected (many combinations have been tried) using either the Process or Preview buttons. When printing is attempted, RM Writeoff Documents Preview under Report Infomation in the Report Destination dialog window appears, but only an empty report results. Reports->Sales->Trial Balance shows lots of entries, but the write off routine d...

importing leads, branches defined but won't show in the conversion table
Hi all, I'm trying to import loads of leads but am having some difficulty getting all the information in CRM 1.2 that I want. We wish to import a branch discription ("industrycode" ? / we use the Dutch version) but if I add values to this record they don't show up when I use the lead-import wizard. I can only select the original values that where there from the get go. Anyone any Ideas?? rgds Patrick you need to publish your changes through deployment manager and then do an IISRESET to restart the web server service "Haas" <p.haasewinkel@ecostar.nl> w...