Prev: excel extract data from one sheet to another
Next: Invisible Formula in Cells !!!!!!!!!!!!!!!!!!
From: GEdwards on 20 Apr 2010 22:40
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?
From: Jacob Skaria on 21 Apr 2010 01:11
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?
From: Jacob Skaria on 21 Apr 2010 01:14
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?
From: Aussie Bob C on 21 Apr 2010 08:49
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?
From: helene and gabor on 15 May 2010 09:38
'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

--------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------


"GEdwards" <GEdwards(a)discussions.microsoft.com> wrote in message
news:A10B9623-7E3A-4FA7-B718-55C7A6519352(a)microsoft.com...
>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?
>

 |  Next  |  Last
Pages: 1 2
Prev: excel extract data from one sheet to another
Next: Invisible Formula in Cells !!!!!!!!!!!!!!!!!!