VBA Help - Two Nested For Loops

[Johnny Thunder]

Hello All,

I am working with a piece of code that does the following,

1st loop (For J): Defines my starting row on my "Data Entry" sheet and then steps down every 20 rows (Currently not doing this exactly but its what I need)

2nd loop (For K): Looks at my "Lookups" sheet Column("S2:S" & Lastrow) for a text string which is defined by "BlockCode" as my text variable

So what happens is that the first loop updates the Variable from the second loop BlockCode in cell A4 and then it was intended to step down 20 rows and using the value from the second loop update with the BlockCode string

Here is what the data looks like

Marketing Timing Model v2.8.xlsb
	A	B	C
1
2
3			Order
4	Block 1		1
5	Block 1		2
6			3
7			4
8			5
9			6
10			7
11			8
12			9
13			10
14			11
15			12
16			13
17			14
18			15
20
21
22
23
24			Order
25	Block 1		1
26	Block 1		2
27			3
28			4
29			5
30			6
31			7
32			8
33			9
34			10
35			11
36			12
37			13
38			14
39			15
41
Data Entry

So some explanation to the variables that are clear
Count = how many time the "Blocks" were created which in my test data is currently set to 4. So there are 4 full blocks of data which looks like Row 1:20
BlockCode = is a text String that is dropped to identify the block numbers, so "Block 1", "Block 2" , "Block 3", "Block 4"

Currently my kinda works, problem is I don't have the proper method of stepping down 20 rows based on the variable Count (which tells the code to only do it 4 times)
The code is also not going to the 2nd next J Statement which would update the BlockCode string. Any ideas on what I am doing wrong?

Here is my code:

Dim J as long, k as long

For j = 4 To Count * 20 'Count * 20 = 4*20 which is 100
            For k = 2 To LastR2
                BlockCode = ws3.Cells(k, 19).Value
                    If ws2.Cells(j, 1).Value = "Block 1" Then
                        ws2.Cells(j, 1).Value = BlockCode
                    End If
            Next k
            Next j

 

[Johnny Thunder]

Its a text String, but with that swap it did run without error.

Thanks for the additional pass on this.

 

[JackDanIce]

You mean the cells in column S contain values exactly like
"Block1", "Block2" in the formula bar (without the speech marks?) I'm not sure why then that swap works or runs error free.

And if it ran without error, do you mean it ran successfully or what you were expecting?

 

[Johnny Thunder]

So, you were right, I double checked and the text was formatted as a formula which is why the first run failed.

it looked something like ="Block " & T2

T2 = 1

Once I used the formula variation it fixed the code. Glad I now understand how that is different with the SpecialCells syntax.

Thanks again for all the help.

 

[JackDanIce]

No probs, glad you have something new to try and use in VBA