Help on setting a range

Here is my code.

    Dim LastRow, LastCol As Long
    Dim LastCell As Range
    Dim LastCellAddress As Range
    Dim intNumCols As Long
    Dim DataCells As Range

'Find the last row, column and last cell of the worksheet.
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    'Set LastCell = ActiveSheet.Cells(LastRow, LastCol)

'Select from "A2" to the last cell.
    Range("A2").Select
    Set DataCells = Cells("A2").Resize(LastRow, LastCol).Select

I keep failing at the last line, trying to set the range variable. I
get a Type Mismatch error. I have tried everything. I don't understand
the mismatch error. DataCells is a range correct? Are my LastRow and
LastCol arguments to the Resize method good?

This should be so easy. Thanks in advance   for your help.
0
troy_lee (13)
10/7/2008 8:35:26 PM
excel 39879 articles. 2 followers. Follow

2 Replies
482 Views

Similar Articles

[PageSpeed] 53

Remove the .Select at the end.

-- 
__________________________________
HTH

Bob

<troy_lee@comcast.net> wrote in message 
news:1f79c55d-05fa-4a07-8502-4987c96a410e@b30g2000prf.googlegroups.com...
> Here is my code.
>
>    Dim LastRow, LastCol As Long
>    Dim LastCell As Range
>    Dim LastCellAddress As Range
>    Dim intNumCols As Long
>    Dim DataCells As Range
>
> 'Find the last row, column and last cell of the worksheet.
>    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
>    LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
>    'Set LastCell = ActiveSheet.Cells(LastRow, LastCol)
>
> 'Select from "A2" to the last cell.
>    Range("A2").Select
>    Set DataCells = Cells("A2").Resize(LastRow, LastCol).Select
>
> I keep failing at the last line, trying to set the range variable. I
> get a Type Mismatch error. I have tried everything. I don't understand
> the mismatch error. DataCells is a range correct? Are my LastRow and
> LastCol arguments to the Resize method good?
>
> This should be so easy. Thanks in advance   for your help. 


0
BobNGs (423)
10/7/2008 9:14:35 PM
I tried posting a response last night, but for some reason it didn't get 
thru.  Here it is again:

Hi Troy

You need Range() not Cells() in the last line, also you don't want to Select
when you are setting a range reference.  Your resize needs to be modified
too, otherwise it will include an extra line at the bottom:

Set DataCells = Range("A2").Resize(LastRow - 1,LastCol)


Richard



<troy_lee@comcast.net> wrote in message 
news:1f79c55d-05fa-4a07-8502-4987c96a410e@b30g2000prf.googlegroups.com...
> Here is my code.
>
>    Dim LastRow, LastCol As Long
>    Dim LastCell As Range
>    Dim LastCellAddress As Range
>    Dim intNumCols As Long
>    Dim DataCells As Range
>
> 'Find the last row, column and last cell of the worksheet.
>    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
>    LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
>    'Set LastCell = ActiveSheet.Cells(LastRow, LastCol)
>
> 'Select from "A2" to the last cell.
>    Range("A2").Select
>    Set DataCells = Cells("A2").Resize(LastRow, LastCol).Select
>
> I keep failing at the last line, trying to set the range variable. I
> get a Type Mismatch error. I have tried everything. I don't understand
> the mismatch error. DataCells is a range correct? Are my LastRow and
> LastCol arguments to the Resize method good?
>
> This should be so easy. Thanks in advance   for your help. 

0
10/8/2008 7:00:11 AM
Reply:

Similar Artilces:

Routing Engine not starting, please help ASAP
Exchange and Windows 2003 I cannot get the Exchange Routing engine to start up. Upon startup I get an event ID 7023 telling me that the path is not found. Everything else works and starts up just fine, people can open mailboxes, but cannot send/receive emails. I read online that it sounds like the metabase.bin file is corrupted. I uninstalled/reinstalled IIS, reinstalled Exchange 2003, and have reinstalled Exchange 2003 SP1, everything works except that I still cannot get the routing engine to kick back on. Any suggestions! Thanks! Was this an OEM preinstall? I recently bought a ser...

