Oberon70
Board Regular
- Joined
- Jan 21, 2022
- Messages
- 160
- Office Version
- 365
- Platform
- Windows
Hi,
I have written the below code
Which worked great on the below test spreadsheet.
https://1drv.ms/x/s!AtcG2TZ_Kn5yovoIPSGrK_xy5ISA6w
but when I applied it to a statement that had 8000 entries it takes around 15 minutes, then for some reason from transaction 34 none of the data is moved into the rows on sheet List of Claims.
If there a way for me to enter step by step in vba when a value equal to say the reference number I now where the problem starts? or is there a much better way to accomplish what I am wanting to do?
Let me know if I need to provide anything. I will then log into my home computer as I am currently on my work laptop.
I have written the below code
VBA Code:
Option Explicit
Dim x As Double
Dim y As Double
Dim wb As Workbook
Dim wsTransc As Worksheet
Dim wsListCl As Worksheet
Dim LastRowTransc As Double
Sub Report1()
Dim NumStatemetns As Double
Dim i As Double
Dim LastRowLClms As Double
Dim StNum As String
Set wb = ThisWorkbook
Set wsTransc = wb.Sheets("Table2")
Set wsListCl = wb.Sheets("List of Claims")
NumStatemetns = 17
wsTransc.Activate
LastRowTransc = (FindLast(xlFindLastRow))
wsListCl.Activate
LastRowLClms = (FindLast(xlFindLastRow))
Debug.Print LastRowTransc
For i = 3 To LastRowLClms
Debug.Print Cells(i, 1).Value
Cells(i, 1).Select
For y = 2 To LastRowTransc
StNum = wsTransc.Cells(y, 2).Value
wsListCl.Cells(i, 10).Select
wsListCl.Cells(i, 11).Select
If wsListCl.Cells(i, 1).Value = wsTransc.Cells(y, 1) Then
Select Case StNum
Case 6879
wsListCl.Cells(i, 2) = wsTransc.Cells(y, 3).Value
wsListCl.Cells(i, 3) = wsTransc.Cells(y, 4).Value
Case 6880
wsListCl.Cells(i, 4) = wsTransc.Cells(y, 3).Value
wsListCl.Cells(i, 5) = wsTransc.Cells(y, 4).Value
Case 6881
wsListCl.Cells(i, 6) = wsTransc.Cells(y, 3).Value
wsListCl.Cells(i, 7) = wsTransc.Cells(y, 4).Value
Case 6993
wsListCl.Cells(i, 8) = wsTransc.Cells(y, 3).Value
wsListCl.Cells(i, 9) = wsTransc.Cells(y, 4).Value
Case 6995
wsListCl.Cells(i, 10) = wsTransc.Cells(y, 3).Value
wsListCl.Cells(i, 11) = wsTransc.Cells(y, 4).Value
Case 6996
wsListCl.Cells(i, 12) = wsTransc.Cells(y, 3).Value
wsListCl.Cells(i, 13) = wsTransc.Cells(y, 4).Value
Case 6997
wsListCl.Cells(i, 14) = wsTransc.Cells(y, 3).Value
wsListCl.Cells(i, 15) = wsTransc.Cells(y, 4).Value
Case 7101
wsListCl.Cells(i, 16) = wsTransc.Cells(y, 3).Value
wsListCl.Cells(i, 17) = wsTransc.Cells(y, 4).Value
Case 7102
wsListCl.Cells(i, 18) = wsTransc.Cells(y, 3).Value
wsListCl.Cells(i, 19) = wsTransc.Cells(y, 4).Value
Case 7103
wsListCl.Cells(i, 20) = wsTransc.Cells(y, 3).Value
wsListCl.Cells(i, 21) = wsTransc.Cells(y, 4).Value
Case 7209
wsListCl.Cells(i, 22) = wsTransc.Cells(y, 3).Value
wsListCl.Cells(i, 23) = wsTransc.Cells(y, 4).Value
Case 7210
wsListCl.Cells(i, 24) = wsTransc.Cells(y, 3).Value
wsListCl.Cells(i, 25) = wsTransc.Cells(y, 4).Value
Case 7211
wsListCl.Cells(i, 26) = wsTransc.Cells(y, 3).Value
wsListCl.Cells(i, 27) = wsTransc.Cells(y, 4).Value
Case 7323
wsListCl.Cells(i, 28) = wsTransc.Cells(y, 3).Value
wsListCl.Cells(i, 29) = wsTransc.Cells(y, 4).Value
Case 7433
wsListCl.Cells(i, 30) = wsTransc.Cells(y, 3).Value
wsListCl.Cells(i, 31) = wsTransc.Cells(y, 4).Value
Case 7435
wsListCl.Cells(i, 32) = wsTransc.Cells(y, 3).Value
wsListCl.Cells(i, 33) = wsTransc.Cells(y, 4).Value
End Select
End If
Next y
Next i
LastRowTransc = 0
End Sub
Which worked great on the below test spreadsheet.
https://1drv.ms/x/s!AtcG2TZ_Kn5yovoIPSGrK_xy5ISA6w
but when I applied it to a statement that had 8000 entries it takes around 15 minutes, then for some reason from transaction 34 none of the data is moved into the rows on sheet List of Claims.
If there a way for me to enter step by step in vba when a value equal to say the reference number I now where the problem starts? or is there a much better way to accomplish what I am wanting to do?
Let me know if I need to provide anything. I will then log into my home computer as I am currently on my work laptop.