Posts by jbrasewll

Re: Why is the .select method failing?

Argh! I'm sorry about that. At some point during my testing I accidentally assigned a different value to the cell that originally contained the text.

Thank you.

Re: Why is the .select method failing?

Sorry to be such a pain, but it still fails when I use Barrie's method. It fails at "cell.Worksheet.Activate":

Sub pastedata()
Dim timecell, exchcell, productcell, MMMYYcell, BScell, AvgPricecell, FilledQtycell As Range
Dim sheetname As String
Dim wsnum As Integer
Dim cell As Range
Dim cell2 As Range
Dim cell3 As Range


wsnum = Worksheets.Count - 1


With Worksheets(wsnum).Range("a9:az9")
Set cell = .Find(what:="Ord Time")
End With




cell.Worksheet.Activate
cell.Offset(1, 0).Select


        
End Sub

Re: Why is the .select method failing?

Sure...

Re: Why is the .select method failing?

Hmm, there must be something more fundamental going wrong here. I just simplified my code to the following, and it still gives me the error, even though I know this test code was working before I got the error the first time.

Is there some way I could have inadvertantly turned off a set of methods or something?

Sub pastedata()
Dim timecell, exchcell, productcell, MMMYYcell, BScell, AvgPricecell, FilledQtycell As Range
Dim sheetname As String
Dim wsnum As Integer
Dim cell As Range
Dim cell2 As Range
Dim cell3 As Range


wsnum = Worksheets.Count - 1


With Worksheets(wsnum).Range("a9:az9")
Set cell = .Find(what:="Ord Time")
End With




Worksheets(wsnum).Range("a1").Select


       
End Sub

Re: Why is the .select method failing?

Hmmm, that doesn't seem to work either. I get the same error.

Re: Why is the .select method failing?

Yes, it does. If I comment out the latter part of the code and type

msgbox cell

it returns "Ord Time".

Whenever I run this macro, I'm told that the select method of the range class failed. I know I'm missing something obvious. What I'm really trying to do is select the range just below this cell, like this:

Range(cell.Offset(-1, 0), cell.End(xlUp)).Select

Thank you in advance.

Jason

Sub pastedata()
Dim timecell, exchcell, productcell, MMMYYcell, BScell, AvgPricecell, FilledQtycell As Range
Dim sheetname As String
Dim wsnum As Integer
Dim cell As Range
Dim cell2 As Range
Dim cell3 As Range


wsnum = Worksheets.Count - 1


With Worksheets(wsnum).Range("a9:az9")
Set cell = .Find(what:="Ord Time")
End With


cell.Offset(-1, 0).Select
End Sub

Re: P&L Calculation

Wow! That works wonderfully. Thank you guys very much. That was a far more complete response than I had hoped for.

The macro is very intuitive and far better than the pure Excel solution I was envisioning.

:thanx:

Hi, guys. I'm writing a sheet to analyze my trading activity. If you look at the attached, I want the P&L column to show the net result of all activity since the last time I was flat, i.e., had no position, i.e., last time 'Cumulative
Quantity' was zero.

I can use sumproduct to calculate P&L, but I don't know how to limit the positions to just those that have occurred since the column value was zero. Is there a way to do this with just Excel, or do I need to write a macro to fill this column? If the latter, I can work my way through it, but I wanted to know if it was even necessary.

Thanks!
Jason

Re: Assigning Dimensions to a Range Variable

Thank you very much for your help. I got it to work!

Sorry if this question is simple.

I want to assign a range variable certain dimensions. However, I don't know these dimensions until the program calculates them and assigns them to variables. So, let's say N=2 and M=5. I want some range variable, call it 'x', to equal, say, A2:C5.

How do I assign 'X' these values? Keep in mind that in the code, I need to be able to put variables in the assignment statement for both the column and row.

Thanks for any help in advance.

Jason