LastRow results in Run-time error

I�m using the following code to (hopefully) step through each populate
row of my Data Entry sheet. I am using Excel 2000. I have a problem an
questions:

-	Problem: �LastRow� is giving me a �Run-time error �438�: Objec
doesn�t support this property or method� 
-	Questions: Is the �With Sheets("Data Entry").Range("PTSource")
statement sufficient to ensure the For/Next loop steps through al
populated rows? Is the �LastRow� statement redundant or confllicting?

Thanks.

With Sheets("Data Entry").Range("PTSource")

LastRow = .UsedRange.Rows.Count

For i = 2 To LastRow

Etc

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

0
2/29/2004 3:52:33 PM
excel 39879 articles. 2 followers. Follow

3 Replies
318 Views

Similar Articles

[PageSpeed] 10

Hi Steve

Usedrange you can't use on a range.
You can use it on a sheet like this
ActiveSheet.UsedRange

Try this to loop through the rows in your named range

Sub test()
With Sheets("Data Entry").Range("PTSource")
firstrow = .Cells(1).Row
LastRow = .Rows.Count + firstrow - 1
End With
For I = firstrow To LastRow
MsgBox I
Next
End Sub


-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2000-2003)
www.rondebruin.nl



"steveski >" <<steveski.12eahj@excelforum-nospam.com> wrote in message news:steveski.12eahj@excelforum-nospam.com...
> I'm using the following code to (hopefully) step through each populated
> row of my Data Entry sheet. I am using Excel 2000. I have a problem and
> questions:
>
> - Problem: "LastRow" is giving me a "Run-time error '438': Object
> doesn't support this property or method"
> - Questions: Is the "With Sheets("Data Entry").Range("PTSource")"
> statement sufficient to ensure the For/Next loop steps through all
> populated rows? Is the "LastRow" statement redundant or confllicting?
>
> Thanks.
>
> With Sheets("Data Entry").Range("PTSource")
>
> LastRow = .UsedRange.Rows.Count
>
> For i = 2 To LastRow
>
> Etc.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
rondebruin (3790)
2/29/2004 4:14:52 PM
And just as an addendum, if the reason you are stepping through is to
potentially delete any rows at all, then always start from the bottom and work
up, not the other way round, otherwise you will hit problems.

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
news:%23Q19r8t$DHA.2512@TK2MSFTNGP11.phx.gbl...
> Hi Steve
>
> Usedrange you can't use on a range.
> You can use it on a sheet like this
> ActiveSheet.UsedRange
>
> Try this to loop through the rows in your named range
>
> Sub test()
> With Sheets("Data Entry").Range("PTSource")
> firstrow = .Cells(1).Row
> LastRow = .Rows.Count + firstrow - 1
> End With
> For I = firstrow To LastRow
> MsgBox I
> Next
> End Sub
>
>
> -- 
> Regards Ron de Bruin
> (Win XP Pro SP-1  XL2000-2003)
> www.rondebruin.nl
>
>
>
> "steveski >" <<steveski.12eahj@excelforum-nospam.com> wrote in message
news:steveski.12eahj@excelforum-nospam.com...
> > I'm using the following code to (hopefully) step through each populated
> > row of my Data Entry sheet. I am using Excel 2000. I have a problem and
> > questions:
> >
> > - Problem: "LastRow" is giving me a "Run-time error '438': Object
> > doesn't support this property or method"
> > - Questions: Is the "With Sheets("Data Entry").Range("PTSource")"
> > statement sufficient to ensure the For/Next loop steps through all
> > populated rows? Is the "LastRow" statement redundant or confllicting?
> >
> > Thanks.
> >
> > With Sheets("Data Entry").Range("PTSource")
> >
> > LastRow = .UsedRange.Rows.Count
> >
> > For i = 2 To LastRow
> >
> > Etc.
> >
> >
> > ---
> > Message posted from http://www.ExcelForum.com/
> >
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004


0
ken.wright (2489)
2/29/2004 6:04:14 PM
Example you can find on this page
http://www.rondebruin.nl/delete.htm

-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2000-2003)
www.rondebruin.nl



