用下面修改下
Excel按规定提取列数据
Option Base 1
Sub 按规定提取某列数据()
'2020-4-3 21:34:49
Dim mb(), m(), i As Long, j As Long, r As Long, c As Long, n As Range, k As Long, myr As Range, n1 As Long
mb = Selection
r = UBound(mb, 1)
c = UBound(mb, 2)
ReDim m(r, 1)
Set n = Application.InputBox(prompt:="选择规定数据单元格", Type:=8)
n1 = 1
k = 0
On Error Resume Next
For i = 1 To r
For j = 1 To c
If InStr(1, mb(i, j), n.Text, 1) > 0 Then
k = k + 1
m(k, 1) = mb(i, n1)
Exit For
End If
Next j
Next i
If k = 0 Then
MsgBox "数据不存在!"
Else
Sheets("Sheet5").Select
Set myr = Range("a1")
myr.Value = "商户"
Range(myr.Offset(1, 0), myr.Offset(k, 0)) = m
End If
End Sub
在D列任单元格输入2020-06
运行上面宏时,选该单元格可以
您好,问一下就是,这个公式里面C$100是指到第100行为止是么?我把他改成1000提示错误了
追答是100行,改成1000不能只改C$1000,也要改$2:$1000
=index(sheet4!a:a,small(if(month(sheet4!c$2:c$1000)=6,row($2:$1000),6^6),row(a1)))&""
这个就不行了,提示错误了=index(sheet4!a:a,small(if(month(sheet4!c$2:c$1000)=6,row($2:$1000),6^6),row(a1)))&""
提示c$2:c$1000这个位置错误
截图上来帮你看看,带行列号和公式
追问左边图工作表名露出来看一下,还有C列是不是日期格式,点击C2,看看编辑栏是否显示2016/6/6,如果不是就不是时间格式,公式出错
追问中间有日期是错误不显示的,然后公式中让这个错误跳过可以么?谢谢