PDA

View Full Version : Solved: Search, Edit and Re-insert



kabbak
01-18-2012, 07:06 AM
Thanx Pros for the excellent help all the time.
I do have a table of data (on Worksheet 1) that i can search from another worksheet (Worksheet 1) Thanx to mdmackillop for the code (http://www.vbaexpress.com/forum/showthread.php?t=40420).

I'd now like to be able to make corrections on a searched entry on sheet 1 so that running some code would re-insert the corrected entry back into the table.

Column A has numbers which am not going to be correcting whatsoever so i was thinking of a code that would search the table by column A and on finding the entry, paste the corrected entry over it. (in that same row).

Hope am not diverting your minds from a possible cheap solution to the problem.

The Columns of Data are from A to R and corrections are going to be made on column E or H or both.

mdmackillop
01-18-2012, 02:03 PM
Select the name cell from Names 1-5 and run the code.

Sub DoCorrect()
Dim r As Range
Dim c As Range

Set r = Cells(25, 4).Resize(9)
If Intersect(ActiveCell, r) Is Nothing Then
MsgBox "Please select a name cell"
Exit Sub
Else
Set r = ActiveCell.Resize(, 10)
Set c = Sheets("Info Sheet").Columns(3).Find(r(1), lookat:=xlWhole).Offset(, -2)
c.Offset(, 4) = r(, 2) 'Amount
c.Offset(, 3) = Range("F8") 'Phone No
c.Offset(, 6) = r(, 3) 'Month
'etc
End If
End Sub

kabbak
01-23-2012, 02:41 AM
Thanks Again.
I'll be bothering you from time to time as i make upgrades to "the" system.
I do have lots of innovations to make on it but am doing them manually at the moment. Watch and wait for the next question. It'll be some challenge though. It'll be to do with some payment sheet and the info sheet.
Thanks again.