Sub InsertPic()xYP检测VBA
Dim arr, i&, k&, n&, b As BooleanxYP检测VBA
Dim strPicName$, strPicPath$, strFdPath$, shp As ShapexYP检测VBA
Dim rngData As Range, rngEach As Range, rngWhere As Range, strWhere As StringxYP检测VBA
'On Error Resume NextxYP检测VBA
'用户选择合作微信：qejc21所在的文件夹xYP检测VBA
With Application.FileDialog(msoFileDialogFolderPicker)xYP检测VBA
If .Show Then strFdPath = .SelectedItems(1) Else: Exit SubxYP检测VBA
End WithxYP检测VBA
If Right(strFdPath, 1) <> "\" Then strFdPath = strFdPath & "\"xYP检测VBA
Set rngData = Application.InputBox("请选择合作微信：qejc21名称所在的单元格区域", Type:=8)xYP检测VBA
'用户选择需要插入合作微信：qejc21的名称所在单元格范围xYP检测VBA
Set rngData = Intersect(rngData.Parent.UsedRange, rngData)xYP检测VBA
'intersect语句避免用户选择整列单元格，造成无谓运算的情况xYP检测VBA
If rngData Is Nothing Then MsgBox "选择的单元格范围不存在数据！": Exit SubxYP检测VBA
strWhere = InputBox("请输入合作微信：qejc21偏移的位置，例如上1、下1、左1、右1", , "右1")xYP检测VBA
'用户输入合作微信：qejc21相对单元格的偏移位置。xYP检测VBA
If Len(strWhere) = 0 Then Exit SubxYP检测VBA
x = Left(strWhere, 1)xYP检测VBA
'偏移的方向xYP检测VBA
If InStr("上下左右", x) = 0 Then MsgBox "你未输入偏移方位。": Exit SubxYP检测VBA
y = Val(Mid(strWhere, 2))xYP检测VBA
'偏移的值xYP检测VBA
Select Case xxYP检测VBA
Case "上"xYP检测VBA
Set rngWhere = rngData.Offset(-y, 0)xYP检测VBA
Case "下"xYP检测VBA
Set rngWhere = rngData.Offset(y, 0)xYP检测VBA
Case "左"xYP检测VBA
Set rngWhere = rngData.Offset(0, -y)xYP检测VBA
Case "右"xYP检测VBA
Set rngWhere = rngData.Offset(0, y)xYP检测VBA
End SelectxYP检测VBA
Application.ScreenUpdating = FalsexYP检测VBA
rngData.Parent.Parent.Activate '用户选定的激活工作簿xYP检测VBA
rngData.Parent.SelectxYP检测VBA
For Each shp In ActiveSheet.ShapesxYP检测VBA
'如果旧合作微信：qejc21存放在目标合作微信：qejc21存放范围则删除xYP检测VBA
If Not Intersect(rngWhere, shp.TopLeftCell) Is Nothing Then shp.DeletexYP检测VBA
NextxYP检测VBA
x = rngWhere.Row - rngData.RowxYP检测VBA
y = rngWhere.Column - rngData.ColumnxYP检测VBA
'偏移的坐标xYP检测VBA
arr = Array(".jpg", ".jpeg", ".bmp", ".png", ".gif")xYP检测VBA
'用数组变量记录五种文件格式xYP检测VBA
For Each rngEach In rngDataxYP检测VBA
'遍历选择区域的每一个单元格xYP检测VBA
strPicName = rngEach.TextxYP检测VBA
'合作微信：qejc21名称xYP检测VBA
If Len(strPicName) ThenxYP检测VBA
'如果单元格存在值xYP检测VBA
strPicPath = strFdPath & strPicNamexYP检测VBA
'合作微信：qejc21路径xYP检测VBA
b = FalsexYP检测VBA
'变量标记是否找到相关合作微信：qejc21xYP检测VBA
For i = 0 To UBound(arr)xYP检测VBA
'由于不确定用户的合作微信：qejc21格式，因此遍历合作微信：qejc21格式xYP检测VBA
If Len(Dir(strPicPath & arr(i))) ThenxYP检测VBA
'如果存在相关文件xYP检测VBA
Set shp = ActiveSheet.Shapes.AddPicture( _xYP检测VBA
strPicPath & arr(i), False, True, _xYP检测VBA
rngEach.Offset(x, y).Left + 5, _xYP检测VBA
rngEach.Offset(x, y).Top + 5, _xYP检测VBA
20, 20)xYP检测VBA
shp.SelectxYP检测VBA
With SelectionxYP检测VBA
.ShapeRange.LockAspectRatio = msoFalsexYP检测VBA
'撤销锁定合作微信：qejc21纵横比xYP检测VBA
.Height = rngEach.Offset(x, y).Height - 10 '合作微信：qejc21高度xYP检测VBA
.Width = rngEach.Offset(x, y).Width - 10 '合作微信：qejc21宽度xYP检测VBA
End WithxYP检测VBA
b = True '标记找到结果xYP检测VBA
n = n + 1 '累加找到结果的个数xYP检测VBA
Range("a1").Select: Exit For '找到结果后就可以退出文件格式循环xYP检测VBA
End IfxYP检测VBA
NextxYP检测VBA
If b = False Then k = k + 1 '如果没找到合作微信：qejc21累加个数xYP检测VBA
End IfxYP检测VBA
NextxYP检测VBA
Application.ScreenUpdating = TruexYP检测VBA
MsgBox "共处理成功" & n & "个合作微信：qejc21，另有" & k & "个非空单元格未找到对应的合作微信：qejc21。"xYP检测VBA
End SubxYP检测VBA |
