Troubleshoot

I am find an error in this statement, and the error shown is
"Application defined error or object defined error"

ActiveSheet.Range(Chr(r + 64 + 0) & c).Formula = "=index(" & Chr(11 +
64) & "2:" & Chr(11 + 64) & (max_date) & ",match(" & Chr(1 + 64) & r &
",L2:L" & max_row & "))"


The above statement basically used to match the data from one column
with any column, corresponding to the indexed column. used for
matching dates and times

Could any one please solve this

0
gautamkum (7)
4/19/2007 3:01:42 PM
excel 39879 articles. 2 followers. Follow

2 Replies
616 Views

Similar Articles

[PageSpeed] 23

First, you can use other ways to address cells other than .range().  In your
case, .cells() looks like it work much nicer.

..cells(x,y).formula 

The x represents the row and the y represents the column.  And y can be a number
or a letter--excel will accept either.

I _think_ that this may be closer to what you want:

Option Explicit
Sub testme()

    Dim iRow As Long
    Dim iCol As Long
    Dim Max_Row As Long
    
    'test data
    iRow = 3
    iCol = 3
    Max_Row = 777
    
    ActiveSheet.Cells(iRow, iCol).Formula _
       = "=index(K2:K" & Max_Row & ",match(A" & iRow & ",l2:L" & Max_Row & "))"
    
End Sub

Notice that I changed (max_date) to max_row.  I can't think of where I'd want
the range in =index() to have different number of rows than the range in the
=match() portion.



Gautam wrote:
> 
> I am find an error in this statement, and the error shown is
> "Application defined error or object defined error"
> 
> ActiveSheet.Range(Chr(r + 64 + 0) & c).Formula = "=index(" & Chr(11 +
> 64) & "2:" & Chr(11 + 64) & (max_date) & ",match(" & Chr(1 + 64) & r &
> ",L2:L" & max_row & "))"
> 
> The above statement basically used to match the data from one column
> with any column, corresponding to the indexed column. used for
> matching dates and times
> 
> Could any one please solve this

-- 

Dave Peterson
0
petersod (12004)
4/19/2007 4:04:12 PM
On Apr 19, 9:04 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> First, you can use other ways to address cells other than .range().  In your
> case, .cells() looks like it work much nicer.
>
> .cells(x,y).formula
>
> The x represents the row and the y represents the column.  And y can be a number
> or a letter--excel will accept either.
>
> I _think_ that this may be closer to what you want:
>
> Option Explicit
> Sub testme()
>
>     Dim iRow As Long
>     Dim iCol As Long
>     Dim Max_Row As Long
>
>     'test data
>     iRow = 3
>     iCol = 3
>     Max_Row = 777
>
>     ActiveSheet.Cells(iRow, iCol).Formula _
>        = "=index(K2:K" & Max_Row & ",match(A" & iRow & ",l2:L" & Max_Row & "))"
>
> End Sub
>
> Notice that I changed (max_date) to max_row.  I can't think of where I'd want
> the range in =index() to have different number of rows than the range in the
> =match() portion.
>
> Gautam wrote:
>
> > I am find an error in this statement, and the error shown is
> > "Application defined error or object defined error"
>
> > ActiveSheet.Range(Chr(r + 64 + 0) & c).Formula = "=index(" & Chr(11 +
> > 64) & "2:" & Chr(11 + 64) & (max_date) & ",match(" & Chr(1 + 64) & r &
> > ",L2:L" & max_row & "))"
>
> > The above statement basically used to match the data from one column
> > with any column, corresponding to the indexed column. used for
> > matching dates and times
>
> > Could any one please solve this
>
> --
>
> Dave Peterson

Thanks Dave

Macro's working

0
gautamkum (7)
4/23/2007 5:32:00 AM
Reply:

Similar Artilces:

Troubleshoot
I am find an error in this statement, and the error shown is "Application defined error or object defined error" ActiveSheet.Range(Chr(r + 64 + 0) & c).Formula = "=index(" & Chr(11 + 64) & "2:" & Chr(11 + 64) & (max_date) & ",match(" & Chr(1 + 64) & r & ",L2:L" & max_row & "))" The above statement basically used to match the data from one column with any column, corresponding to the indexed column. used for matching dates and times Could any one please solve this First, you can use oth...

Troubleshoot
Can someone help me with the following statement: =AVERAGEIFS(BeezidTable[final],BeezidTable[time],">TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))") It returns DIV/0, but it should return a proper average. Thanks, Roy Try =AVERAGEIF(Range, ">" & NOW()-INT(NOW()) , Sum Range) "rwtrader" wrote: > Can someone help me with the following statement: > =AVERAGEIFS(BeezidTable[final],BeezidTable[time],">TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))") > It returns DIV/0, but it should return a proper average. > Thanks, Roy...

Need Troubleshooting Tips
On SBS2003 w/Exchange, I have a user that has intermitent delivery failures. In most cases, there is no notification that that the email was not delivered. Here is an example: user A will log into OWA to send an email to an internal user B. To user A it looks as if the email has been sent, and a copy shows up in their Sent Items folder. User B does not recieve the email. This happens with both internal and external users, but sporadically. When I went to test it, it was working ok. This is only happening with one user. He has Outlook 2003 configured with 2 mail accounts, Excha...

Troubleshooting SMTP delivery
I've setup a Perl sendmail script on a client's web server. The server is running Microsoft Exchange. The script runs fine, and using Sendmail's log feature, I get a confirmation that the message is queued for delivery: 250 2.6.0 Queued mail for delivery But the message never makes it to its intended recipient. Where in Exchange should we be looking for the problem? (I'm the web developer and not familair with Exchange-- I'm working with the client's IT person to try to resolve the issue) Thanks, Kevin ...

Troubleshoot Performanceproblems
Hi, My E-mailsystem E2K and Outlook2002 have started to show a few performance problems. What is the best way to pinpoint this out. Sometimes a bar is coming up and tells me that data is retrieved from the exchangeserver. The servername that shows up is sometimes a mailboxserver, sometimes a GC. Any tips appreciated TIA /Mariah ...

troubleshoot views in calendar
When in Calendar view, I like to use the month, and also at the right I like to see 6 months of the small calendar, as well as the task list below that. However, when I close Outlook and then open Outlook again, the calendar view goes back to showing just the month view. It won't seem to keep the view as I set it. I've looked everywhere I can think, but can't seem to find a fix. Can someone help? ...

Process to troubleshoot Outlook hanging.
I have Outlook 2002 SP3. It frequently just hangs during send and receive. Particularly when it says it is "Selecting..." at the bottom. I use all IMAP accounts. Is there are series of step I can use to troubleshoot it? I have already ran Scanpst with no errors. Thanks, Mike ...

Distribution List Troubleshooting
I have a distribution list that I created - the list works when I type in each individual's email address separately in the "to" field. It also works when I send to each person separately. BUT when I try to use the list, only some of the addresses receive the message - others come back undeliverable host. I have deleted and recreated this list three times already, and still the same result. HELP!!!!! ...

Troubleshoot when exiting Excel.
I am working with MS Office XP 2003. I Use to convert my excel files into Pdf files using acrobat writter V5.0. I have trouble when I want to exit excel. Before, it was happening from time to time but now it has become frequent. What should I do to stop this? Hi what trouble do you have exactly=? -error message - Crash -- Regards Frank Kabel Frankfurt, Germany "Leirbag" <Leirbag@discussions.microsoft.com> schrieb im Newsbeitrag news:369F78B9-2520-4726-81A2-CCA5ED51A4B0@microsoft.com... > I am working with MS Office XP 2003. I Use to convert my excel files into Pdf >...

