Excel help

Nice. Before long you'll be doing 3D graphics:

http://forum.beyond3d.com/showthread.php?t=47168



Jawed

 

Truly LOL!

 

Range command or similar

Ok I have a dumb question, but I am having difficulty with it.

Code:

WS_output.Cells(y, 2) = Application.Average(WS_output.Range("B2:B101"))

I am using that for now and it works, but I need it to vary in response to a variable instead. So I would like and need

WS_output.Cells(y, 2) = Application.Average(WS_output.Range(2,2:y-1,2)

It doesn't work though. I have searched and looked in help, but it was not clear if there is another way to do this or how to enter it properly. I tried a couple variations and always got different errors.

 

range(cells(2, 2), cells(y-1, 2)) should work.

Jawed

 

Interestingly enough that does not work either, though it was one of the variations I had not tried.

It says

The other stuff I tried was using brackets around the cell references, range([2, 2],[y-1, 2]) like that and a few others I forget. I talked to a guy who used to work at MS and he said the range function was some sort of legacy thing, but there was a way to do what I wanted, but he forgot. Does that imply that there is a new function to do it? Would the "cells" thing work? I could do it manually by simply summing the numbers in a loop and then dividing by countif they are not blank, but that is rather inelegant, though it would work. I hate endless loops that can be avoided by one line.

 

Sorry, that was incorrect object hierarchy usage, you actually want:

Code:

Application.Average(Range(WS_output.Cells(2, 2), WS_output.Cells(y-1, 2)))

Range needs valid range objects and Cells(y,2) is homeless without a sheet. I can't work out what the other person told you as ranges are totally fundamental to Excel VBA!

Also, bear in mind that you can put formulae into cells. Though, ahem, you start getting into using the worksheet function Address, judging from the help, in order to use numeric cell addresses rather than "B2" style addresses - haven't tried it.

It's funny, it's 7 years or more since I last did Excel VBA seriously

Jawed

 

Thank you kind sir it works. I wish Excel VBA help was like MATLAB help where they gave examples of the code as well so I could have figured that out myself. I got the macro and ribbon stuff all worked out now so I think bearing any unforseen circumstances I have nothing but time standing in the way of finishing it.

http://www.xtremevbtalk.com/showthread.php?t=265636
That had pretty good stuff on integrating into the ribbon if anyone else cares.

 

you can use the record macro button the do what you want manually and it will ouput vba code of your actions

 

Really, no examples in the help? I'm using Excel 2002 and it was an example that steered me to fixing your code.

Jawed

 

It may well be that I did not know what to look up in help. When I looked up range I got this example, but it only showed me what I already knew worked. So I should not say there are no examples, it just seems in Matlab there are quite a few examples usually so one can see how to use the function in a multiple of ways. The later one gave me some ideas, but I never thought that I needed to put the reference sheet into the inside of range to select it with variables.

Code:

Sub Random()
    Dim myRange As Range
    Set myRange = Worksheets("Sheet1").Range("A1:D5")
    myRange.Formula = "=RAND()"
    myRange.Font.Bold = True
End Sub 

Code:

Reference 		Meaning 
Range("A1") 		Cell A1 
Range("A1:B5") 		Cells A1 through B5 
Range("C5:D9,G9:H16") 	A multiple-area selection 
Range("A:A") 		Column A 
Range("1:1") 		Row 1 
Range("A:C") 		Columns A through C 
Range("1:5") 		Rows 1 through 5 
Range("1:1,3:3,8:8") 	Rows 1, 3, and 8 
Range("A:A,C:C,F:F") 	Columns A, C, and F 

davros I know you can record macros, but I think it is easier sometimes just to write them as then you know what the code does and if you need small tweaks to make it work on other data you can.

 

true but if you dont know the code its a way of finding out

 

If you want Excel VBA code examples, then look no further than Chip Pearson's website: http://www.cpearson.com/excel/MainPage.aspx. That boy knows his stuff.

 

mcsven I had him bookmarked along with several other websites I found quite useful. Maybe I should link them on this thread later so others can find the pages though mine were biased toward what I wanted to do.

 

Sxotty - sounds like you've got the right references!

I didn't get chance to post at the right time, but if your transition from 12 to >12 takes place only once within a given dataset you can accomplish your goal using a formula:

=IF(AND(ISERR(MATCH(12,myRange,0))=FALSE,INDEX(myRange,MATCH(12,myRange,0)+1)>12),OFFSET($A$1,MATCH(12,myRange,0),1),"No match")

where myRange is the data containing the transition, it starts from cell A2 and the lookup data is in column "B".

Incidentally - unless you want to distribute your code to someone else, I wouldn't bother with saving it as an add-in (.xla). Just save it in your personal.xls file.

 

It occurs repeatedly, and the code is fast enough that I don't mind if it isn't efficient (satisficing behavior ).

I don't know enough about how this stuff works to know if a xlam or personal xls file is best, but I have about 200 files I have to run through, so I just open the file, click the button on the ribbon and I am done. I know I could automate it to some extent, but unfortunately it is complicated by non-uniform naming and even format in some cases.

I do have one question though, it isn't necessary for me to get this done, but I am curious.

So the function takes the Active sheet from when the button is pushed.

Then it checks if the new sheets are already there and if so asks user if they want to overwrite them. If so it deletes them and recreates them.

Then it checks a specific cell on the original active sheet to see if it is the correct sheet and if not aborts with message as such.

This all works, but if the user selects one of the output sheets as the active sheet, then it is deleted the program is confused as the active sheet does not exist, but using the code to test it doesn't work b/c apparently it isn't nothing even though it doesn't exist.

Code:

If WS_input is Nothing Then
end sub
end if

I could obviously rearrange the order I do stuff in and check the active sheet first, and it isn't a problem anyway as I am the only one running it now, but it made me curious about how one would properly check if the worksheet that a variable referred to still existed.

 

There's nothing unique about a particular sheet apart from the data that's on it. Any given sheet can be referred to by its name or index within the worksheets collection (though the index is dynamic, so that sheets(1) will always exist even if you delete it). In essence, the best way to check for your particular sheet is to try and call it - then handle the error. This chap appears to be doing something similar to what you described: http://www.ozgrid.com/forum/showthread.php?t=72328.

 

I actually saw that previously, but it doesn't quite answer the question I was asking (I am not worried about the error or getting around it).

The weird thing is if a variable is created as a worksheet.
WS_input
in this case and set to the active sheet.
WS_input=active.sheet

Then the active sheet is deleted WS_input still is detected as something, though it points to nothing. And when VBA tries to read the cell it throws the error b/c it doesn't exist.

It just seemed strange that something could not exist and exist at the same time from the compilers perspective.