"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message news:%23fHfy5u$DHA.3828@TK2MSFTNGP10.phx.gbl...
> And just as an addendum, if the reason you are stepping through is to
> potentially delete any rows at all, then always start from the bottom and work
> up, not the other way round, otherwise you will hit problems.
>
> -- 
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                   Sys Spec - Win XP Pro /  XL 00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
>
>
> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
> news:%23Q19r8t$DHA.2512@TK2MSFTNGP11.phx.gbl...
> > Hi Steve
> >
> > Usedrange you can't use on a range.
> > You can use it on a sheet like this
> > ActiveSheet.UsedRange
> >
> > Try this to loop through the rows in your named range
> >
> > Sub test()
> > With Sheets("Data Entry").Range("PTSource")
> > firstrow = .Cells(1).Row
> > LastRow = .Rows.Count + firstrow - 1
> > End With
> > For I = firstrow To LastRow
> > MsgBox I
> > Next
> > End Sub
> >
> >
> > -- 
> > Regards Ron de Bruin
> > (Win XP Pro SP-1  XL2000-2003)
> > www.rondebruin.nl
> >
> >
> >
> > "steveski >" <<steveski.12eahj@excelforum-nospam.com> wrote in message
> news:steveski.12eahj@excelforum-nospam.com...
> > > I'm using the following code to (hopefully) step through each populated
> > > row of my Data Entry sheet. I am using Excel 2000. I have a problem and
> > > questions:
> > >
> > > - Problem: "LastRow" is giving me a "Run-time error '438': Object
> > > doesn't support this property or method"
> > > - Questions: Is the "With Sheets("Data Entry").Range("PTSource")"
> > > statement sufficient to ensure the For/Next loop steps through all
> > > populated rows? Is the "LastRow" statement redundant or confllicting?
> > >
> > > Thanks.
> > >
> > > With Sheets("Data Entry").Range("PTSource")
> > >
> > > LastRow = .UsedRange.Rows.Count
> > >
> > > For i = 2 To LastRow
> > >
> > > Etc.
> > >
> > >
> > > ---
> > > Message posted from http://www.ExcelForum.com/
> > >
> >
> >
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004
>
>


0
rondebruin (3790)
2/29/2004 6:15:58 PM
Reply:

Similar Artilces:

SOAP Error 04-25-05
Hi everybody. I have an error when I try to open Workflow Manager (same error in Workflow Monitor) "An error ocurred when charge the configuration of Number Format, Datetime, etc..." When I go to "http://<MyServer>/mscrmservices/bizorganization.srf" i have the next error... <SOAP:Envelope xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/"> <SOAP:Body> <SOAP:Fault> <faultcode>SOAP:Client</faultcode> <faultstring>SOAP Invalid Request</faultstring> <deta...

Pivot Table result isn't displaying all the data that it should
I have a pivot table that I use to list invoices paid on a certain date. I fill in this field in the spreadsheet, refresh the pivot table and select the date I want from the dropdown list but it doesn't always list all the invoices. Then I'll refresh it again and it displays an extra invoice or two but not all the ones it should. I've verified that I entered all the info correctly and try to refresh multiple times but it doesn't display all the data. If I delete the pivot table and recreate it then it finds all the invoices. Later, it happens again. Has this happen...

"Running Notes" in Outlook
Hi all, I am a "forced" convert to Outlook 2003 from Time & Chaos. One feature the T&C has that I can't seem to live without is the ability to take unlimited notes for a contact, task or event. For example, you simply have a Notes "page" for a contact, that looks like this: [start example] Tuesday, September 29, 1998 11:01a [IN BOLD] Office Hours: Monday & Friday 9am-5pm Tuesday-Thursday 8am-8pm Saturday 8am-2pm Tuesday, September 29, 1998 11:06a [IN BOLD] Jill took my name and said that she would call when they start offering cable modems. Tuesday, Nov...

too many recipients error
I am receiving a "Too many recipients" error when using a distribution list of 85 addresses (does not matter if I use the To:, CC:, or Bcc: field). I have contacted my ISP and they recommended reinstalling Outlook. I did that and it still does not work. Any other suggestions. I think your ISP's help desk is no help :-) Outlook does not restrict the number of recipients you can send to at one time, except perhaps if you exceed the number of characters permitted in one of the addressing fields -- but you're unlikely to do that with only 85 recipients. That message i...

Outlook error msgs & won't send or receive
I am having several issues with the Outlook 2003 that came on my new computer. Initially, I installed Outlook 2000 b/c the 2003 version is only a trial, but after trying it out, I decided I wanted to use it and would purchase it when my trial runs out. Since I started using it though, I keep getting error messages saying that there is an "extension config file error in syntax or format of file c:\Program Files\Microsoft Office\Office\Addins\fldpub.ecf" and also that "C:\Documents and Settings\Owner\Application Data\Microsoft\Addins\outlvba.dll" is not a valid Off...

Sending and Recieving Error Message
(0x800CCC92) "The Server Responded *ERR Login Failed.' I have Windows XP MSOffice and use Outlook as my primary email editor. I've been away from my usual server/service: Charter for two weeks, logged on at a different location but was not able to use outlook. Instead got all of my email through my hotmail account. Now that I'm back home I cannot send or receive any email through my "home" address and can only communicate through my hotmail account. It has been working fine until now. My last in/out was August 2. I called Charter and we walked though the setti...

