VBA问题:查找某些数据并复制到其他工作表

Question

宏停止为我工作，我不知道为什么，它应该从data_wb复制数据。床单到wbMe。data_wb应该在后台运行。没有错误，但是，数据没有查找，好像它不知道应该在"中的哪个选项卡中；Adekwatnosc"；有人知道为什么？当我追上f8时，它会直接跳起来；设置loc=。单元格。查找(what:=vDate(如果Not loc为Nothing，则执行以结束如果"；看起来在阿德克瓦特诺斯克的床单上什么也找不到。

Sub PlanKroczacyRefresh()

Dim vDate As Date
Dim wbMe As Workbook
Dim ws As Worksheet
Dim data_wb As Workbook
Dim inputbx As String
Dim loc As Range, lc As Long
Dim MyFolder As String, ThisMonth As String
Dim MyFile As String
'Zmieniamy nagłówek na short date w pliku z makrem'
Set wbMe = ActiveWorkbook
With wbMe.Sheets("input_forecast").Rows("1:1")
.Copy
.PasteSpecial Paste:=xlPasteValues
.NumberFormat = "YYYY-MM-DD"
End With

'Otwieramy plik automatycznie ze wskazanego folderu'

MyFolder = "C:UsersV1410190DocumentsFOLDERY ROBOCZE"

MyFile = Dir(MyFolder & "Kopia PKO BH Plan kroczac1*.xlsm")

If MyFile <> "" Then
Set data_wb = Workbooks.Open(MyFolder & "" & MyFile, UpdateLinks:=0)
Else
Exit Sub
End If
'Usuwamy alerty o akutliazacji itp'
Application.ThisWorkbook.UpdateLinks = xlUpdateLinksNever '2
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.AskToUpdateLinks = False
'Zmieniamy nagłówek na short date w pliku z planem kroczącym'
With data_wb.Sheets("Adekwatnosc").Rows("1:1")
.Value = .Value
.NumberFormat = "YYYY-MM-DD"
End With
'input box - wpisujemy date zgodnie z podanym formatem'
Do
inputbx = InputBox("Enter Date, FORMAT; YYYY-MM-DD", Format("YYYY-MM-DD"))
If inputbx = vbNullString Then Exit Sub
On Error Resume Next
vDate = DateValue(inputbx)
On Error GoTo 0
DateIsValid = IsDate(vDate)
If Not DateIsValid Then MsgBox "Please enter a valid date.", vbExclamation
Loop Until DateIsValid

' Wyszukujemy find daty w nagłówku -> kopiujemy -> wklejamy je do pliku z makrem we wskazane w makrze miejscu'

data_wb.Worksheets("Adekwatnosc").Activate
With data_wb.Worksheets("Adekwatnosc")
Set loc = .Cells.Find(what:=vDate)
If Not loc Is Nothing Then
lc = .Cells(loc.Row, Columns.Count).End(xlToLeft).Column
.Range(.Cells(109, loc.Column), .Cells(123, lc)).Copy
Set locPaste = wbMe.Sheets("input_forecast").Cells.Find(what:=vDate)
wbMe.Sheets("input_forecast").Cells(27, locPaste.Column).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

lc1 = .Cells(loc.Row, Columns.Count).End(xlToLeft).Column
.Range(.Cells(138, loc.Column), .Cells(138, lc1)).Copy
Set locPaste = wbMe.Sheets("input_forecast").Cells.Find(what:=vDate)
wbMe.Sheets("input_forecast").Cells(21, locPaste.Column).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False


End If
End With

Answer 1

这对我有效：

Sub Tester()
Dim vDate As Date, loc As Range, data_wb As Workbook, inputbx
Dim DateIsValid As Boolean, lc As Long

Set data_wb = ThisWorkbook

With data_wb.Sheets("Adekwatnosc").Rows("1:1")
.Value = .Value
.NumberFormat = "YYYY-MM-DD"
End With

Do
'note user does not need to follow your format to enter a valid date
inputbx = InputBox("Enter Date, FORMAT; YYYY-MM-DD", Format("YYYY-MM-DD"))
If inputbx = vbNullString Then Exit Sub
If IsDate(inputbx) Then
vDate = DateValue(inputbx)
Else
MsgBox "Please enter a valid date.", vbExclamation
vDate = 0
End If
Loop While vDate = 0

With data_wb.Worksheets("Adekwatnosc")
'specify all relevant arguments to Find().  Note *xlFormulas* for dates
Set loc = .Cells.Find(what:=vDate, lookat:=xlWhole, LookIn:=xlFormulas)
If Not loc Is Nothing Then
Debug.Print "Found at " & loc.Address
Else
Debug.Print "Not found"
End If
End With
End Sub