Clarify OFFSET Command

[oskar44]

From a previous post I understand that the Offset parameter for my cmdADD_Click() shown below should be like (1, n). I did this change and it works well; in a lesson I follow it was set like this (0, n)

Please Login or Register  to view this content.

But how about the following two commands shown below, cmdDelete_Click() and cmdEdit_Click(), should I also change the Offset to (1, n) from (0, n)? When I do this change I notice no difference but perhaps I’m making a mistake. The reason I’m asking is perhaps on Delete / Edit commands this Offset parameter makes no difference?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[JasperD]

Too lazy to read all your code, but:

"Offset" determines the number of cells "away" from the cell you're working with - where the first number is the offset in rows and the second number is the offset in columns.

A few examples:

Please Login or Register  to view this content.

would be cell A2, since it's A1 offset by 1 row and 0 columns

Please Login or Register  to view this content.

would be cell B2, since it's A1 offset by 1 row and 1 column

Please Login or Register  to view this content.

would be cell F1, since it's 4 rows less and two columns extra.

Please Login or Register  to view this content.

would be cell D25, since there's only 1 number specified, excel assumes it's rows, since there's no comma

Please Login or Register  to view this content.

is cell X5, since it's offset 20 columns.

I hope this clarifies things.

[TMS]

I think JasperD has done an excellent job of explaining the Offset property of a range.

Here's an explanation from the horse's mouth ... but with a lot less detail For more examples, Google: excel vba offset property of range

That said, it isn't really necessary or useful in this case . It just means that you have to work out, based on the start range and the offset, where you will be putting the data. Bit of a PItA in my opinion. Very helpful if you're looping through a range, but you're not.

Some alternatives to compare. I'm not actually searching for the ID, I've just set a cell as the starting point for each of the options.

Please Login or Register  to view this content.

Regards, TMS

[TMS]

Sorry, as an answer to the specific question.

If you are inserting a new entry, you need to find the last row of data and add 1 to the row ... so you write the new data into an empty row. You would write .Offset(1, n)

If you are editing or deleting an entry, you need to find the row that corresponds to that ID and edit that row. In this case, you'd write .Offset(0, n)


Regards, TMS

[oskar44]

TMS you are a genius and you made my day

I followed what you mentioned in my earlier post “Offset should ALL be (1, n) rather than (0, n) and it worked well in the ADD command but in the DELETE or EDIT command did not work and that was driving me crazy.

Now with your excellent reply, all is fine and understood.

Thanks so much

[TMS]

You're welcome.

Did you see post #3?



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.