using VBA to lookup a row that contains a value and change the contents in one cell

[pcholka]

Hello,

I have a data table like a checkbook register. I've written several Macros that allow me to edit the table from a different worksheet using a form (add data, look up data, delete a row in the table, etc.). I want to change the delete row functionality to a void functionality.

Every row has an ID number. I use the following string to lookup the ID number and delete the row.
Set Rng = TransIdCol.Find(What:=SearchValue, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False)
If Not Rng Is Nothing Then
Rng.EntireRow.Delete

I want to take the current value in column I of the row it finds and replace it with "void"

I am struggling finding a string to replace "Rng.EntireRow.Delete" to do that.

*credit to TeachExcel.com for the string I am currently using.*

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Replace a value in a cell of a row - Idiot Proofing Forms Series | TeachExcel.com
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

How about

If Not Rng Is Nothing Then
Range("I" & Rng.Row).Value = "Void"

 

[pcholka]

It works when I run it from the VB editor. Thank you! When I run it from the button I created and linked to the Marco in another worksheet, it does not.

 

[Fluff]

Which col is the TransId column?

 

[pcholka]

Which col is the TransId column?

Column A

 

[Fluff]

Ok, how about

If Not Rng Is Nothing Then
Rng.Offset(, 8).Value = "Void"

 

[pcholka]

This does it!!! Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.