Iterate through rows and columns, compare dates and if statement true sum and print in new column

Question

this is my first question here and i can't find a solution that works for me.

I have a set of data. i need to compare the date displayed in cell 2,2 (until 2,109) with the date in the head of cell 3,1 until 54,1. If the date is the same month in the same year, I need to display in cell(s) 55,1 the value "one month before" or "the active cell -1 row".

The cells used in the if-statement refer to Dates / there are dates written in the cells. I might mistreat the dates...

Highly appreciate any help. Many thanks in advance!

My code looks like this: I don't recieve an error but the values dont make sense

Sub oneM_lag()
'
' oneM_lag Macro
'

With Application
 .Calculation = xlCalculationManual

 End With
 
 rw = 2 'counting purpose
 erw = 109
 
 cdrw = 2 'change date
 cdcl = 2
 
 dvrw = 1 'date values in head of each row
 dvcl = 3
 
 vrw = 2 'flow values
 vcl = 3
 
 fcl = 54 'final calculated flow values
 frw = 2

 
 Do While rw < erw

    If Cells(cdrw, cdcl) >= Cells(dvrw, dvcl) Then
    Cells(frw, fcl) = Cells(vrw, vcl - 1)
    
    End If
    
 rw = rw + 1
 
 cdrw = cdrw + 1
 dvcl = dvcl + 1
 frw = frw + 1
 vcl = vcl + 1
 
 Loop

 
End Sub

I tried a different code. I manually lagged the dates by 1 month so now i want to display the value in the i-th cell below the Date:

fd = 3

For i = 2 To 109

If Cells(i, 2) <= Cells(1, fd) Then
Cells(i, 54) = Cells(i, fd)
End If

fd = fd + 1
Next i

End Sub

This works but only for row 29 where it corretly identified the value ... i checked the format of my values. The code pasts other wrong values until row 52.

Answer 1

I think I'll need a bit more clarification to give you the answer you are looking for. You wrote that you need to compare the dates in the between column 2 (B) and the head of cells 3, 1 through cell 54, 1, Since you said 'head', I'm inclined to believe that you meant row 1, columns 3 through 54 or C1 through BB1. (If written in terms of how VBA interprets Cells, you've written the head of rows 3 through 54 in column A.)

I don't have enough information to provide updated VBA, yet.

After you read through this, please either update your question with the answers I need to help or place them in a comment. (If you update your question, leave a comment to so I know that you did.)

What I think you want the code to do

I believe that you have a spreadsheet that looks something like this:

	A	B	C	D	~	BB	BC
1	?	Dates	May 1, 2022	Apr 1, 2022	~	Aug 1, 2019	if true, results of first iteration
2	?	Aug 1, 2019	?	?	~	?	?
3	?	Jul 2, 2019	?	?	~	?	?

Where should the results of the second iteration go?

You've coded and inferred that this is something that needs to be iterated, but I only know that the first output value needs to be placed in cell BC1. Where does the next value go?

For example, using the example table:

First iteration

1. Compare the month and year B2: Aug 1, 2019 with dates in C1:C54.
2. The month = month & year = year match is C54: Aug 1, 2019.
3. Collect date in B2: Aug 1, 2019. Subtract one month from the date collected: July 1, 2019.
4. Place the modified date, July 1, 2019, in BC1 (the 55th column, row 1).

	A	B	C	D	~	BB	BC
1	?	Dates	May 1, 2022	Apr 1, 2022	~	Aug 1, 2019	July 1, 2019
2	?	Aug 1, 2019	?	?	~	?	?
3	?	Jul 2, 2019	?	?	~	?	?

What happens next?

• Do I compare the next value in column B with the new date in BC1?
  • If so, does the resulting match, less one month go in cell BD1?
• Your question mentions the 'true sum'; how does that come into play here?

What your code does

When I wrote that you would need to modify the if-then, I wrote what your code does.

Here is a different example table, this indicates where the values will go if the first through third iteration are true:

	A	B	C	D	E	~	BB
1	?	Dates	Aug 1, 2019	Jul 1, 2019	Jun 9, 2019	~	Aug 1, 2022
2	?	Aug 1, 2019	?	?	?	~	if true, results of first iteration
3	?	Jul 2, 2019	?	?	?	~	if true, results of second iteration
4	?	Oct 9, 2019	?	?	?	~	if true, results of third iteration

Is B2 ≥ C1? True. Then the value in B2 goes to BB2.

	A	B	C	D	E	~	BB
1	?	Dates	Aug 1, 2019	Jul 1, 2019	Jun 9, 2019	~	Aug 1, 2022
2	?	Aug 1, 2019	?	?	?	~	Aug 1, 2019
3	?	Jul 2, 2019	?	?	?	~	if true, results of second iteration
4	?	Oct 9, 2019	?	?	?	~	if true, results of third iteration

---

---

Update

I'm still not sure what you're looking for, but perhaps if I show you what your code is doing, that will help.

	A	B	C	D	E	~	BB	BC	BD
1	?	Dates	8/1/19	7/1/19	6/9/19	~	8/1/22	?	?
2	?	8/1/19	use this value if the first iteration matches	?	?	~	if matched this value changes	?	?
3	?	7/2/19	?	use this value if the second iteration matches	?	~	if matched this value changes	?	?
4	?	10/9/19	?	?	use this value if the third iteration matches	~	if matched this value changes	?	?

The first iteration: Is B2 ≤ C1? If yes, then put the value of C2 into BB2

The second iteration: Is B3 ≤ D1? If yes, then put the value of D3 into BB3

The third iteration: Is B4 ≤ E1? If yes, then put the value of E4 into BB4

I can tell you that BB is 54; BC should be 55. Although, you said that the correct value was provided in row 29...If you wanted to stick to column C's values, that means that row 29 column C and column AC have the same value?