Troubleshooting Installation
Everytime I try to install Publisher 2003 when I click next on the window that gives you the option for a complete, typical or custom install, the window disappears. I have restarted my computer and have tried a 1000 times! Does anyone know what is wrong here?? Katie what version of Windows are you using??? Do you have a virus infection??? -- I have Windows XP Pro and I do not have a virus--I updated my definitions and scanned my computer. >-----Original Message----- >Katie what version of Windows are you using??? > >Do you have a virus infection??? > >-- > ...

Troubleshoot Rules Processing
I use 2007 now. I thought I remembered there was a way to troubleshoot a rule if it didn't work. I am using a phrase from the body of the message. I am not new to rules as I have a lot of them but running just this one rule does not move any messages to the desired folder. Is there a way to step by step walk through a rule? Thanks! -- DrDOS "DrDOS" <dcal53@comcast.net> wrote in message news:B6863E4C-B12D-48A0-8A5B-A0AB8EF81B1C@microsoft.com... >I use 2007 now. I thought I remembered there was a way to troubleshoot a > rule if it didn't wor...

troubleshooting
when ever i try to close outlook it gives me a message saying that it failed and then another message saying program ending if you end now you may lose any unsaved data. it didn't do this before. please help me so i can fix the problem thank you stacy ...

Troubleshooting a PDF conversion from Word-Mac 2008 (version 12.1.1)
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) I need to save a 51-page Word document as PDF. The result is always 2 consecutive PDF files - one 28-page file and one 23-page file. What's wrong? Yep, not an uncommon issue. See here for details and options: http://www.word.mvps.org/mac/MultiplePDFs.html Moir@officeformac.com wrote: > Version: 2008 > Operating System: Mac OS X 10.4 (Tiger) > > I need to save a 51-page Word document as PDF. The result is always 2 consecutive PDF files - one 28-page file and one 23-page file. What's wrong? > ...

Scribble troubleshooting
Got through the 5th chapter and successfully compiled Scribble and executed it. One problem I found is that it will save a zero-byte ..srb file but when the same file is loaded a white screen is shown. I can't tell if any image was actually saved or not. Or, possibly a ..srb files is not being properly loaded. I double-checked code where "save" was found and that looked okay. How do I troubleshoot this? SWDeveloper wrote: > Got through the 5th chapter and successfully compiled Scribble and > executed it. One problem I found is that it will save a zero-byte > .srb...

Outlook 2003 Troubleshooting
If I have the "ENABLE TROUBLESHOOTING" option checked in Outlook 2003, how do I make use of it, i.e. view it's contents? As always, Thanks in advance! Ivan T. Williams <anonymous@discussions.microsoft.com> wrote: > If I have the "ENABLE TROUBLESHOOTING" option checked in > Outlook 2003, how do I make use of it, i.e. view it's > contents? The logs this generates should be in %USERPROFILE%\Local Settings\Temp\Outlook Logging -- Brian Tillman ...

troubleshooting #2
Unable to access outlook functions: calender, contacts, tasks, etc.... Also, unable to sync with PDA. Once I click the Outlook icon, after some time I get the response that it is unable to locate appropriate folder, and suggests that the necessary dll.file may be missing. What might I do to resolve. Thanks State your Outlook version. State the error message more precisely. State the steps that produced your problem. -- Russ Valentine [MVP-Outlook] "Todd" <ctcouch88@cinci.rr.com> wrote in message news:2c3c01c49da0$512b0ac0$a401280a@phx.gbl... > Unable to access outl...

mail merge troubleshooting
I am looking for troubleshooting help in Publisher 2003 mail merge. We are trying to add mailing addresses to a full color 4 page 8 1/2 x 11 booklet. Original booklet file is 13 MG. 300 Addresses are in an Excel file. I have tried 2 different excel files. We got this to work once for 1/2 the list before it quit. In November I got it to work, albeit slowly, and 10 addresses at a time. I have since had the registry fix installed and double-checked by our IT dept. Since the registry fix, I cannot print the file, or pdf it with anything involving a merge. I also tried to cancel the merge. ...

