A = "0" & Trim(Str(I)) 'A001~A099
Range("B:B").Select '产品编号
Cells.Find(What:=A, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlUp, MatchCase:= _
False).Activate '找最后一笔
If ActiveCell.Row >= 3 Then
B = ActiveCell
S1 = UCase(ActiveCell.Offset(0, 3)) '最后第1笔来货结果
S2 = UCase(ActiveCell.Offset(-1, 3)) '最后第2笔来货结果
S3 = UCase(ActiveCell.Offset(-2, 3)) '最后第3笔来货结果
N1 = ActiveCell.Offset(0, 0) '最后第1笔产品编号
N2 = ActiveCell.Offset(-1, 0)
N3 = ActiveCell.Offset(-2, 0)
If S1 = "PASS" And S2 = "PASS" And S3 = "PASS" And N1 = N2 And N1 = N3 Then '是否为连续3笔PASS
Range("H65535").End(xlUp).Offset(1).Select '找最后一笔
ActiveCell = B '免检资料放在H栏
End If
End If
Next I
999
Range("A1").Select
End Sub
方法4：
Sub checkparts()
'On Error Rescom Next
Dim checksize As Integer
Dim scomPN As Integer
Dim id As Integer
Dim acceptcount As Integer
Dim firstPN, nextPN As String
Dim vendor As String
checksize = InputBox("Enter the number consecutive records to check for each PN")
Columns("G:I").Select
Selection.ClearContents
Range("B1").Select
id = 1
While ActiveCell.Value <> ""
firstPN = Trim(ActiveCell.Value)
scomPN = 1
nextPN = Trim(ActiveCell.Offset(scomPN, 0).Value)
vendor = Trim(ActiveCell.Offset(0, 3).Value)
Do While nextPN = firstPN
scomPN = scomPN + 1
nextPN = Trim(ActiveCell.Offset(scomPN, 0).Value)
Loop
If scomPN >= checksize Then
For acceptcount = 0 To checksize - 1
If UCase(Trim(ActiveCell.Offset(acceptcount, 2).Value)) <> "ACCEPTED" Then Exit For
Next acceptcount
If acceptcount = checksize Then
Cells(id, 7) = id
Cells(id, 8) = firstPN
Cells(id, 9) = vendor
id = id + 1
End If
End If
ActiveCell.Offset(scomPN).Select
Wend
Columns("G:I").Select
With Selection.Font
.Ncom = "Arial"
.Size = 9
.Strikethrough = False
.Supers cript = False
.Subs cript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAligcomnt = xlLeft
.VerticalAligcomnt = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Columns.AutoFit
Range("G1").Select
ActiveWindow.SmallScroll ToRight:=2
End Sub
如何用不同颜色的字体，将手式输入与公式计算结果区分开来
解答1：可以用条件格式，如A1格，则条件格式公式如下： =CELL("contents",A1)
解答2：用自定义函数了：
Function IsFormula(cl)
adrs = cl.Address
If Left(Range(adrs).Formula, 1) = "=" Then
IsFormula = True
Else
IsFormula = False
End If
End Function
条件格式公式为：
=IsFormula(A1)
怎样在电话号码书中提取手机号码
解答：假设a1=3322211,a2=13322121122,a3=2503225
a4={=sum((len(a1:a3)=11)*(a1:a3))}
完善：建议再加一个第一位是"1"的判断条件,以免长途超过11位的情况
{=SUM((LEN(A1:A3)=11)*(A1:A3)*(LEFT(A1:A3,1)="1"))}
是不是用数组函数解决？请教各位
a1=aa, b1=apple
a2=bb,b2=orange
.....
if(a11=aa, 则b11=apple
if(a11=bb, 则b11=orange
......
是不是用数组函数解决？请教各位。
解答：
如果对应关系比较多，先在一个工作表中分两列输入查找值及查找结果，如在工作表SHEET2中的A列输入查找值，B列输入对应的结果，在另一个工作表的B1格中用公式
=VLOOKUP(A1,SHEET2!A:B,2,0)
即可按该表的A1格中的值，在sheet2表的A列中查找并返回对应B列的值。
如果对应关系不多，可用公式
=VLOOKUP(A1,{"aa","apple";"bb","orange";......},2,0)
注意其中的;号。
又问：在SHEET1！B2输入
=IF(A1="","",if(没有匹配,"这是新货号",vlookup(a1,sheet2!a:b,2,0)))
如何实现上面要求。（没有匹配,"这是新货号",）
解答：ISNA(VLOOKUP(....))
关于日期的格式化问题
我要接收汇总数据；出生年月有的写成“58.5 ”有的写成"58.05" 我现在需要统一为1958.05(常规型）怎样实现。
解答：=value(IF(LEN(A1)>5,LEFT(A1,4)&"."&TEXT(RIGHT(A1,LEN(A1
松语文学免费小说阅读_www.16sy.com