Mobile Express
I was previously receiving this error when browsing accounts, but I have fixed that error but still faced with this error when browsing Opportunites. Can anyone help? The error is: System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Web.Services.Protocols.SoapException: Server was unable to process request. at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientPro...

Mail Merge Error on A2003
I am creating mail merge documents and opening them from VBA code using the code below. Set objWord = New Word.Application objWord.Visible = True objWord.Documents.Open Filename:=txtFolder & "\" & lstDocs With objWord.ActiveDocument.MailMerge If .State = wdMainAndDataSource Or .State = wdMainAndSourceAndHeader Then ... open merged document here, etc. This code works fine on A2000 but fails at the If statement on A2003. I believe this is due to extra security measures introduced by MS and is explained in this articl...

Urgent! Error when move public folder
Hi, I am migrating SBS 2003 to 2008. I got an error message "The token supply to the function is invalid. ID no: 80090308. Exchange system manager" when I move public folder. It seems related to the certificate, but don't know how to fix. My trusted certificate was original purchased for website “webmail.mycompany.com”. When I do the Internet Address migration, I accidently select "Remote" in the wizard as the domain prefix. It is in the wizard by default after click Advanced button. so my server became remote.mycompany.com. Even I imported the vali...

"Contacts Failed To Load" Error messages in Live Mail
Hello, I've recently been trying to download and install Windows Live Mail. That part was easy, however, upon opening the program, I receive error messages. The first one says, "Sorry, something unexpected happened and Windows Live Mail must close, your most recent changes may not be saved." Then another error box is displayed stating, "Contacts failed to load. Windows Live Mail is incorrectly configured, please re-install." I've uninstalled and reinstalled this program several times, but still not sure why I am receiving these errors. I did ed...

How do I set up a formula on a time sheet to calculate time in 1/.
I am setting up a time spreadsheet and I want all of my totals to be calculated in 1/4 increments. Example: IN OUT IN OUT TOTAL 7:01 AM 11:47AM 12:50 PM 4:32 PM = 8:30 hours Don't know what you total formula is, but let us say it is =(B2-A2)+(D2-C2) If you want to round this to the nearest quarter hour use =ROUND(((B2-A2)+(D2-C2))*96,0)/96 If you want to round up a quarter hour, use =ROUNDUP(((B2-A2)+(D2-C2))*96,0)/96 -- HTH RP (remove nothere from the email address if mailing direct) "gschmid" <gschmid...

ESEUTIL /P: error -1018
Today I was checking the integrity of an Exchange 5.5 SP4 database (the private store) and ESEUTIL /G returned errors -1018; so I ran ESEUTIL /P on it, but it too crashed with the same error code. How can I fully repair the database? It mounts correctly and seems to work fine, but I don't like having errors around. No online backups available (it's not my system, I was only called to move the server on new hardware). Massimo On Sat, 8 Apr 2006 20:10:38 +0200, "Massimo" <barone@mclink.it> wrote: >Today I was checking the integrity of an Exchange 5.5 SP4 dat...

Labor & OH Time Format
In the Quick MO routing setup, is the labor and overhead time format hours & minutes or hours and fractions of hours? For example, is something that takes 1 hour 30 mins entered as 1.30 or 1.5? 1.5 hours Comming to Convergence? Visit us in Booth 836. And make plans to attend one great party aboard an ocean going cruise ship. Visit our website for details and to reserve your boarding pass. http://www.AccoladePublications.com -- Richard L. Whaley Author / Consultant / MVP Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http:/...

FRX Error Message "The Fiscal Year Row Must be Provided for GL Columns"
Help! What does this error message mean? How do you fix it? Any advice will be helpful. Thanks. Sounds like the Fiscal Year cell in one of your Column Layouts is not filled in properly. -- Victoria Yudin GP MVP "Catbird" <pork@xsite.net> wrote in message news:1140738782.827093.164780@j33g2000cwa.googlegroups.com... > Help! > > What does this error message mean? How do you fix it? > > Any advice will be helpful. > > Thanks. > ...

CreateMailbox error when running from Exchange machine
Hi! I've got the following configuration: Server A: MS Server 2003 as a domain cotroller Server B: MS Server 2003 + Exchange 2003 as an Exchange server I programmatically try to create a user in the AD and inmediately tr to create an Exchange mailbox for the user: ... mailbox = (IMailboxStore)masterUser.NativeObject; mailbox.CreateMailbox(homeMDB); ... When I execute this code from the domain controller(Server A), n problem, everything works fine. But when running it from the Exchang server (Server B) it fails at the mailbox creation: "An operation error ocurred". I got no f...

