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
711 Views

Similar Articles

[PageSpeed] 11

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 incoming mail messages
I'm running exchange 2003 enterprise with all up to date service packs. I have a client who sends emails to me and I don't get them. I send to them and they get them fine. I've tried with various users and the same thing. Don't have anyone else with similar issue. how can I verify that mail they are send is getting to my mail server? I've had them from their mail server (smtp sending gateway) telnet to our server on port 25 and all is good. I have their domian name in our whitelist for our symantec mail-security I look at the logs to see if it is being quarentee...

Troubleshooting Data Import from Excel File
Hello all! I am trying to determine why my data import into excel 2002 is not working correctly. I have multiple Excel sheets that I am going to have feeding into one primary one. One sheet I have imported correctly; although its formatting got shot to hell in the process, it is still usable and workable. However, I run into problems with a second sheet. When I do the data import from the other excel file, I can see the titles for both columns and rows, but no numerical data. This happens with another worksheet from the same workbook. In the excel workbook that I am trying to import fro...

Troubleshooting
When clicking on "Restore my Active Desktop" the following error message appears RUNDLL ERROR LOADING c:\windows\azehobek.dll The specified module could not be found How does your question pertain to Windows Update? I think you're seeing the effects of a hijackware infection. -- ~Robear Dyer (PA Bear) MS MVP-IE, Mail, Security, Windows Client - since 2002 Roy Michaelson wrote: > When clicking on "Restore my Active Desktop" > the following error message appears > RUNDLL > ERROR LOADING c:\windows\azehobek.dll > The specified module co...

TROUBLESHOOT: SECONDARY AXIS v.07
sequence: highlight chart | format selection | series options | 'Plot Series on' - that last bit does not appear in the menu (it just shows Gap Depth/Width instructions). I want to add a vertical secondary axis showing percentages, would this be an add-on? Thank you Hi, Sounds like you have a 3d chart type, which does not allow 2 axis. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Shevvie" <Shevvie@discussions.microsoft.com> wrote in message news:3E008C2A-22E7-4B67-B5B9-EF919DA45CA6@microsoft.com... > sequence: highlight chart ...

troubleshooting new page in access 2003
I am printting a 4 group level where for every change in the 2 first groupes they should be a new page. What happens is when the first group is changing I had a page with only the title of the group and remaining empty ??? kalman wrote: >I am printting a 4 group level where for every change in the 2 first >groupes they should be a new page. What happens is when the first >group is changing I had a page with only the title of the group and >remaining empty ??? Set the top level group header section's ForceNewPage property to BeforeSection. Use code in the top level group h...

Troubleshooting "Rules"
Hi, I am trying to cope with spam by creating rules from messages in my Junk folder. I am hilighting the message and then selecting either the "From" option or the "Subject" option. All seems to go well up to that point. However . . . After clicking OK and then selecting "run this rule in this folder now", the only message that is deleted is the one that I am using to create the rule, instead of going through the whole Junk folder, as expected. Another problem that I am having is with the Search funciton. Again in my failaing efforts to cope with over...

3.5" floppy disk troubleshooting
my problem is with my floppy disk. i'm unable to open my disk in ms word computers at local libraries i've been using recently. these two messages pop up everytime i try to open my disk; "a: / ' is not recognized. it may not be formatted". and " the floppy disk in drive A: is not formatted or has been formatted for a Macintosh". On 28/5/04 1:42 am, in article 13d8d01c4444c$99470c30$a001280a@phx.gbl, "don wilkey" <anonymous@discussions.microsoft.com> wrote: > my problem is with my floppy disk. i'm unable to open my > disk in ms w...

Troubleshoot Outlook calendar update/deletion running Exchange 2007 SP1
Our clients are Office 2003 Pro SP3, server is Exchange 2007 SP1. From time to time, we have complains that deleted meetings don't get to the invitee's calendar, or it's showing updated instead of removing from the calendar. How can I investigate this? I'd assume this has to do with Outlook more than on the Exchange server? Some users have Blackberries and we have BES running, much appreciate your help. ...

IM troubleshooting
Hi We are running on Ex2k IM for quite some time now with latest clients, XP and service packs on the server and clients. One thing I notice recently is that when I logged on with both .NET passport account (xxx@company.com) and Exchange Instant Messaging account (xxx@im.company.com) The Exchange account can't communicate with some of the users. We had to rely on .NET account to communicate, which makes our Exchange IM infrastructure meaningless. I don't have the authentication problem, but it keeps telling me the message can't be delivered when using purely Exchange IM accoun...

how to troubleshoot poor graphics?
Can you be a bit more specific? If all your graphics are poor, upgrade your video card driver... -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "amit" <amit@discussions.microsoft.com> wrote in message news:B3A58E64-DBCB-4122-BE9B-5DDA14F5163E@microsoft.com... > ...

exchange troubleshooting resources
can you please let me know resources for troubleshotting exchange servers . Start here http://support.microsoft.com/default.aspx?scid=fh;EN-US;KBHOWTO and here http://groups.google.com/advanced_group_search?hl=en anil reddy wrote: > can you please let me know resources for troubleshotting exchange servers . ...

Systematic troubleshooting tool/method?
I am looking for a systematic troubleshooting system for WindowsXP. I am specifically looking for a way to test the integrity of the OS and be able to isolate faults to specific subsystems. I am looking for both an automated tool and a logical procedure. Can anyone direct me to books, articles or tools that will do this? Thank you, Mike B. I would start with Google. --- Leonard Grey Errare humanum est On 12/22/2009 4:42 PM, Mike Burke wrote: > I am looking for a systematic troubleshooting system for WindowsXP. > > I am specifically looking for a way to te...

