abenitez77
Board Regular
- Joined
- Dec 30, 2004
- Messages
- 149
I have this data below and I manually insert 2 empty rows according to the criteria below. On the first empty row I sum up the netclaim according to the criteria being met. I want to be able to do this part using VBA. I insert this data from ms access using this line: loWorkSheet.Range("A4").CopyFromRecordset rs2
This is my criteria:
SentDt <= 30 Then insert 2 empty rows and sum netclaim for this dataset above
SentDt >30 AND <= 60 Then insert 2 empty rows and sum netclaim for this dataset above
SentDt >30 AND <= 60 Then insert 2 empty rows and sum netclaim for this dataset above
SentDt >60 AND <= 90 Then insert 2 empty rows and sum netclaim for this dataset above
SentDt >90 Then insert 2 empty rows and sum netclaim for this dataset above
* I am using DateDiff to determine the number of days from SentDt compared to GetDate() (IN SQL Server)
i.e. DateDiff(day, Cast([SentToVendorDate] as Date), GetDate()) <= 30 THEN 30
(see data sample below)
This is my criteria:
SentDt <= 30 Then insert 2 empty rows and sum netclaim for this dataset above
SentDt >30 AND <= 60 Then insert 2 empty rows and sum netclaim for this dataset above
SentDt >30 AND <= 60 Then insert 2 empty rows and sum netclaim for this dataset above
SentDt >60 AND <= 90 Then insert 2 empty rows and sum netclaim for this dataset above
SentDt >90 Then insert 2 empty rows and sum netclaim for this dataset above
* I am using DateDiff to determine the number of days from SentDt compared to GetDate() (IN SQL Server)
i.e. DateDiff(day, Cast([SentToVendorDate] as Date), GetDate()) <= 30 THEN 30
(see data sample below)
Date | netClaim | SentDt |
22-Apr-22 | $18,926.00 | 11-May-22 |
27-Apr-22 | $25,011.00 | 11-May-22 |
04-May-22 | $1,142.19 | 26-May-22 |
12-May-22 | $3,651.85 | 26-May-22 |
19-May-22 | $4,759.00 | 26-May-22 |
06-May-22 | $5,771.57 | 26-May-22 |
06-May-22 | $10,198.41 | 26-May-22 |
31-60 Days | $69,460.02 | |
28-Apr-22 | $105,775.01 | 06-Jun-22 |
07-Jun-22 | $1,947.00 | 15-Jun-22 |
07-Jun-22 | $2,000.00 | 15-Jun-22 |
10-Jun-22 | $2,000.00 | 15-Jun-22 |
10-Jun-22 | $7,246.00 | 15-Jun-22 |
15-Jun-22 | $3,132.64 | 22-Jun-22 |
16-Jun-22 | $5,699.46 | 22-Jun-22 |
16-Jun-22 | $7,168.50 | 22-Jun-22 |
15-Jun-22 | $8,670.34 | 22-Jun-22 |
15-Jun-22 | $16,013.83 | 22-Jun-22 |
16-Jun-22 | $18,336.30 | 22-Jun-22 |
0-30 Days | $177,989.08 |