Search and Replace, or Add New Row

[GEdwards]

I am using Excel 2003. I mistakenly posted this originally under General
Questions.

Worksheet1 contains columns
(A) Invoice
(B) Name
(C) Address
(D) P.O.# ... and others.

I need to perform a search of column A using an invoice number and if it IS
FOUND, then replace the entire row with new data.

If the invoice number IS NOT FOUND, then I need to add a new row.

Is there a simple way to do this?

 

[Jacob Skaria]

Try

Sub Macro()

Dim lngNewRow As Long
Dim varFound As Variant
Dim varSearch As Variant

varSearch = InputBox("Enter Invoice Number")
Set varFound = Columns(1).Find(varSearch)

If Not varFound Is Nothing Then
Range("A" & varFound.Row).Offset(0, 1).Resize(, 3).ClearContents
MsgBox "Cleared"
Else
lngNewRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
MsgBox "Added"
End If


End Sub

 

[Jacob Skaria]

If you need to clear the entire row then replace the below line

Range("A" & varFound.Row).Offset(0, 1).Resize(, 3).ClearContents

'with

Rows(lngNewRow).ClearContents

 

[Aussie Bob C]

Neither of the ClearContents lines work.
--
Thank you

Aussie Bob C
Little cost to carry knowledge with you.
Win XP P3 Office 2007 on Mini Mac using VMware.

 

[helene and gabor]

'Try this:

Sub amacro()
Dim varFound As Variant
Dim varSearch As Variant
Dim ulimit As Long
varSearch = InputBox("enter InvoiceNumber")
ulimit = Cells(Rows.Count, "A").End(xlUp).Row + 1
Count = 0
For i = 1 To ulimit
If Cells(i, 1).Value / varSearch = 1 Then
Count = 1
Cells(i, 2).Value = ""
Cells(i, 3).Value = ""
Cells(i, 4).Value = ""
Cells(i, 5).Value = ""
Cells(i, 6).Value = ""

End If
Next i
If Count = 0 Then

Cells(ulimit, 1).Value = varSearch
End If








End Sub




Hello,

If an invoice is read that is listed in column A, then it deletes entries in
columns B to F.
If invoice is not found in column A then the first empty line will have the
new invoice number added.

HTH
Best Regards,

Gabor Sebo

 

[Don Guillett]

Option Explicit
Sub FindRow()
Dim mr
Dim mwhat As Long
mwhat = InputBox("Enter number to find ie:40533")
Set mr = Columns(1).Find(mwhat, LookIn:=xlValues, _
lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not mr Is Nothing Then
Cells(mr.Row, 1) = "new"
Cells(mr.Row, 2) = "new2"
'etc
'MsgBox mr
Else
'MsgBox "not found"
Rows(Cells(Rows.Count, 1).End(xlUp).Row).Insert
End If
End Sub