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?
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
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
Here's a screenshot of the area involved.
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
