Doubt with Offset

[Ben AFF]

Please appreciate if you can help me clarify a doubt.
In this procedure to find the max value in a range, why is Row -1 and not just Row? Thank you.




Sub ExitForDemo()

Dim MaxVal As Double
Dim Row As Long

MaxVal = WorksheetFunction.Max(Range("A:A"))

For Row = 1 To Rows.Count

If Range("A1").Offset(Row - 1, 0).Value = MaxVal Then
Range("A1").Offset(Row - 1, 0).Activate
MsgBox "Max Value is in Row " & Row
Exit For
End If

Next Row

End Sub

 

[RoryA]

Because you want to include A1 in your search. When Row is 1 at the start, Range("A1").Offset(1) would refer to A2 (one cell offset from A1) but you actually want Range("A1").Offset(0), which is A1, so you subtract 1 from Row.

That is not particularly efficient code though.

 

[Ben AFF]

Much appreciate your reply.
I got this example from a book, supposedly teaching me to code

If Im understanding correctly
A1 is Row 1 but for offset A1 is 0
Do I get it right?

Thank you.

 

[RoryA]

Yes. The Offset command tells the code how many rows (and/or columns) to move from the starting range. So if you want to refer to the start range itself, the offset number needs to be 0, not 1.

 

[Ben AFF]

Much appreciated RoryA. I got this example from VBA Programming for Dummies (yes Im a Dummy) but you said its not efficient coding.
Im somehow bothered that this code snippet is the example to teach for loops and not actually offset and the concepts are somehow obscured trying to figure out these bits of code.

Might I ask, if you would you be so kind to recommend me any books for begginer learner? Thank you.

 

[RoryA]

It's been a long time since I read any new VBA books, but obviously I would trust anything from the MrExcel store. Perhaps this one: Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365) which looks like a good place to start, looking at the table of contents.

 

[Ben AFF]

Ohh wow! thank you much.