Referring to Cells in as sheet
Referring to Cells in as sheet
Referring to Cells in as sheet
Referring to Cells in as sheet
Referring to Cells in as sheet
Referring to Cells in as sheet Referring to Cells in as sheet Referring to Cells in as sheet Referring to Cells in as sheet Referring to Cells in as sheet Referring to Cells in as sheet Referring to Cells in as sheet Referring to Cells in as sheet
Referring to Cells in as sheet Referring to Cells in as sheet
Referring to Cells in as sheet
Go Back  Xtreme Visual Basic Talk > > > Referring to Cells in as sheet


Reply
 
Thread Tools Display Modes
  #1  
Old 01-30-2004, 11:41 AM
joek joek is offline
Freshman
 
Join Date: Sep 2003
Location: Tucson, AZ
Posts: 40
Default Referring to Cells in as sheet


In the sample code below, I can fill cells using row,column notation

Dim ws As Excel.Worksheet = _
DirectCast(ThisWorkbook.ActiveSheet, Excel.Worksheet)

Dim i As Integer
For i = 1 To 20
ws.Cells(i, 2) = i
Next i

But to access the contents of cells, the row, column notation seems to be invalid. The following statement is not valid:

dim string1 as string
string1 = ws.Cells(5, 6)

instead, we have to use A1, A2 ... etc notation such as:

string1 = ws.Range("E5").Text

Is there a way to get away from the A1, A2 ... notation?

Thanks
Joe K
Reply With Quote
  #2  
Old 01-30-2004, 12:02 PM
herilane's Avatar
herilaneReferring to Cells in as sheet herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

You'll need to specify which property of Cells you're trying to get. Try
Code:
string1 = ws.Cells(5, 6).Value
(and you should really do the same when setting the value)
Reply With Quote
  #3  
Old 01-30-2004, 12:07 PM
joek joek is offline
Freshman
 
Join Date: Sep 2003
Location: Tucson, AZ
Posts: 40
Default

Quote:
Originally Posted by herilane
You'll need to specify which property of Cells you're trying to get. Try
Code:
string1 = ws.Cells(5, 6).Value
(and you should really do the same when setting the value)



That does not work. ws.cells() has no property named value
Reply With Quote
  #4  
Old 01-30-2004, 12:34 PM
herilane's Avatar
herilaneReferring to Cells in as sheet herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

Try this:
Code:
Dim rng As Excel.Range rng = ws.Cells(5, 6) string1 = rng.Value
Reply With Quote
  #5  
Old 01-30-2004, 12:49 PM
joek joek is offline
Freshman
 
Join Date: Sep 2003
Location: Tucson, AZ
Posts: 40
Default

Thanks HeriLane, that works for me!
Joe K
Reply With Quote
  #6  
Old 01-30-2004, 01:05 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey, glad you're working!

Being .Net, you might want to take the *extraordinary* step of not double-referencing on one line, to prevent Excel from "Hanging" when you try to exit.

I would use the following:
Code:
Dim oRng As Excel.Range Dim oCells As Excel.Range oCells = WS.Cells oRng = oCells.Item(5,6) string1 = CStr(oRng.Value)
Breaking up the usage of WS.Cells.Item(Row,Col) is not required in old-style VB 6.0 Automation, but is for .Net. (I added use of Cstr() as well in the last line, in case you are using 'Option Strict'.)

Joe, for more on this nonsense, you can read the tutorial above: VB.Net Office Integration FAQ

-- Mike

[Edit: Oops, sorry about the typo. 3rd line now correctly reads 'Rng = Rng.Item(5,6)' Sorry about before. -- Mike]
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Timbo; 02-03-2004 at 12:01 AM. Reason: Alteration on request
Reply With Quote
  #7  
Old 01-30-2004, 01:26 PM
joek joek is offline
Freshman
 
Join Date: Sep 2003
Location: Tucson, AZ
Posts: 40
Default

Thanks Mike
Joe
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Stop cells from calculating to speed up sheet. mauddib Excel 4 01-13-2004 09:27 AM
Conditional formatting can't reference cells in different sheet? masochistsr Excel 4 07-18-2003 12:27 PM
VB 6.0 Sheet and cell protection aragnaz Excel 1 07-16-2003 12:18 PM
copying data in cells located in another sheet greghm Excel 2 07-03-2003 04:07 AM
macro to look in another sheet HUBERT Excel 0 06-02-2003 06:51 PM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Referring to Cells in as sheet
Referring to Cells in as sheet
Referring to Cells in as sheet Referring to Cells in as sheet
Referring to Cells in as sheet
Referring to Cells in as sheet
Referring to Cells in as sheet Referring to Cells in as sheet Referring to Cells in as sheet Referring to Cells in as sheet Referring to Cells in as sheet Referring to Cells in as sheet Referring to Cells in as sheet
Referring to Cells in as sheet
Referring to Cells in as sheet
 
Referring to Cells in as sheet
Referring to Cells in as sheet
 
-->