I have an Excel workbook that has values from F13 to F40, these values change every Powershell Job run we do.
I need to find the Top 3 values in the range of F13 - F40 and I need to get the Row Number of each of the three values
So far I can get the 3 values with this Code in Powershell
$EXCEL = New-Object -ComObject Excel.Application $EXCEL.visible = $false $basefinalwb = $EXCEL.workbooks.open("C:\relax\Book1.xlsx") $basefinalws = $basefinalwb.worksheets.Item("Current Results") $MAX1 = $basefinalws.cells.item(1,1).Formula = "=LARGE(F13:F40, 1)" $MAX2 = $basefinalws.cells.item(2,1).Formula = "=LARGE(F13:F40, 2)" $MAX3 = $basefinalws.cells.item(3,1).Formula = "=LARGE(F13:F40, 3)" $MAX11 = $basefinalws.cells.item(1,1).value2 $MAX21 = $basefinalws.cells.item(2,1).value2 $MAX31 = $basefinalws.cells.item(3,1).value2 Write-host Max11 is $max11 Write-host Max21 is $max21 Write-host Max31 is $max31
The Results are this:
Max11 is 0.112570356472795
Max21 is 0.097560975609756
Max31 is 0.0881801125703564
Now I need to figure out which Row each of those Values came from
So I have tried this code and it returns blank
$Cell2 = $basefinalws.range("F1","F40").find($MAX21) $Cell2.Value2 $Cell2.Row $Cell2
In my testing I even pulled the Value on the Cell the number came from and they match … I just cannot figure out why “find” isn’t working.
Now the only thing I can think of is that all the values in the F column are from Formulas =(D13-C13)/C13 but the above code does grab the value of the cell just fine it just can’t re-find it so I can get the Row Number
If anyone knows where I am going wrong, help is appreciated or maybe an easier way to accomplish the task?