Using For loop to copy and paste from offset cells to specific locations in different worksheets based on the values found

huangapple go评论103阅读模式
英文:

Using For loop to copy and paste from offset cells to specific locations in different worksheets based on the values found

问题

我正在尝试编写一个宏,该宏循环遍历排序表,为找到的每个财政年度创建一个模板副本,然后将排序表中的数据复制并粘贴到相应工作表的适当单元格中。

我已经有了循环遍历数据并为每个财政年度创建工作表副本的子程序。
我的以前的问题可以在此链接找到:https://stackoverflow.com/questions/76582456/vba-nested-for-loop-to-copy-and-rename-a-worksheet-based-on-a-variable-not-work/76582950?noredirect=1#comment135027018_76582950

现在我遇到了一个新问题,我已经编写了循环遍历费用类型并返回我需要的变量以便将其粘贴到正确工作表的代码部分:财政年度(我已经将工作表命名为这个名称),财政月份决定了目标行,费用类型决定了目标列以及现金值,我想要复制和粘贴。

问题是我一直收到Runtime 1004错误:应用程序定义或对象定义错误。

我尝试了各种不同的方法:
Range(X).Value = Range (Y).Value
.Copy Destination:= X
以及.PasteSpecial Paste:=xlPasteValues

都产生相同的结果。我怀疑问题出在我声明或设置目标工作表的方式上。但我看不出我哪里出错了。

以下是代码的翻译部分:

  1. Option Explicit
  2. Public Function FY(Fdate As String) As String '此函数从日期中提取财政年度,日期格式为##.##.####
  3. Dim arDMY As Variant
  4. arDMY = Split(Fdate, ".")
  5. If arDMY(1) >= 5 Then
  6. FY = CStr(arDMY(2) + 1)
  7. Else
  8. FY = CStr(arDMY(2))
  9. End If
  10. End Function
  11. Public Function FM(Fdate As String) As String '此函数从日期中提取财政月份,日期格式为##.##.####
  12. Dim arDMY As Variant
  13. Dim TM As String
  14. arDMY = Split(Fdate, ".")
  15. If arDMY(1) >= 5 Then
  16. TM = CStr(arDMY(1) - 4)
  17. Else
  18. TM = CStr(arDMY(1) + 8)
  19. End If
  20. If arDMY(0) >= 5 Then
  21. FM = TM
  22. Else
  23. FM = TM - 1
  24. End If
  25. End Function
  26. Sub Move_stuff()
  27. '以下是函数中的变量和操作,未翻译
  28. ...
  29. End Sub

请注意,由于代码非常复杂,无法一一翻译所有内容。如果您需要进一步的帮助,请提出具体问题,我将尽力提供解决方案。

英文:

I am trying to write a macro that loops through a sort table, creates a copy of template for each financial year it finds then copies and pastes the data from the sort table into the appropriate cells on the corresponding worksheet.

I have the Sub where it loops through the data and creates a Copy of the worksheet for each financial year.
My previous problem with that can be found at: https://stackoverflow.com/questions/76582456/vba-nested-for-loop-to-copy-and-rename-a-worksheet-based-on-a-variable-not-work/76582950?noredirect=1#comment135027018_76582950

Now I a new problem, I have written the bit that loops through the Charge Types and returns the Variables I need in order to paste it to the correct Worksheet: Financial Year (Which is what I have named the worksheets), Financial Month which dictates the destination Row and Charge Type which decides the destination column and Cash which is the value I want to copy and paste.

The problem is I keep getting a Runtime 1004 Error: Application-defined or Object Defined Error.

I have tried various different methods:
Range(X).Value = Range (Y).Value
.Copy Destination:= X
and .PasteSpecial Paste:=xlPasteValues