Help Microsoft Office 2003
After now using Microsoft Office 2003 for about 3 months now, have suddenly noticed that my email messages have just started showing strange symbols for certain punctuation. This does not show as I type... but the recipent sees these symbols when the message is received. I can see it when they reply back. The strange symbols show for the apostrophe and sometimes for the exclaimation. I do not recognize the symbols. I do not have caps lock on, do have number lock on. None of the symbols show on my keys. Help?! Thanks! Kim it's caused by character encoding. What email client and...

.pst folder help
How do I put a .pst folder in My Documents for constant up to date copying? Mine seems to be in a folder called Office Data File which I cannot open but do not see any pst file...can someone kindly help me out here? Thanks! Right click the pst in outlook and select properties and then click Advanced You will see the location of the file. Depending on your view settings it may be hidden. Close the pst, (right click it, close) Move the file to the location you desire. Open Outlook select File - Open - Outlook Data File Browse to the file and select it. OK Done Regards Mark Dormer &quo...

Specific Age Query for a Date Range
Table = Personal Field = Birthdate I need to create a query to show who is of a certain age for January 1, 2008 to December 31, 2008. For example, I need to know who will be 50, 60, 65, 70 and 75. I would like to create the query so that when I click the query to open it, I have to input the age. I've done other queries to show who is of a specific age as of today: AgeYears: DateDiff("yyyy",[Birthdate],Date())-IFF(Format([Birthdate],"mmdd")>Format(Date(),"mmdd",1,0), with criteria of [Age in Years] ... but I cannot figure out how to pull a report...

Help with macro to choose printer
Hi, I am new to macros in Excel and this is what I did to print a worksheet: ------------------------------------------------------------------------ Range("A1:W35").Select ActiveSheet.PageSetup.PrintArea = "$A$1:$W$35" Selection.PrintOut From:=1, To:=1, Copies:=1, Collate:=True End Sub ------------------------------------------------------------------------ Works great except that I want to select the printer to use and the macro won't stop at the point to choose printer. Any way to work around this? Any help is greatly appreciated. Emilio Emilio,this wil...

Help with an expression to calculate a profit from one of three fi
Using Access 2003 SP3 - in Forms I know I'm probably asking a very complex question, so a very big THANK YOU to whomever can help. I am doing a detailed inventory and need help with an expression in my form that will calculate my profit based on one of three fields. Only one field would have the end data. To clarify what I am working with: Most stock items come by the "unit" and contain multiple "subunits" which, in turn, contain smaller "pieces." However, some "units" only come with "subunits" and some only come as &quo...

Setting up mailbox Resources
We are running Exchange 2003 Standard. Our users mailboxes were setup via active directory. We want to set up our mailbox resources so that: - Conference Rooms, Projectors, Laptops etc can be booked using the meeting resources selection. - resource mailboxes automatically accept meeting requests and process cancellations - The Free/busy options are viewable by users. Currently we have not been able to find any information that indicates how this is to be done. From Outlook web we can clearly see that a resource option exits. Any help would be appreciated here Thanks See here: http...

Help with Lookup #2
I have an excel worksheet that has approximately 20,000+ records. I would like to classify these recorded based on two columns. The first column's name is "YB Description" and the other is "Sold Date". The YB column mainly contains 4 digit numerical values, with approximately 300-400 records that are text, ie. "classified", "not verified", "public use". The Sold Date column is pretty self-explanatory, all the records are dates with the following formatting - M/D/Y. What I would like to do is create a third column called YB/Sold that classi...

Saving Attachments Default Setting
Whenever I save attachments to emails in Outlook Express it always defaults to My Documents File. This is very tiresome as this is not the file I want to use. Everytime I save attachements I have to browse for the correct file. I am sure it used to remember the file you last used and default to that but since I installed an update a couple of months ago it just goes back to My Documents. Help! ...

