MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

pls amend this macro

  Asked By: Norma    Date: Aug 22    Category: MS Office    Views: 854

When i run this macro, it will ask "Search for what", when i give the date
for ex: 3/7/2008. It will search for this date in H column in the sheet "TAT
invoice processed", if the date is found, the entire rows will be copied
from the sheet TAT Invoice processed to another sheet "DailyTAT invoice
But the macro is not working properly.... It searches the date for the range
& not in H column......but I want this macro to search the date *ONLY IN H
column. *
Can you helpme in rectifying this.

Sub copyTATinvoiceProcessed()
response = InputBox("Search for what")
Dim MyRange, MyRange1 As Range
Sheets("TAT Invoice Processed").Select
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
*Set MyRange = Sheets("TAT Invoice Processed").Range("A11:H" & lastrow)
*For Each c In MyRange
If UCase(CStr(c.Value)) = UCase(response) Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
If MyRange1 Is Nothing Then
MsgBox ("No Matches for " & response)
Exit Sub
End If
If (Sheets("DailyTAT Invoice Processed").Range("A11") <> "") Then
Sheets("DailyTAT Invoice Processed").Rows("11:" &
Range("A11").End(xlDown).Row).Delete Shift:=xlUp
End If
Sheets("DailyTAT Invoice Processed").Select
End Sub



2 Answers Found

Answer #1    Answered By: Abbas Hashmi     Answered On: Aug 22

First of all,
you're setting MyRange to be from column  A, row  11, to column H, row "lastrow".
simply set to H11, like:
.Range("H11: H" & lastrow)

Also, be aware that you're doing string comparisons.
NOT date  comparisons!

That means that if the user enters 03/07/2008 or even 7-Mar-2008, it will NOT
find the records with 3/7/2008 as a match.

Consider first using the isdate(result) function to ensure that it is a date
then use the datediff() to see if the dates match.

Answer #2    Answered By: Jana Franklin     Answered On: Aug 22

This question has already been asked..............

Didn't find what you were looking for? Find more on pls amend this macro Or get search suggestion and latest updates.