Doubt with Offset

Ben AFF

Board Regular
Joined
Sep 21, 2023
Messages
55
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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. ;)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top