type mismatch error 13

[whcmelvin]

I am facing this error when i run this code. the values in SelectRangeAuto is all 0. all are empty cells. the error is caused by AllUnrealizedPandL = WorksheetFunction.Sum(SelectRangeAuto). Please help. Thanks

Dim SelectRangeAuto As Variant
Dim AllUnrealizedPandL As Double

    With Sheets(EmailSheetAuto)
        SelectRangeAuto = .Range(.Range("J2"), .Range("J2").End(xlDown))
        If .Range("J2").Value <> "" Then
            AllUnrealizedPandL = WorksheetFunction.Sum(SelectRangeAuto)
        End If
    End With

 

[Norie]

Try this:

Set SelectedRangeAuto = .Range(.Range("J2"), .Range("J2").End(xlDown))

You might also want to check you are trying to sum the correct range, using xlDown isn't always the best way to find the last row of data.

 

[whcmelvin]

Hi, the code is suppose to be refreshing every sec. Then when i first use the excel, the range should be empty. while i am using, it will then have values inside. i am thinking that it might be the empty cells that causes the problem but i do not know how to solve it

 

[Norie]

I used what I suggested on a blank sheet and there was no error.

I think the error is because you are trying to sum an array with over 1 million items, which is a result of using xlDown.

eg if there's nothing below J2 then xlDown will return the very last row in the worksheet

Is there definitely nothing in cell J2 when you get the error?

 

[whcmelvin]

Ya. there is nothing in J2. hmmm. Btw, does you all know if excel could insert those java charts that in in the internet?

 

[Norie]

If there's nothing in J2 then your code should skip the Count part.

Try what I suggested or changing to use xlUp and finding the last row from the bottom upwards.

Java Charts? What to do you mean?

 

[whcmelvin]

Okay thanks. Internet has those financial charts. it uses JAVA. is there any way to import it to the excel?

 

[Norie]

I've honestly no idea what you mean.

Can you post a link to one of these 'java' charts?

 

[whcmelvin]

Sorry to have confused you. this is the link http://finance.yahoo.com/echarts?s=G13.SI+Interactive#symbol=G13.SI;range=

 

[Norie]

That isn't a 'java' chart as far as I can see.

It's something created in Flash.