0

Im trying to creat a program that finds the highest magnitude value in a column and returns the value value of the cell to the right of it.

When I run my code it just returns nothing in the VLOOKUP cell. `

Sub maxMoment()
Dim j As Integer
j = 1
Dim max As Single
max = 0
For i = 0 To (l + 0.1) Step 0.1
    If Abs(max) < Abs(Cells(j, 47)) Then
        max = Cells(j, 47)
    End If
    j = j + 1
Next i
Range("H21").Select
ActiveCell.Value = max
Range("H22").Select
ActiveCell.Value = Application.WorksheetFunction.VLookup(max, Sheet1.Range("AU:AV"), 2)
End Sub

` Any tips would be greatly appreciated.

Joshua El-araj
  • 11
  • 1
  • **1.** I hope you know your loop will only run two times? You have not initialized the value of `l`. If that is not intentional then I highly recommend you use `Option Explicit`. And if that is intentional (which I do not think it is) then simply use `For i = 1 to 2` since you are not using the vaiable `i`. In such a scenario then `0.1` also loses it significance. **2.** What is the value of `max` **3.** Have you physically checked if that value is present in the table? Also use the formula bar to check the value in the cell else you will not see the underlying value. – Siddharth Rout Nov 14 '22 at 04:21
  • **Other Tips** **1.** [Avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) **2.** Avoid the use of `Integer` when working with *Excel Rows*. Use `Long`. I have spoken about it in [THIS](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba/11169920#11169920) post. **3.** You can also step by step debug your code. If you are interested then see [Error Handling & Debugging](https://youtu.be/1ZLuco1-klw) – Siddharth Rout Nov 14 '22 at 04:29
  • In Fact, in relation to `Option Explicit`, I would recommend Developer>Visual Basic>Tools>Options>Require Variable Declaration - Ticked. (so it's done by default) – Stax Nov 14 '22 at 04:30
  • Yup that is a very good idea @Stax. In fact, I have spoken about it in [Visual Basic Editor - An Introduction](https://youtu.be/lftEbmYwh-U). It is mentioned in the section [Option Explicit](https://www.youtube.com/watch?v=lftEbmYwh-U&t=1159s) – Siddharth Rout Nov 14 '22 at 04:36

0 Answers0