Help with ACT! to CRM migration
Hi to anyone that can help. I am trying to do a trial data migration from our ACT! database to MSCRM to see what it will look like. I have installed the migration framework and appear to have CRM running fine on a server in our network (no errors on install and all windows appear ok). Since I do not have a strong database background I was wondering if someone could confirm what I believe is MS's way of migrating my data accross. I have exported the data from ACT! to a text file. I imported this into excel and repleaced the Free/busy data with a unique number for each record. I the...

Using VBA to find a value and select a range
I am trying to write a macro that will let me find a specific vale in a specific column, then select part of the row that the value is in, beginnign with a column 31 cells away from the column that the specific value is in. Someone suggested to me that I use an IF statement, but this is all I know about that: IF [value in column B] is “Grand Total” Then select from column AF to the end of the array in that row and copy it and paste a transpose of the values starting in cell I9 of worksheet X. What is the best way for me to accomplish this goal? Does this macro do what you...

Help with Links to Files
Hello, I have a file in Word 97-2003 compatibility mode. When I select the Office Button / Prepare I see an option to Edit Links to Files. If I convert the file to 2007 format the option for Edit Links to Files is no longer there. Where did they go? thanks, mitch This is strange. The Edit Links to Files option should be available even if you save in Word 2007 format. Are you sure you didn't inadvertently break the link to the automation object? You could press Alt+F9 to see that the field code for the object is still intact. (Note that you can only see the field co...

Need help backing up OL2002 items (or folders)
HELP says: "If you want to create a folder to which you can drag items for safekeeping or to distribute to another computer. Create a new data file (.pst) and drag items to it. If you used the default name for the folder when you created the data file, the folder will be called Personal Folders . In the Folder List (Folder List: Displays the folders available in your mailbox. To view subfolders, click the plus sign (+) next to the folder. If the Folder List is not visible, on the View menu, click Folder List.), drag any item from your current folders to the new folder. Press CTRL...

outlook settings #3
I have three email accounts (myself, my wife, and daughter) whenever an email comes in regardless of the name it was sent to, all three accounts recieve the email. My internet provider has checked all there stuff and tell me it is an issue with the software. I have no idea how to reconfigure my outlook program to eliminate this problem. ...

Copy and display a range
Is it possible to have the contents of a named range copied and pasted into a new location. Ideally the ranges would be on sheet 2, and on sheet 1 there would be a selection cell. When a range name was selected from a drop-down list the contents of that range would be retrieved from sheet 2 and displayed on sheet 1. Thanks. Hi if your selection dropdown is in cell A1 you could use (for a single cell): =INDIRECT(A1) -- Regards Frank Kabel Frankfurt, Germany "Ken G." <KenG@discussions.microsoft.com> schrieb im Newsbeitrag news:13AB03A1-AE1A-4D8C-AB5E-9A545DFEF1D3@microsof...

set value in subform
I want to use the set value macro to set a control in a subform based on a control in another form. I have three forms: FormA, FormB, FormC. FormC is a subform within FormB. What I want is to be able to enter a value in FormA and then have FormC automatically updated. Currently, if I open FormA and FormC I am able to use the SetValue macro to do this. However, if I open FormA and FormB (recall that FormC is a subform within FormB) and try to run the macro, Access gives me an error. I'm assuming that it is because Access does not think FormC is open (even though it is op...

password
Hi I have problem with Microsoft Outlook XP. Very often Outlook won't remember the password. Thx I am having a similar problem. Using Outlook 2002 (XP). I have several POP3 accounts, and Outlook requires the passwords every time. I've tried everything, and it still forgets them. Thanks to anyone that can help. >-----Original Message----- >Hi >I have problem with Microsoft Outlook XP. Very often >Outlook won't remember the password. > >Thx >. > Earlier reply had the incorrect e-mail address. This address is correct. Jim V >-----Original Mess...