Troubleshooting a non-delivery Problem
I am trying to troubleshoot an intermittant non-delivery problem to a public folder. I have a public foler that is set not to be replciated to the other exchnage servers in our exchange environment. End users create Outlook forms based messages that get sent to the public folder involved. All of the user accounts that use the forms involved are on the save server. The public folder is located on a differnt exchange server. On occassion, when submitting a message using a specific form, the user will get a delayed deliver message that is always followed a couple of days later by a non-deliver...

How to troubleshooting this issue?
Hi all, I just made an ActiveX control which consists of a lot of properties. I tested it in the Test Container, no problem at all. I can save it to a stream/storage and load back. The problem is that I can not put it in a MFC client dialog program. When I use Insert ActiveX control in the resource editor, I am able to get the control shows up. However, if I save the RC and load back, it pops up the dialog saying that "Control IDC_XXXACTIVEX2 could not reload its state from its data saved from the last time the dialog editor was used. The control will be uninitialized on the dialog."...

Troubleshooting undeliverables
Hi, We have many public folders. I'll call this one public folderA for names sake. Public folder A has 'send as' and all permissions assigned to a group of 5 people who send and receiver e-mail to this address. The address is publicfolderA@mycompany-name.com for names sake. That is not the feault address. There are other addresses and there are reasons for this. It is 70% internally used and 30% external. Someone said today that a customer has said they are getting bouce backs when e-mailing publicfolderA@mycompany-name.com I can't replicate the problem and i can't s...

troubleshooting delete
I am using version 2000 sp-3. I have transferred all my information from Netscape, I can save it and start over if that's what it takes. I did try unplugging the internet connection, restarting, no good. Also I tried your "shift delete" recommendation to someone else and it would not work. I also tried to delay the sending of any message (message is obviously not being sent anywhere but error says started transmission.) What next? Nice to mention what you did but you forgot to mention what you are trying to achieve and what your issue is. It looks like you are trying to de...

Outlook Express Troubleshooting
Hi, Recently I joined a Help Desk job. The job involves providing support to Outlook Mail Client for their ISP users. Is there any good book dedicated to Outlook Troubleshooting?. Thanks in advance Joseph "Joseph" <anonymous@discussions.microsoft.com> wrote in message news:4a4101c3e423$a3ee7d50$a601280a@phx.gbl... > Hi, > > Recently I joined a Help Desk job. The job involves > providing support to Outlook Mail Client for their ISP > users. Is there any good book dedicated to Outlook > Troubleshooting?. > > Thanks in advance > > Joseph Well ...

Rules Troubleshooting?
I am trying to create a rule that will block messages containing the Swen Virus. The way I am trying to do it is by creating a rule that checks the email after it's downloading for keywords in the subject line. However, once i've created the rule it says "you dont have appropriate permission to perform this operation" Does anyone know how to resolve this problem or what could be causing it I too have been messing around with a rule to remove these Swen virus messages. I figured out that they are either 104 or 113 KB so I set up a rule to remove e-mails of these sizes b...

RCP/HTTP troubleshooting
I used https://www.testexchangeconnectivity.com/ and received the error report following this message (failure towards the bottom). Any ideas what I configured incorrectly on SBS 2003 SP2, Dell PowerEdge, to get this error? Already followed http://technet.microsoft.com/en-us/exchange/bb123622(EXCHG.65).aspx and checked http://technet.microsoft.com/en-us/exchange/bb124175(EXCHG.65).aspx. Thanks in advance, Shem Sargent Testing RPC/HTTP connectivity RPC/HTTP test failed Test Steps Attempting to resolve the host name mail.{mydomain}.com in DNS. Host successfully resolved A...

Hotmail Troubleshoot
When I recieve large emails in my hotmail account it does not open saying that a connection the server could not be established. It states: There may be server or network problems or your timeout interval may be too short. Please search Microsoft Outlook Help for [HTTP Mail troubleshooting] PLease Help! ...