Posts by tinyjack

I am struggling to see how you are ending at C4 as the code should leave you at either C6 or D6 depending on the contents of E5.

How is this code being run? Is it in an event like ???_Change?

TJ

Do you need to do this in VBA or are you using VBA because you cannot find a formula approach?

Depending what is in the other rows that you are skipping, you might be able to use the LARGE() function.

TJ

If Funds are in A2:A6 and performance in F2:F6 then you can use:

=INDEX(A2:A6,MATCH(MAX(F2:F6),F2:F6,0))

In order to use Lookup the data must be in order, but using Match you have the ability to set wether you want an exact match or the nearest smaller or large number.

TJ

This works fine for me:

Range("a1").Formula = "=If(C2<>"""",LEFT(C2,20)&"" - ""&LEFT(D2,13),"""")"

TJ

I think we really need to see the rest of your code (the IF statements). I have never needed to use Goto (as in F5 GOTO) in any of my code, Select has always been fine (not that I ever use that very often)

IF statements can be fussy, especially if you are using the equals comparison ie "fred" does not equals "FRED" "Fred" or " fred "

TJ

Check the help files for the Find method. Using .Activate or .Select is going to cause an error if the item is not found as the return will be Nothing. Your use of On Error Resume Next is a fudge around the issue. You should be using:

Dim rngFound as Range


Set rngFound = ....Find(.......)


If Not rngFound Is Nothing Then
    'Action to process a match
Else
    'Action to process no match
End If

The is no need to use On Error with .Find. But, it seems to be one of the most common things I see.

Any more questions, just post.

TJ

Are you talking about automating Excel from VB6 or VB.Net rather than with VBA?

TJ

I make it 57.4%, like this:

TJ

The default property of a Range if Value, so as LastCellBeforeBlankinColumn is a Variant that is what it will return.

Try:

Sub test()
Dim fred as Range
Set fred = LastCellBeforeBlankinColumn(Range("a1"))
MsgBox fred.Address
End Sub




Function LastCellBeforeBlankinColumn(start As Range) As Range
    Set LastCellBeforeBlankinColumn = start.End(xlDown)
End Function

TJ

ps. Additional Stuff - see SET

Too many s's

LastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Columns 


'should be


LastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

TJ

You could either put =ROUND(A1,2)+ROUND(B1,2) into cell C1

or

say A1 contains =IF(Z27>12,CC12*Z27,C15*Z27) or whatever, you just wrap the whole thing with ROUND like this

=ROUND(IF(Z27>12,CC12*Z27,C15*Z27),2)

It really depends on your needs.

There is another option and that is to set Excel to Precision as displayed, but I am not a fan of using this as I feel it just a fudge to allow for poor sheet design.

TJ

Have a look at ROUND()

TJ

Here is an example file with conditional formatting set on A1:A4

TJ

Have a look at Conditional Formatting.

TJ

NOT has a higher precedent than OR so you are saying:

(NOT Condition) OR Condition

when you want to say

NOT (Condition OR Condition)

Put in the brackets and everything should be OK.

TJ

Just list the subs in one main sub

Sub MySuperSub()

SubName1
SubName2
SubName3
SubName4
SubNameFred

End Sub

TJ

Have a look at SUMPRODUCT:

=SUMPRODUCT((A1:A18<>0)*(B1:B18="*"))

TJ

Since you are dealing with Average, there will be no need to rework the formula. The only issue with Large is if you try to get an id to go with a value:

Bill 23
Tim 23
Simon 19

If you do Large(x1:x3,2) it would return 23, but Match and Offset would point to Bill and not Tim.

TJ