Range in Excel

Hey everyone Im just trying to get the correct syntax for this 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

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

Thanks, that was def the problem :)

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

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,

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

Mike, you really don't have much faith in Excel, do you? :p 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.

Ok, but even if I try to make it logrithmatic chart, it doesn't like it

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

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.

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

You are using a scatter graph, right?

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

Check your spelling/typing... It should be MinimumScale, just like you've posted above :)

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, 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! :p]

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

haha its ok Ill forigve you ;)

xlCategory is the X axis.
xlValue is the Y axis.

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

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.

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 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/ExcelObj.asp) 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

Thanks, Ill take a look at it! :D