Using months in VBA

[Strooprover]

Hi, I've got a list with data based on streetnames in column 2 of ws Data (=straat in dutch) and dates in ws.(cells Lastr, 1). I wanted to sum up the weights that are in Column 3 of Sheets(Data) for each month individualy.

Now I used this macro, but I have to change to smaller then and greater then values for each month. Is there an easier way to do this so I can run my macro at once without having to chance the numbers per month?

Sub permaand()
Dim ws As Worksheet
Dim ws2 As Worksheet


Set ws = Sheets("Data")
Set ws2 = Sheets("Resultaat")


For lrow = 2 To ws2.Range("A" & Rows.Count).End(xlUp).Row
straat = ws2.Cells(lrow, 1).Value
Gewicht = 0


For Lastr = 2 To ws.Range("A" & Rows.Count).End(xlUp).Row


If ws.Cells(Lastr, 2) = straat And ws.Cells(Lastr, 1) > 42917 And ws.Cells(Lastr, 1) < 42948 Then
Gewicht = Gewicht + ws.Cells(Lastr, 3)


End If
Next Lastr
ws2.Cells(lrow, 9) = Gewicht


Next lrow






End Sub

 

[shg]

  If ws.Cells(Lastr, "B") = straat And _
     ws.Cells(Lastr, "A").Value >[COLOR="#FF0000"]=[/COLOR] CDate("1 Jul 2017") And _
     ws.Cells(Lastr, "A").Value < CDate("1 Aug 2017") Then

 

[Roxxien]

If you want to find the first date of the current month and for the next one, you should use this 2 lines

DateSerial(Year(Date), Month(Date), 1)
DateAdd("m", 1, DateSerial(Year(Date), Month(Date), 1))

Just as shg highlighted, you should put >= for the first date criteria if you don't want to exclude the first day of the month.

 

[Strooprover]

Thank you both for reacting. But I was wondering if it's possible to create a line in vba where I dont have to adjust the months each time I run the macro.

For example
For i = 1 to 12

Month(i)
Something like this

 

[Roxxien]

Hum, not sure of what you wrote. You are trying to do a loop?

What I already replied to you is to have the first day of the current month and the first day of the next month.

 

[Strooprover]

First, thanks for your help.

I want to do it for it multiple months. Now I have to change the days and months manually.

My goal is to use a variable so I can run it in 1 loop.

 

[Roxxien]

Ok if you want a loop for each month, now the problem is with the year. If you work for only 1 year, I would suggest this kind of loop just as you thinked.

The real question will now be : which loop which be in the other?
1. For each month, you go through each cell
2. For each cell, you go through each month

The most logical would be first one for me.

Dim i as currency
For i=1 to 12
.........
If ws.Cells(Lastr, 2) = straat And month(ws.Cells(Lastr, 1)) = i Then
.........
Next

If on the other side, you need to work with the year as well, I would like more precision on that.