I have a shortage report generated daily by the company server. It has
PartNo (Col A), Owner (Col B), Comments (Col C), Qty (Col D). Part numbers
will only appear in this file if qty falls below certain value.

I already have a macro that can filter the daily shortage report by owner
(which is my name). Based on the filtered list (all part numbers that belong
to me), I will update the comments column.

Now I created a master file with all the part numbers that I need to take
care of. It has 2 columns: MyPartNo (col A) and Comments (col B).

I also created a macro that can match the PartNo in shortage report to
MyPartNo in my master file, copy the comments I enter earlier in shortage
report and then paste it to Comments column (Col B) in my master file. But
this macro still check all PartNo in the shortage file despite it's filtered.

How can I write the macro in such a way that it only match/look for the
PartNo based on the filtered list, but not every single part number in the
whole shortage report? This will reduce a lot of macro processing time.

choo

You would probably have to use the SpecialCells(xlCellTypeVisible) method to
skip the hidden cells created by the filter.



"choo" wrote in message
...
I have a shortage report generated daily by the company server. It has
PartNo (Col A), Owner (Col B), Comments (Col C), Qty (Col D). Part
numbers
will only appear in this file if qty falls below certain value.

I already have a macro that can filter the daily shortage report by owner
(which is my name). Based on the filtered list (all part numbers that
belong
to me), I will update the comments column.

Now I created a master file with all the part numbers that I need to take
care of. It has 2 columns: MyPartNo (col A) and Comments (col B).

I also created a macro that can match the PartNo in shortage report to
MyPartNo in my master file, copy the comments I enter earlier in shortage
report and then paste it to Comments column (Col B) in my master file.
But
this macro still check all PartNo in the shortage file despite it's
filtered.

How can I write the macro in such a way that it only match/look for the
PartNo based on the filtered list, but not every single part number in the
whole shortage report? This will reduce a lot of macro processing time.

choo

Hi,
Based on the code below, how do I modify it to use xlCellTypeVisible like
you suggested?

NRowsComments = shtComments.Cells(1, 1).SpecialCells(xlLastCell).Row

shtSR.Activate
irow = 2
NRowsSR = Cells(1, 1).SpecialCells(xlLastCell).Row
For irow = irow To NRowsSR
PartNo = Trim(Cells(irow, 1).Value)
irow2 = 2
Fnd = False
While Not Fnd And irow2 <= NRowsComments
If Trim(shtComments.Cells(irow2, 1)) = PartNo Then
Fnd = True
If Cells(irow, 3) < "" Then
msgbox "copy and paste"
End If
Else
irow2 = irow2 + 1
End If
Wend
Next




"JLGWhiz" wrote:

You would probably have to use the SpecialCells(xlCellTypeVisible) method to
skip the hidden cells created by the filter.

I am not sure this will fly as part of your procedure, but it should give
you an idea of how to use the special cells. It looked to me like you were
only testing column A data, so I limited the range to check to only cells in
column A. In the For Each line, the range is restricted to only visible
cells so that the hidden cells will be ignored although the line above
includes them as part of the range.

Dim Nrng As Range
NRowsSR = Cells(1, 1).SpecialCells(xlLastCell).Row
Set Nrng = Range("A" & irow & ":A" & nRowsSR)
For Each c In Nrng.Cells.SpecialCells(xlCellTupeVisible)
PartNo = Trim(Cells(c.Row, 1).Value)
irow2 = 2
Fnd = False
While Not Fnd And irwo2 <=NRowsComments
If Trim(c.Value) = PartNo Then
Fnd = True
If Cells(c.Row, 3) < "" Then
MsgBox "Copy and Paste"
End If
Else
irow2 = c.Row + 1
End If
Wend
Next





"choo" wrote in message
...
Hi,
Based on the code below, how do I modify it to use xlCellTypeVisible like
you suggested?

NRowsComments = shtComments.Cells(1,
1).SpecialCells(xlLastCell).Row

shtSR.Activate
irow = 2
NRowsSR = Cells(1, 1).SpecialCells(xlLastCell).Row
For irow = irow To NRowsSR
PartNo = Trim(Cells(irow, 1).Value)
irow2 = 2
Fnd = False
While Not Fnd And irow2 <= NRowsComments
If Trim(shtComments.Cells(irow2, 1)) = PartNo Then
Fnd = True
If Cells(irow, 3) < "" Then
msgbox "copy and paste"
End If
Else
irow2 = irow2 + 1
End If
Wend
Next




"JLGWhiz" wrote:

You would probably have to use the SpecialCells(xlCellTypeVisible) method
to
skip the hidden cells created by the filter.

Your example works! Thank you.

Since I'm trying to learn about this "range" example, would you be able to
show me how should the code looks like if the data in destination file is
filtered instead of the source file?