Range in Excel
Range in Excel
Range in Excel
Range in Excel
Range in Excel
Range in Excel Range in Excel Range in Excel Range in Excel Range in Excel Range in Excel Range in Excel Range in Excel
Range in Excel Range in Excel
Range in Excel
Go Back  Xtreme Visual Basic Talk > > > Range in Excel


Reply
 
Thread Tools Display Modes
  #1  
Old 07-08-2004, 08:55 AM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default Range in Excel


Hey everyone Im just trying to get the correct syntax for this code.

Code:
For i = 3 To 27 'Check to see if cell is number or not If oSheet.Cells(i, 2).numberformat = "0" Then rngXValues1 = .Range("b '" & i & "':b27") '<--Error Here rngSeries1 = .Range("a '" & i & "' :a27") Exit For End If Next i

Does anyone know how I get this to work. So if cell (3,2) is a number then the ranges will be b3:b27 and a3:a27, if its cell (4,2) it will be b4:b27 etc.
Anyone got an idea? Thanks.

Stacey
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #2  
Old 07-08-2004, 09:14 AM
herilane's Avatar
herilaneRange in Excel herilane is offline
Unashamed geek

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

Skip all the single quotes and spaces and you should be fine.
While you're debugging your code, you might want to build the string first, put it a string variable so you can check it, and then use the variable to refer to the range.
Alternatively, you can refer to a range by specifying the two corner cells using the Cells(r,c) syntax. That way all the range references will be in the same format so the code might be easier to follow.
Code:
rngXValues1 = .Range("b" & i & ":b27") 'or strAddress = "b" & i & ":b27" rngXValues1 = .Range(strAddress) 'or even rngXValues1 = .Range(.Cells(i,2), .Cells(27,2))
It also looks to me like your code is a With...End With, or the .Range should all be oSheet.Range etc.
Reply With Quote
  #3  
Old 07-08-2004, 10:40 AM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Thanks, that was def the problem
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #4  
Old 07-08-2004, 01:25 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Ok Helen (and everyone) maybe one more question I say that a lot don't I? Anywho I'm trying to apply a min and a max to the scale on the graph thats created and make it a logarithmatic scale
Code:
With ochart2.Axes(xlCategory) .MinimumScale = 2.54E-11 .MaximumScale = 2.54E-04 .Crosses = xlCustom .ReversePlotOrder = False .ScaleType = Excel.XlScaleType.xlScaleLogarithmic .DisplayUnit = xlNone End With

It doesnt even like the first line, Tells me "Minimum type on Axis not found", so I look up Axis and it said MinimumScale, so I tried that, but it didnt like that either. I'm not the best at looking thorugh the object browser yet. Any suggestions?
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #5  
Old 07-08-2004, 01:33 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

Stacey,

I'm not the *Chart Master* around here, but I'm 99% sure that you can't set the Min & Max values for a Logarithmic Chart. The Min is locked at 0 (a value that can't technically be achieved) I think. The Max is probably self scaling.

Try playing with this manually in Excel (while Macro Recording the actions) and see what happens. I'm pretty sure you will be prevented from doing this...

,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #6  
Old 07-08-2004, 01:42 PM
herilane's Avatar
herilaneRange in Excel herilane is offline
Unashamed geek

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

Mike, you really don't have much faith in Excel, do you? Sure you can! You could easily have a chart with a logarithmic scale ranging from 100 to 100,000.
Note that Excel will adjust the Min and Max values to powers of 10 (0.1, 0.000001, 10,000) unless you change the .MajorUnits property to something other than 10.

What error are you getting Stacey? Your code works fine for me.
Reply With Quote
  #7  
Old 07-08-2004, 01:45 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Ok, but even if I try to make it logrithmatic chart, it doesn't like it
Code:
With ochart.Axes(xlCategory) '.MinumumScale = 0.0000000000254 '.Crosses = xlCustom '.ReversePlotOrder = False .ScaleType = Excel.XlScaleType.xlScaleLogarithmic '.DisplayUnit = xlNone End With
Tells me "unable to set the ScaleType Property of the Axis class." Am I missing an import? I have lots
Code:
Imports Excel = Microsoft.Office.Interop.Excel Imports Microsoft.Office.Interop.Excel.XlChartLocation Imports Microsoft.Office.Interop.Excel.XlChartType Imports Microsoft.Office.Interop.Excel.XlAxisType Imports Microsoft.Office.Interop.Excel.XlBorderWeight Imports Microsoft.Office.Interop.Excel.XlLineStyle Imports Microsoft.Office.Interop.Excel.Constants Imports Microsoft.Office.Interop.Excel.XlAxisGroup
These are just the Excel ones.
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #8  
Old 07-08-2004, 01:46 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