All yield the same result. I suspect the problem is the way I'm declaring or setting the destination worksheet. But I can't see what I'm getting wrong.

  1. Option Explicit
  2. Public Function FY(Fdate As String) As String 'This function extract the Financial Year from a date in ##.##.#### format
  3. Dim arDMY As Variant
  4. arDMY = Split(Fdate, ".")
  5. If arDMY(1) >= 5 Then
  6. FY = CStr(arDMY(2) + 1)
  7. Else
  8. FY = CStr(arDMY(2))
  9. End If
  10. End Function
  11. Public Function FM(Fdate As String) As String 'This function extracts the Financial Month from a date in ##.##.#### format
  12. Dim arDMY As Variant
  13. Dim TM As String
  14. arDMY = Split(Fdate, ".")
  15. If arDMY(1) >= 5 Then
  16. TM = CStr(arDMY(1) - 4)
  17. Else
  18. TM = CStr(arDMY(1) + 8)
  19. End If
  20. If arDMY(0) >= 5 Then
  21. FM = TM
  22. Else
  23. FM = TM - 1
  24. End If
  25. End Function
  26. Sub Move_stuff()
  27. Dim cell As Range
  28. Dim Charge As Range
  29. Dim Sort_Table As Worksheet
  30. Dim C As Variant
  31. Dim D As Variant
  32. Dim Targ As Range
  33. Dim Ddate As Range
  34. Dim Charge_Amount As Range
  35. Dim Targdate As String
  36. Dim Cash As Double
  37. Dim TM As Variant
  38. Dim Pos As Variant
  39. Dim Sheet_go As String
  40. Dim Year As Variant
  41. Dim Month As Variant
  42. Set Charge = Range("Sorttable[Charge Type]")
  43. Dim CType As Object
  44. Set CType = CreateObject("Scripting.Dictionary")
  45. CType.Add "Fos", 2
  46. CType.Add "Foo", 9
  47. CType.Add "Bar", "Cash"
  48. 'real dictionary contains various types that returns the number of the column I want the data pasted in.
  49. Sheets("Sort_Table").Select 'select worksheet with data in sort table
  50. For Each cell In Charge 'loop to find charge type
  51. If Not IsEmpty(cell) Then
  52. C = cell.Value
  53. D = CType(C)
  54. Set Targ = cell.Offset(, 3)
  55. Set Ddate = cell.Offset(, 2)
  56. Set Charge_Amount = cell.Offset(, 4)
  57. Targdate = Targ.Value
  58. EDP = Ddate.Value
  59. Cash = Charge_Amount.Value
  60. Pos = 16 - FM(Targdate)
  61. 'this gives me a row where I want data to be copied to. So with R1C1 referencing the range I want to copy to is .Range(Pos, D)
  62. Sheet_go = FY(Targdate) ' This gives me the year which is what my destination worksheets have been named.
  63. If D = "Cash" Then
  64. MsgBox Cash & vbCrLf & EDP & vbCrLf & cell.Row
  65. Else
  66. MsgBox FY(Targdate) & vbCrLf & Pos & ", " & D & vbCrLf & Cash 'checks all variables are correct
  67. cell.Offset(, 4).Copy 'this only works if I don't reference the Worksheetin the range.
  68. Sheet_go.Select 'selects the destination table and this works as I can see the change in tab.
  69. Worksheets(Sheet_go).Range(Pos, D).PasteSpecial xlPasteValues
  70. 'this doesn't work returns Runtime Error 1004: Application defined or Object defined error.
  71. 'Also the xlPasteValues are wrong and don't match the value in cell.Offset(, 4)
  72. Worksheets("Sort_Table").Select
  73. End If
  74. End If
  75. Next cell
  76. End Sub

答案1

得分: 0

Range需要“A1”公式或两个Cells(x,y)的值。

如果你使用Cells(Pos,D)代替,它会工作,但是D必须有一个有效的标识符(已编辑)。

如果CType(C)返回一个空值(无匹配),那么该行将失败。

英文:

Range requires "A1" formula or two Cells(x,y) value.

If you use instead Cells(Pos,D) it works but D must have a valid identifier (edited).

If CType(C) returns an Empty value (no match) then the line fails.

huangapple
  • 本文由 发表于 2023年7月31日 21:28:10
  • 转载请务必保留本文链接:https://go.coder-hub.com/76804117.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定