Log In
Register
Home
Excel Tables
May 27, 2019
Offline
This code finds the first smallest value: firstVal = Application.WorksheetFunction.Small(rng, 1)
I would like to either backcolor that cell or put "First" in the next cell. I've tried setting a range variable to find and select firstval:
Set cell = cells.Find(firstVal).Select
Selection.Offset(0, 3).value = "First Place"
Error the select line - can anyone render assistance?
Trusted Members
December 20, 2019
Offline
Hi Sal
Something like the below should work
Sub FirstSmall()
Dim rng As Range
Dim firstVal As Integer
Dim cell As Range
Set rng = Range("a1:a9")
firstVal = Application.WorksheetFunction.Small(rng, 1)
Set cell = Cells.Find(firstVal)
With cell
.Offset(0, 3) = "First"
.Interior.Color = vbRed
End With
End Sub
Purfleet
Excel Tables
May 27, 2019
Offline
Hi Purfleet,
Thank you for the rapid response. I ran the macro with my range and got the same error. I did not mention that the range is comprized of merged cells, could this have an effect?
Sal
Excel Tables
May 27, 2019
Offline
Here's a screenshot of the area involved.
Trusted Members
December 20, 2019
Offline
I think it was a combination of things, but merging cells is the devils own work
It is always better to upload test data when asking questions or debugging so we can see what is happening.
I have amended the code to look at values to make it a little more resilient, but also unmerging the data does seem to work.
Sub FirstSmall()
Dim rng As Range
Dim firstVal As Integer
Dim cell As Range
Set rng = Range("i38:i57")
firstVal = Application.WorksheetFunction.Small(rng, 1)
Set cell = rng.Find(what:=firstVal, LookIn:=xlValues)
'Debug.Print cell.Address
With cell
.Offset(0, 3) = "First"
.Interior.Color = vbRed
End With
End Sub
1 Guest(s)