troubleshooting the release version
Can someone give me a tip on how to troubleshoot an access violation that occurs in the release exe but not in the debug version? I'd like to add that the exception I get with the release version does not occur in the development environment -- only when I open the exe separately, say from within Windows Explorer. I turned on debugger info for the release configuration, but I get no indications of any trouble. -------------------- "Steve Russell" <srussell@removethisinnernet.net> wrote in message news:OWtqGagjEHA.2812@tk2msftngp13.phx.gbl... > Can someone give me a t...

How to troubleshoot Excel spreadsheet printing
I downloaded an Excel self calculating invoice template and it saves OK, it changes OK, it goes to preview perfect, but will not print on my printer? Did I save it incorrecly (to Excel workbook), I did a repair run on Office, I am stymied and need a hand. Try the Control Panel / hardware devices / go to your printer and run troubleshoot from there. Possibly not an EXCEL problem at all. Please hit yes if my comments have helped. Thanks. "koegg58" wrote: > I downloaded an Excel self calculating invoice template and it saves OK, it > changes OK, it g...

can't update or download troubleshooter
Can't update windows can't download troubleshooter think I've made a mistake buying windows 7 laptop should have stuck with XP any suggestion please -- Allly55 "Allly55" <Allly55@discussions.microsoft.com> wrote in message news:9D84C2D0-2E4B-41E6-A180-A6F7B3343E13@microsoft.com... > Can't update windows can't download troubleshooter think I've made a > mistake > buying windows 7 laptop should have stuck with XP any suggestion please > -- > Allly55 Yes. You should seek help with your Windows 7/ laptop issue either throu...

Troubleshooting shared workbook in Excel 2007
I have a shared workbook upgraded from Excel 2003 to Excel 2007 (saved as 2007 format). Both run in an ASP environment. The Excel 2003 OS was Windows 2003 server while Excel 2007 is running on Windows 2008 server. The workbook itself is about pipeline data and each salesman updates his rows. The registration worksheet as filter enabled so that each salesman can see only his rows, sort them as he pleases and so on. The sorting possibilities in filter in 2007 are much better than in 2003 (even in shared mode). The problem after upgrading is that the rows keep mixing together, t...

How do I troubleshoot open proxy?
Our email server ip is blocked by some site for "open proxy" issue? How do i troubleshoot this issue? THanks Do you mean open relay? You could telnet to your server on smtp port and send a test message to an external recipient. If it's not an open relay, you should get the following error on RCPT TO: 550 5.7.1 Unable to relay for foo@foo.com You can also use the web-based tool at: http://www.abuse.net/relay.html Exchange 200x default settings prevent anonymous users from relaying. Settings are in SMTP virtual server properties | Access tab | Relay. Default is "Only ...

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 NDR
How can I troubleshoot an NDR for e-mail bound for an e-mail enabled public folder. The outside (one of our customer) sender, received following NDR. The message reached the recipient's e-mail system, but delivery was refused. Attempt to resend the message. If it still fails, contact your system administrator. <***.****.com #5.2.1> The event ID logged for this message is 1030 (SMTP NDR All; SMTP: Non-Delivered Report (NDR) Generated) in message tracking log. This is one time occurance and we don't see the problem any more. Does any one know what could be the ...

AutoCalculate TroubleShooting
I have a workbook with 16 worksheets. There are various links between each spreadsheet. The problem is that the workbook will not auto calculate when a cell changes. The "CALCULATE" notification stays on the bottom status bar and doesn't go away. Autocalculate is Turned on. There is no external links. There is no circular references. Does anyone have any similar experiences? maybe it's this: http://support.microsoft.com/default.aspx?scid=243495 XL: Calculate Message Remains in Status Bar If 65,536 Formula References Mark Vandenberk wrote: > > I have a workbook ...

Money 2007 troubleshooting
When I run the program a message appears "Not enough memory is available. If you have several applications open at the same time, you might need to close one of them before starting Money." What shall I do I can't open my file and of course I don't have other programs running. ...

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...

Troubleshooting DL membership
Hi guys, we have a user in a DL that doesn't receive mail sent to the DL. Other 1200+ users receive correctly the mail, but this one nothing. I've tried to remove/re-insert the user, but nothing. Someone can help us?? Thx -- Fabio Ciprandi System & Exchange Engineer On Mon, 26 Sep 2005 08:06:02 -0700, "Fabio Ciprandi" <FabioCiprandi@discussions.microsoft.com> wrote: >Hi guys, > >we have a user in a DL that doesn't receive mail sent to the DL. > >Other 1200+ users receive correctly the mail, but this one nothing. I've >tried to rem...

Troubleshooting distance from text
I have used: format picture > layout > square > both sides > distance from text is 0.3 and saved changes. But when I open the document again it has changed back to "automatic" 0.1 distance from text. How do I make sure it stays on 0.3? Using Publisher 2002 on Windows 2000. I can't duplicate your issue. Are you using .wmf images or a bitmap? What happens if you delete the image and insert a fresh copy? Are you using a pre-designed template? Are all your publications behaving the same way? Have you tried detect and repair? -- Mary Sauer MSFT MVP http://offi...

Need advice to troubleshoot a computer
My parent's computer has stopped working. It is on, but is not doing anything (the monitor is not receiving a signal). It will not reboot or even shut down via the buttons on the front. I need to figure out what the problem is and whether it is fixable. I was planning to redo their hard drive soon as it was orginally partitioned into 4 sections of 10GB each and the C drive is basically full while the other drives are not. I have already transferred as much as possible to the other drives, but it is now full, mostly with windows files and things i am not sure I can move. My plan...