Return Max value of a range of cells using VBA? 3

[Excelerate2004]

Hello to all,

I'm trying to get the following to work in VBA for excel:

If Sheets("affydata").Range(Max("C2:F2")).Value = C2 Then
I2 = "A"
End If

If Sheets("affydata").Range(Max("C2:F2")).Value = D2 Then
I2 = "C"
End If

If Sheets("affydata").Range(Max("C2:F2")).Value = E2 Then
I2 = "G"
End If

If Sheets("affydata").Range(Max("C2:F2")).Value = F2 Then
I2 = "T"
End If

Is it possible to use the max function in this way within VBA??

Thanks

 

[xlbo]

don't think so - what are you trying to achieve ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

Something like this ?
With Sheets("affydata")
If .[C2] >= .[D2] And .[C2] >= .[E2] And .[C2] >= .[F2] Then
I2 = "A"
ElseIf .[D2] >= .[C2] And .[D2] >= .[E2] And .[D2] >= .[F2] Then
I2 = "C"
ElseIf .[E2] >= .[C2] And .[E2] >= .[D2] And .[E2] >= .[F2] Then
I2 = "G"
Else
I2 = "T"
End If
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

I ASSUME that C2, D2, E2, F2, I2 are CELL REFERENCES

With Sheets("affydata")
    Select Case Application.Max(.Range("C2:F2"))
        Case .[c2]
            .[I2] = "A"
        Case .[d2]
            .[I2] = "C"
        Case .[E2]
            .[I2] = "G"
        Case .[F2]
            .[I2] = "T"
    End Select
End With

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Excelerate2004]

Thanks PHV but theres A slight modification, with brackets around the I2:

With Sheets("affydata")
If .[C2] >= .[D2] And .[C2] >= .[E2] And .[C2] >= .[F2] Then
.[I2] = "A"
ElseIf .[D2] >= .[C2] And .[D2] >= .[E2] And .[D2] >= .[F2] Then
.[I2] = "C"
ElseIf .[E2] >= .[C2] And .[E2] >= .[D2] And .[E2] >= .[F2] Then
.[I2] = "G"
Else
.[I2] = "T"
End If
End With

 

[Excelerate2004]

Thats another alternative as well skip, thanks!

 

[Excelerate2004]

I'm not sure whether to post this as a seperate thread, however there is one more question I have regarding this code snippet, I'd like to be able to execute this code no matter how many records I have.

Right now I use a FOR NEXT Loop but I enter how many records I have manually.

With Sheets("affy data")
For z = 2 To 16384
If .Range("C" & z).Value >= .Range("D" & z).Value And .Range("C" & z).Value >= .Range("E" & z) And .Range("C" & z) >= .Range("F" & z) Then
.Range("J" & z).Value = "A"
ElseIf .Range("D" & z).Value >= .Range("C" & z).Value And .Range("D" & z).Value >= .Range("E" & z) And .Range("D" & z) >= .Range("F" & z) Then
.Range("J" & z).Value = "C"
ElseIf .Range("E" & z).Value >= .Range("C" & z).Value And .Range("E" & z).Value >= .Range("D" & z) And .Range("E" & z) >= .Range("F" & z) Then
.Range("J" & z).Value = "G"
Else
.Range("J" & z).Value = "T"
End If
Next
End With


What components do I need to build the code so that I can execute the above code for all my records or in other words have it execute dynamically.


Thanks

 

[SkipVought]

What do you mean by "... have it execute dynamically."???

Make it work on the row that you just changed?

Make it work when you Activate a Sheet?

Make it work on any workbook that you open?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[PHV]

Have a look in the FAQ area to discover how to find the last row.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

I think they want to know how to find the last row of data......at least 2 FAQs on that in the FAQs section here

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Excelerate2004]

To skip:

I'd like it to work on my entire recordset regardless of how big or small the recordset is. Right now it uses a confined FOR loop. It goes through records 2 to 16384.

I'd like my code to be "smart" enough to execute the If statements regardless of how big or small the dataset is.

For example if I was to change the For loop to 2 to 16382, it would miss 2 records or rows.

So I want my code to be able to find the end of the recordset and execute the If statements only on those records contained within.

Thanks

 

[Excelerate2004]

I found this simple line of code that moves the cursor to the just outisde the last row & last column.

Excel.Selection.SpecialCells(11).Select

I guess what I need to do now is find a way to return that position as a variable and then feed that into my FOR loop.


Hmm...

 

[AnotherHiggins]

You could use something like this instead of selecting the cell:
rngLastCell = Excel.Selection.SpecialCells(11).Address
or for an IF loop:
rngLastCell = Selection.SpecialCells(11).Row

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Excelerate2004]

Thanks for the group "shove" in the right direction, I should be able to figure it out now!

Cheers!

 

[xlbo]

....and there are still 2 FAQs on how to find the last cell in a column to work with......

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question