Looking for a Record Match with a Macro

Hi again

First, let me apologize for this lengthy post but it was found to be necessary in order to Clarify.

Second, thank you for your assistance to date; it was greatly appreciated but I am afraid there was a little confusion on the intent. I do apologize for this. Re-posting an edited record to where it came from is the current issue.

There are a total of 32 col of data per completed record (22 cells input and 10 cells calculated)

When the initial input is ready to be posted:
When a new record gets processed following input, part of this process involves copying the input data to a formulas row beginning at A96.
The formulas start at W96 of this row. This is done so that the formulas work on the new data just pasted before it.
Once resolved, the entire row containing the Input data and the formulas are copied.

SInce it is unknown to me as to how to target a cell found with a loop statement or any other programmed function, the copied range is pasted as values to an implicitly targeted row just above the record stack.

The range was then selected and cut.
The Cut range is then normally-pasted at the next empy row of the record stack when a "DoWhile" loop expression ends; completing the addition of the record; the Input screen is then returned for the next input.

During the Edit:
When a record gets recalled to an edit screen for any needed changes, the record is retrieved using lookup formulas that are copied from elsewhere in the sheet then pasted as values to the Edit Screen.
The data is edited then gets copied and Transposed-pasted to the same Formula row that was used during the input of the record.

Since the Now-edited row in 96 contains formulas again, it must be reposted as values; In essence, repeating the original input but getting posted to the same row in the Record stack it was recalled from for editing. Hence, the confusion-factor. Recalling a record to Edit is easy-enough but the "End-Edit and Post" is the problem.

The posting of a "New" record was easy-enough to figure out as all that was needed was to find the next empty row in the records stack to post it This was accomplished using a "DoWhile and Loop" routine targeting the next empty cell in col-A; the record#

This is where I am stuck;
Finding the record match by using some sort of "Conditional-Loop" to locate it in the record stack. The search criteria would be the record number that is part of the edited record in the Edit screen.
Once found, the edited record is simply pasted in place, replacing the original record.

The original thought for this was to use a "DoWhile" or similar expression that was used in finding the next empty cell for the new record; but was unsuccessful in finding the right expression for this.

I hope this clarified things.

And thanks is given once-again for your help and consideration.

The last bit-o-code I tried didn't seem to work and was the reason for the wordy scribe that preceeds this follow-up message.

Again, my apologies for that.

The objective again is to have a subroutine go down the Record number column of the record stack that starts at A101 (first record) until a match is found based on the search criteria of A1. This cell may also be a formula in the form of "=some cell"

The elusive subroutine will pick up after the last action of current code.

The following code is currently being used (less the needed code for what I would like for it to do); maybe this will help.

Private Sub CommandButton7_Click()
' end_edit_post Macro
MsgBox "This Option will only write the updated record to A98. The Subroutine needed to replace existing record is in process", vbInformation
Range("BA3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Application.Goto Reference:="R96C1"
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
' endright Macro
Range("A96").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Range("a98").Select

'Insert subroutine to Scan record column until the selected cell =A1 then append the rest of the code to complete this function

Tentative appended code for Replacing Record in stack
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("b41").Select
Range("ba4").Select

End Sub

Example;
If the cell contents of A1 = 1012, the code needs to go down the record column (A, or C1) until 1012 is found. Once found, the subroutine exits, allowing the rest of the Macro to resume.

The latest code attempt is here; thank you again for the attempt.

Please Login or Register  to view this content.

Thank you again for your efforts