Search and Replace, or Add New Row
 

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?

Search and Replace, or Add New Row
 

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 (MVP - Excel)


"GEdwards" wrote:

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?

Search and Replace, or Add New Row
 

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


--
Jacob (MVP - Excel)


"Jacob Skaria" wrote:

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 (MVP - Excel)


"GEdwards" wrote:

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?

Search and Replace, or Add New Row
 

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.


"Jacob Skaria" wrote:

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


--
Jacob (MVP - Excel)


"Jacob Skaria" wrote:

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 (MVP - Excel)


"GEdwards" wrote:

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?