Help w/ Accounts
Hi, I have Outlook 2003, I used to have four email accounts in Outlook. One of those accounts was set up to check the email but leave the original in the server. It was working fine until all of a sudden it started downloading the same email twice and deleting it from the server. I deleted the account from Outlook, but it is still downloading the email from that account. I went to Email accounts and I don't see that particular email account listed there. Does anyone have any idea how to get rid of this "invisible" email account that does not show up in the email accounts an...

Need help writing an expression to find a Quarterbacks Passer Rati
I'm trying (without success) to write a formula to use in a table to figure the NFL quarterbacks passer rating. Here are the components, and I believe my errors are the maximum and minimum and not knowing how to use them in the formula: C= Comp/Att*100-30/20 Y= YDS/Att-3*.25 T= TD/Att*20 I= 2.375-INT/Att*25 The four components are then added and divided by 6 and multiplied by 100, so the formula for passer rating is given as: (max(min(C,2.375),0)+max(min(Y,2.375),0)+max(min(T,2.375),0)+max(min(I,2.375),0)*100/6 I have played for 3 days with this monster formula and have not figured ...

Re-set a date to the first of the month
Hello, I need to re-set whatever date is entered in a cell to the first of that month. For example if someone enters 9/11/2008, I need it to be re-set to 9/1/2008. Is this possible, and if so, can you please tell me how to do it?? thanks in advance! You could use a formula in another cell: =date(year(a1),month(a1),1) or =a1-day(a1)+1 Then copy|paste special|values over the original range (a column???) OS wrote: > > Hello, > > I need to re-set whatever date is entered in a cell to the first of > that month. For example if someone enters 9/11/2008, I need it to be > re...

Need help getting data from Word to Excel
I'm novice level with Word and Excel, so excuse what may be a basic question/problem that I've got. I asked an employee to enter lots of data into Excel. The data is company name, address, contact name, and phone number. I requested that each type of data be entered into different columns. Instead, the employee used MS Word 2003. To compound the issue, the employee did not use commas, tabs, or any other delimiter or formatting method that might allow me easily to get the data over into Excel (by *easily* I mean using a technique that I know ; ) The data in Word simply is s...

Linking...HELP (accidentally posted on Excel Programming)
I'm trying to change the source on a link. Every time I do it, however my system locks up. I actually have 4 workbooks that are linked up to one workbook. I hav to do the same thing three times, but the workbooks have to hav different names to them: ie: folder x -> worbookA WorkbookB workbookC workbookD BIGWORKBOOK new to use the exact information as above, but I have to have it in difference folder with difference names... folder y -> worbook1 Workbook2 workbook3 workbook4 SecondBIGWORKBOOK I've used the Edit-> Links-> change source (but that's where my syste loc...

If Then Conditional Help
I am fairly new at working with code. I am trying to write a statement that will open one report if a feild is true, and another if it is False. This is what I have so far Dim stDocName As String Dim strCriteria As String strCriteria = "[Event Name]='" & Me![Event Name] & "'" If ([Cash Calculations].[NewEvent] = Yes) Then DoCmd.OpenReport NewEvntCashCalc, acViewPreview, , strCriteria Else DoCmd.OpenReport PrevEvntCashCalcu, acPreview, , stCriteria End If When I try to run this code I get the Error Message ...

Help with formula #10
Is there a way to count how many days a specific number has been entered. Example: Column A Column B Part Number Date 12345 3-Oct-4 12345 3-Oct-4 99999 3-Oct-4 12345 7-Oct-4 99999 8-Oct-4 For part number 12345 answer would be from todays date to 7-Oct-4 I think you could use: =COUNTIF(A:A,"12345") HTH Jason Atlanta, GA >-----Original Message----- >Is there a way to count how many days a specific number >has been entered. Example: >Column A Column B >Part Number Date >12345 3-Oct-4 >12345 3-Oc...

help
where do i get my user ID and password for my 90 day trial I would call MS at 800-456-0025. -- Charles Allen, MVP "jamieg" wrote: > where do i get my user ID and password for my 90 day trial ...