英文:
How can i map data from one file to another based on entering a date and searching for that value in the second file
问题
Sub UpdateData()
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wbTarget As Workbook
Dim wsTarget As Worksheet
Dim sourceRow As Long
Dim targetRow As Long
Dim targetDate As Date
Dim targetDateStr As String
' 打开源文件并设置源工作表
Set wbSource = Workbooks.Open("C:\Users\E12\Desktop\FY23.xlsx") ' 用源文件的路径进行更新
Set wsSource = wbSource.Sheets("Pull in") ' 用源工作表的名称进行更新
' 打开你的文件并设置目标工作表
Set wbTarget = ThisWorkbook ' 如果你的文件不同,请进行更新
Set wsTarget = wbTarget.Sheets("Released") ' 用目标工作表的名称进行更新
' 提示用户输入一个日期
targetDateStr = Application.InputBox("输入日期 (DDMMMYY)", Type:=2)
' 将目标日期字符串转换为有效的日期格式
On Error Resume Next
targetDate = DateValue(Replace(targetDateStr, ".", "-"))
On Error GoTo 0
' 检查目标日期是否是有效日期
If IsDate(targetDate) Then
' 将目标日期格式化为 "dd-mmm-yy"
targetDateStr = Format(targetDate, "dd-mmm-yy")
' 在源文件的列 "W" 中查找基于日期和 "Release to Logistics" 值的行
Dim searchRange As Range
Set searchRange = wsSource.Columns("W")
sourceRow = 0
On Error Resume Next
sourceRow = Application.WorksheetFunction.Match(CDate(targetDate), searchRange, 0)
On Error GoTo 0
' 如果找到匹配项,则更新文件中的数据
If Not IsError(sourceRow) Then
' 将特定列从源文件映射到你的文件
wsTarget.Cells(targetRow, 1).Value = wsSource.Cells(sourceRow, 2).Value
wsTarget.Cells(targetRow, 2).Value = wsSource.Cells(sourceRow, 4).Value
wsTarget.Cells(targetRow, 3).Value = wsSource.Cells(sourceRow, 5).Value
wsTarget.Cells(targetRow, 4).Value = wsSource.Cells(sourceRow, 6).Value
wsTarget.Cells(targetRow, 5).Value = wsSource.Cells(sourceRow, 7).Value
wsTarget.Cells(targetRow, 6).Value = wsSource.Cells(sourceRow, 8).Value
wsTarget.Cells(targetRow, 7).Value = wsSource.Cells(sourceRow, 9).Value
wsTarget.Cells(targetRow, 8).Value = wsSource.Cells(sourceRow, 12).Value
wsTarget.Cells(targetRow, 9).Value = wsSource.Cells(sourceRow, 13).Value
wsTarget.Cells(targetRow, 10).Value = wsSource.Cells(sourceRow, 16).Value
wsTarget.Cells(targetRow, 11).Value = wsSource.Cells(sourceRow, 18).Value
wsTarget.Cells(targetRow, 12).Value = wsSource.Cells(sourceRow, 21).Value
wsTarget.Cells(targetRow, 13).Value = wsSource.Cells(sourceRow, 23).Value
wsTarget.Cells(targetRow, 14).Value = wsSource.Cells(sourceRow, 1).Value
MsgBox "数据更新成功。"
Else
MsgBox "未找到指定日期的数据或未找到 'Release to Logistics' 值。"
End If
Else
MsgBox "输入的日期无效。请重试。"
End If
' 关闭源文件,不保存更改
wbSource.Close SaveChanges:=False
End Sub
英文:
- the user enters a date in the target file(where the macro is)
- If user enters a date like 12jun23, it should convert it to 12-Jun-23 or any variation
- search that date in W column in source file (POR file)
- if there's a row filled with that date, map few of the columns data to my target file.
- It should map the columns like A column in my file, to B column in the source file.
I'm getting a 1004 error, stating application or object error.
Also, tried making some changes, the recent error im getting is:
When i enter the date, for example 30may23, it should format to 30-May-23, and then search for it in the file.
But its currently formatting to 30-Dec-99.
Sub UpdateData()
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wbTarget As Workbook
Dim wsTarget As Worksheet
Dim sourceRow As Long
Dim targetRow As Long
Dim targetDate As Date
Dim targetDateStr As String
' Open the source file and set the source worksheet
Set wbSource = Workbooks.Open("C:\Users\E12\Desktop\FY23.xlsx") ' Update with the path to the source file
Set wsSource = wbSource.Sheets("Pull in") ' Update with the name of the source sheet
' Open your file and set the target worksheet
Set wbTarget = ThisWorkbook ' Update if your file is different
Set wsTarget = wbTarget.Sheets("Released") ' Update with the name of the target sheet
' Prompt the user to enter a date
targetDateStr = Application.InputBox("Enter the date (DDMMMYY)", Type:=2)
' Convert the target date string to a valid date format
On Error Resume Next
targetDate = DateValue(Replace(targetDateStr, ".", "-"))
On Error GoTo 0
' Check if the target date is a valid date
If IsDate(targetDate) Then
' Format the target date to "dd-mmm-yy"
targetDateStr = Format(targetDate, "dd-mmm-yy")
' Find the row in the source file based on the date and "Release to Logistics" value
Dim searchRange As Range
Set searchRange = wsSource.Columns("W")
sourceRow = 0
On Error Resume Next
sourceRow = Application.WorksheetFunction.Match(CDate(targetDate), searchRange, 0)
On Error GoTo 0
' Update the data in your file if a match is found
If Not IsError(sourceRow) Then
' Map specific columns from the source file to your file
wsTarget.Cells(targetRow, 1).Value = wsSource.Cells(sourceRow, 2).Value
wsTarget.Cells(targetRow, 2).Value = wsSource.Cells(sourceRow, 4).Value
wsTarget.Cells(targetRow, 3).Value = wsSource.Cells(sourceRow, 5).Value
wsTarget.Cells(targetRow, 4).Value = wsSource.Cells(sourceRow, 6).Value
wsTarget.Cells(targetRow, 5).Value = wsSource.Cells(sourceRow, 7).Value
wsTarget.Cells(targetRow, 6).Value = wsSource.Cells(sourceRow, 8).Value
wsTarget.Cells(targetRow, 7).Value = wsSource.Cells(sourceRow, 9).Value
wsTarget.Cells(targetRow, 8).Value = wsSource.Cells(sourceRow, 12).Value
wsTarget.Cells(targetRow, 9).Value = wsSource.Cells(sourceRow, 13).Value
wsTarget.Cells(targetRow, 10).Value = wsSource.Cells(sourceRow, 16).Value
wsTarget.Cells(targetRow, 11).Value = wsSource.Cells(sourceRow, 18).Value
wsTarget.Cells(targetRow, 12).Value = wsSource.Cells(sourceRow, 21).Value
wsTarget.Cells(targetRow, 13).Value = wsSource.Cells(sourceRow, 23).Value
wsTarget.Cells(targetRow, 14).Value = wsSource.Cells(sourceRow, 1).Value
MsgBox "Data updated successfully."
Else
MsgBox "No data found for the specified date or 'Release to Logistics' value not found."
End If
Else
MsgBox "Invalid date entered. Please try again."
End If
' Close the source file without saving changes
wbSource.Close SaveChanges:=False
End Sub
答案1
得分: 1
Option Explicit
Sub UpdateData()
Const SRCFILE = "C:\Users\E12\Desktop\FY23.xlsx" ' 请更新为源文件的路径
Dim wbSource As Workbook, wsSource As Worksheet
Dim wbTarget As Workbook, wsTarget As Worksheet
Dim sourceRow As Variant, targetRow As Long
Dim arCol, i As Long
' 提示用户输入日期
Dim targetDate As Date, targetDateStr As String
targetDateStr = Application.InputBox("输入日期 (DD MMM YY)", Type:=2)
' 检查目标日期是否有效
If Not IsDate(targetDateStr) Then
MsgBox "'" & targetDateStr & "' 不是有效日期。请重试。", vbExclamation
Exit Sub
Else
targetDate = DateValue(targetDateStr)
' 格式化目标日期为 "dd-mmm-yy"
targetDateStr = Format(targetDate, "dd-mmm-yy")
End If
' 打开源文件并设置源工作表
Set wbSource = Workbooks.Open(SRCFILE)
Set wsSource = wbSource.Sheets("Pull in") ' 请更新为源工作表的名称
' 打开您的文件并设置目标工作表
Set wbTarget = ThisWorkbook ' 如果您的文件不同,请更新
Set wsTarget = wbTarget.Sheets("Released") ' 请更新为目标工作表的名称
targetRow = 2 ' 根据需要更新
' 根据日期和“Release to Logistics”值在源文件中查找行
Dim rngSearch As Range
Set rngSearch = wsSource.Columns("W")
sourceRow = Application.Match(CDbl(targetDate), rngSearch, 0)
' 如果找到匹配项,则更新您的文件中的数据
If Not IsError(sourceRow) Then
' 从源文件映射到您的文件的特定列
arCol = Array("B", "D", "E", "F", "G", "H", "I", "L", "M", "P", "R", "U", "W", "A")
For i = 0 To UBound(arCol)
wsTarget.Cells(targetRow, i + 1) = wsSource.Cells(sourceRow, arCol(i)).Value
Next
MsgBox targetDateStr & " 的数据已成功更新", vbInformation, "目标行 " & targetRow
Else
MsgBox targetDateStr & " 没有找到数据或未找到 'Release to Logistics' 值。"
End If
' 不保存更改关闭源文件
wbSource.Close SaveChanges:=False
End Sub
英文:
Option Explicit
Sub UpdateData()
Const SRCFILE = "C:\Users\E12\Desktop\FY23.xlsx" ' Update with the path to the source file
Dim wbSource As Workbook, wsSource As Worksheet
Dim wbTarget As Workbook, wsTarget As Worksheet
Dim sourceRow As Variant, targetRow As Long
Dim arCol, i As Long
' Prompt the user to enter a date
Dim targetDate As Date, targetDateStr As String
targetDateStr = Application.InputBox("Enter the date (DD MMM YY)", Type:=2)
' Check if the target date is a valid date
If Not IsDate(targetDateStr) Then
MsgBox "'" & targetDateStr & "' is not a valid date. Please try again.", vbExclamation
Exit Sub
Else
targetDate = DateValue(targetDateStr)
' Format the target date to "dd-mmm-yy"
targetDateStr = Format(targetDate, "dd-mmm-yy")
End If
' Open the source file and set the source worksheet
Set wbSource = Workbooks.Open(SRCFILE)
Set wsSource = wbSource.Sheets("Pull in") ' Update with the name of the source sheet
' Open your file and set the target worksheet
Set wbTarget = ThisWorkbook ' Update if your file is different
Set wsTarget = wbTarget.Sheets("Released") ' Update with the name of the target shee
targetRow = 2 ' as appropriate
' Find the row in the source file based on the date and "Release to Logistics" value
Dim rngSearch As Range
Set rngSearch = wsSource.Columns("W")
sourceRow = Application.Match(CDbl(targetDate), rngSearch, 0)
' Update the data in your file if a match is found
If Not IsError(sourceRow) Then
' Map specific columns from the source file to your file
arCol = Array("B", "D", "E", "F", "G", "H", "I", "L", "M", "P", "R", "U", "W", "A")
For i = 0 To UBound(arCol)
wsTarget.Cells(targetRow, i + 1) = wsSource.Cells(sourceRow, arCol(i)).Value
Next
MsgBox "Data updated successfully for " & targetDateStr, vbInformation, "Target Row " & targetRow
Else
MsgBox "No data found for " & targetDateStr & " or 'Release to Logistics' value not found."
End If
' Close the source file without saving changes
wbSource.Close SaveChanges:=False
End Sub
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论