Insert Rows in Excel from ms Access

[abenitez77]

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)

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​

 

[Micron]

Perhaps use Select Case block and insert accordingly, using Excel automation

Function testSelect(SentDt As Long)
Select Case True
  Case SentDt <= 30
     'code to insert 2 empty rows and sum netclaim for this dataset above
  Case SentDt > 30 And SentDt <= 60
     'code to insert 2 empty rows and sum netclaim for this dataset above
  Case SentDt > 30 And SentDt <= 60
     'code to insert 2 empty rows and sum netclaim for this dataset above
  Case SentDt > 60 And SentDt <= 90
     'code to insert 2 empty rows and sum netclaim for this dataset above
  Case Is > 90
     'code to insert 2 empty rows and sum netclaim for this dataset above
End Select

End Function

 

[abenitez77]

Perhaps use Select Case block and insert accordingly, using Excel automation

Function testSelect(SentDt As Long)
Select Case True
  Case SentDt <= 30
     'code to insert 2 empty rows and sum netclaim for this dataset above
  Case SentDt > 30 And SentDt <= 60
     'code to insert 2 empty rows and sum netclaim for this dataset above
  Case SentDt > 30 And SentDt <= 60
     'code to insert 2 empty rows and sum netclaim for this dataset above
  Case SentDt > 60 And SentDt <= 90
     'code to insert 2 empty rows and sum netclaim for this dataset above
  Case Is > 90
     'code to insert 2 empty rows and sum netclaim for this dataset above
End Select

End Function

This part is clear for me...I was having issues with the actual syntax for looping thru rows in the excel object (sheet).

 

[Micron]

There must be a gazillion posts out there about how to do that. Assuming the column in question is A and the 1st row is a header, then

For Each rng In Range("A2", Cells(Rows.count, "A").End(xlUp))

 

[abenitez77]

There must be a gazillion posts out there about how to do that. Assuming the column in question is A and the 1st row is a header, then

For Each rng In Range("A2", Cells(Rows.count, "A").End(xlUp))

gazillion and 1 now. Thanks!