The first code gave me the error "minimum type on axis not found"
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #9  
Old 07-08-2004, 01:48 PM
herilane's Avatar
herilaneRange in Excel herilane is offline
Unashamed geek

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

You are using a scatter graph, right?
Reply With Quote
  #10  
Old 07-08-2004, 01:52 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Yes, Im def stupid on this one, by you saying that I just realized I had that coded above where I told it to be a scatter plot. Thanks again

Edit [But it still doesnt like the .minumumscale or .minumum. It says "Public member 'MinumumScale' on type 'Axis' not found."]
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #11  
Old 07-08-2004, 01:57 PM
herilane's Avatar
herilaneRange in Excel herilane is offline
Unashamed geek

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

Check your spelling/typing... It should be MinimumScale, just like you've posted above
Reply With Quote
  #12  
Old 07-08-2004, 02:02 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

haha thanks. But the graph is still starting and ending at incorrect intervals. Ah I just thought of something, is xlCategory the X Axis and xlPrimary the Y axis?

Edit :Because although the cells are correct for the X axis, theyre all showing 0's. And its the Y axis that I want a min and max for.
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #13  
Old 07-08-2004, 02:03 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

Stacey, if you're not getting IntelliSense for the word "MinimumScale" (or whatever) it could be indicative that you need to make use of CType() around your 'ochart.Axes(xlCategory)'.

Also, are you making use of 'Option Strict On'? In which case a quigly blue line would be underlining wherever you have need for CType() as well as other issues...

-- Mike

[Edit: And sorry about the 99% thingy ealier, clearly I need to revise my confidence scale! ]
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #14  
Old 07-08-2004, 02:04 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Ya I don't use Option Strict, only Option Explicit.

haha its ok Ill forigve you
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #15  
Old 07-08-2004, 02:09 PM
herilane's Avatar
herilaneRange in Excel herilane is offline
Unashamed geek

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

xlCategory is the X axis.
xlValue is the Y axis.
Reply With Quote
  #16  
Old 07-08-2004, 02:14 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

You are a lifesaver, thanks. I really need to find a site or book that has all this info that they think that you already know but dont sort of thing...
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
Reply With Quote
  #17  
Old 07-08-2004, 02:22 PM
herilane's Avatar
herilaneRange in Excel herilane is offline
Unashamed geek

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

I'm glad we could help; come back any time.

There is a very good and cheap book for this as well... It's called the Help files You just need to get used to how they are organized, and how to search in them. Sometimes a search doesn't bring up what you'd expect, and you have to know how to rephrase things to get the results you're looking for.
Reply With Quote
  #18  
Old 07-08-2004, 02:25 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

I don't know of any .Net books for Excel, though I know that people are working on them... Ken Getz has a very nice article on Understanding the Excel Object Model from a .NET Developer's Perspective It's long, but very well written. Print it out and give it a read...

John Green / Stephen Bullen's "Excel 2002 VBA" is probably the best out there to learn about the Excel object model. It's from a VBA perspective, but that's fine. For this Chart stuff it can really be helpful to hash this stuff out in Excel VBA and then once it's working transfer the code over to .Net and then make the appropriate changes. You don't need to do this always, but in this case it probably would have helped you out. Even the Macro Recorder can be very helpful with this.

Ok, wait, I take this back: It looks like Green/Bullen have their 2003 edition out and it has a chapter on Excel in .Net. I can't say how extensive a chapter it is, but I'm sure it's a solid intro to the topic. And I assure you that the rest of the book will be outstanding.

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #19  
Old 07-08-2004, 02:51 PM
Stacey244 Stacey244 is offline
Regular
 
Join Date: Jun 2004
Location: Michigan
Posts: 74
Default

Thanks, Ill take a look at it!
__________________
~Stacey~

All the world's a stage and all the men and women merely players...
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

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
Range in Excel
Range in Excel
Range in Excel Range in Excel
Range in Excel
Range in Excel
Range in Excel Range in Excel Range in Excel Range in Excel Range in Excel Range in Excel Range in Excel
Range in Excel
Range in Excel
 
Range in Excel
Range in Excel
 
-->