Invalid Picture Error
Hi. I'm trying to install a plug in that will compare and highlight any differences between two excel spreadsheets. I have it working on 3 machines w/ Office 2003 but then on the other 2 machines I can't get it to work becasue I get: VISUAL BASIC ERROR: Invalid Picture Any ideas how I fix this? Thanks Hi Su, See Jan Karel Pieterse's suggestion in response to a similar post: http://tinyurl.com/a8pkp --- Regards, Norman "Su" <Su@discussions.microsoft.com> wrote in message news:8280B6A2-CA4D-4E96-8A36-6A424EA0AACC@microsoft.com... > Hi. > ...

Problem with running Office:Mac 2008 Installer
I'm curious about what gets installed by the Office 2008 Special Media Edition installer AFTER it runs the installer script near the end that puts icons of the applications in the Dock. My Office Mac 2008 installer crashed viciously at that point and took out the Finder with it so I had to do a hard restart to get my computer running again. (2.8 GHz Intel iMac, Tiger 10.4.11). I don't want to do a complete re-install of Office 2008 but I'm wondering if anything important might be missing. The applications all seem to work OK. When I installed Office: mac 2008, I got an error messa...

Time "and" Date for data points
I have date and time in my first column for my x axis, but I can't get both date and time to display in the chart. My data spans two weeks and now displays as I want it, but the data is useless without showing the relevant date/time for the data points (across the bottom of the chart). Is there a work around for this. I've tried different date/time cell formats which will display as I want it in the cell, but can't get the same display in the chart. I'm not sure what I'm doing wrong. Thanks for any help. Diana Cowtoon wrote: > I have date and time in my...

replace "#DIV/0!" errors with blanks #2
Does anyone know how to replace #DIV/0! errors with blanks automatically? I am working with very large data sets, and complete averages, maximums, etc when compiling the data. However, the #DIV/0! errors always affect each subsequent calculations. I have tried using the Find and Replace commands under the Edit menu, and have also attempted formulas such as: =IF(AVERAGE(A12:A19)="#DIV/0!", "", AVERAGE(a12:a19)) Both of these methods leave me with the same "#DIV/0!" as before. As you can see, this will negatively affect any calculations based on the cell...

runtime error 91 when creating FRx report in Great Plains
Not sure I'm in the right newsgroup, but couldn't find one more appropriate. I have an XP user using MS FRx and Great Plains. When she goes to create a report, the creation process seems to proceed normally, but then the report comes up blank. If she tries to print it to see what happens she gets a runtime error 91. I'm new to GP and FRx, and it doesn't look like MS provides any free assistance, so any help would be appreciated. repost to microsoft.public.greatplains Matt Parks ---------------------------------------- ---------------------------------------- On Tu...

Time recognized as a whole number?
I want to use a time value presented in hours and minutes as a number that can be used to multiply to get a value. ie. so many hours and minutes equals a number and using that number times a dollars per hour number to get a gross. Did you suddenly change your clock? See previous post. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "Bob Morrison" <morrib@chartermi.net> wrote in message news:k5vni.13$b_7.10@newsfe12.lga... >I want to use a time value pr...

Recovering emails lost as a result of removing user account in Win 98
A few weeks ago, I have made some changes to my friend's Win98 OS to d away with the login popup (where u press enter or cancel when u have t key in your username. Both will bring you to the Desktop of win98) Can't remember the details exactly, but it does include changing th Windows Family Logon or something similar, and then changing som passwords for the computer, including the screensaver, renaming th extension to the existing password for the user to .old extension. ( searched online and followed the instructions there) I didn't realise that after that, some data the user wa...

Calculate hours between to separate dates and times
I need to work out the amount of hours between a finish time on one day and the start time on the next day. In some cases the start time may not be until 2 days time. Just subtract them, =A2-A1, and format the cell as [h] (or [h]:mm if you want the mins as well) -- __________________________________ HTH Bob "Dave08" <Dave08@discussions.microsoft.com> wrote in message news:24DC1455-F22C-498E-8230-3066870EA4DD@microsoft.com... >I need to work out the amount of hours between a finish time on one day and > the start time on the next day. In some cases the start time ...

Can not run report after install frontpage server extension
Hi all, I have a problem with can not run report install frontpage server extension. The error msg display as below. System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: The permissions granted to user 'LOCUS-CRM\IUSR_LOCUS-CRM' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user 'LOCUS-CRM\IUSR_LOCUS-CRM' are insufficient for performing this operation. --- End of inner exception stack trace --- at Microsoft.ReportingServices...

Outlook 2000 generate errors in offline mode
outlook.exe generate errors only in offline mode and the application is closed. The error appear ONLY when you try to work in OFFLINE mode ! I try to apply the Microsoft Knowladge Base article - 297010 but without any success. Thanks. Marc. ...