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

 

[CSmith]

Based on what you seemed to be asking for above this should get you started starting with the For k=2 line I think is flaw in coding logic

Public Sub Johnny_Thunder()
  Dim Block, J, k As Long
  Const Count = 4 ' This is based on forum post text and given data
  For Block = 1 To Count ' Blocks to step through based on forum post
    For J = 20 * (Block - 1) + 1 To 20 * (Block - 1) + 20 ' Block ROW to look in as each BLOCK has 20 ROWS but the +1 and +20 would be the rows checked.  Your data above does not seem to be exactly 20 ROWS
      For k = 2 To LastR2 ' Not sure what you are wanting to do here and in the If statement also this section looks out of order with possible IF needs and possible desires above
        BlockCode = ws3.Cells(k, 19).Value ' Unclear needs
        If ws2.Cells(J, 1).Value = "Block 1" Then ' Unclear needs
          ws2.Cells(J, 1).Value = BlockCode ' Unclear needs
        End If
      Next k
    Next J
  Next Block
End Sub

 

[Johnny Thunder]

Thank you for your help CSmith, unfortunately the revisions look way more sound then my code but its still doing the exact same thing as my code.

The code is currently updating all the Block text with the same details over and over again until it gets to the last block placement.

For the details that you marked as unclear.

BlockCode = ws3.Cells(k, 19).Value ' Unclear needs - This defines the name of the block, so in theory as the K variables loops thru the block name should also update as such: "Block 1", "Block 2" , "Block 3", "Block 4"

If ws2.Cells(J, 1).Value = "Block 1" Then ' Unclear needs - This was a workaround to ensure that as the Code loops with the J variable that if it sees "Block 1" which is how all the blocks are initially named then process the update - This most likely can be removed because the method in which this new code works is much more elegant then what I was doing.

ws2.Cells(J, 1).Value = BlockCode ' Unclear needs - This is the piece of code that will override the current block name with the newly defined BlockCode from the k loop.

Please let me know if my explanations make more sense now. Again, I really appreciate all the help on this.

 

[Johnny Thunder]

Ok, I was able to essentially write exactly what is needed but in the least efficient method that I know. It works, tested 3 times. Sorry, if this should have been in the original post but I have been working on creating this for sometime now and just wrapped it up. Not the most savvy when it comes to loops.

ws2.Range("A4").Select 'Start position on sheets "Data Entry"

For Each Cell In ws3.Range("S2:S" & LastR2) 'Loop for the BlockCode Text Strings ("Block 1", "Block 2" , "Block 3", "Block 4") Again, this is a variable, won't always be 4 can vary based on other factors but the range will always have whatever is needed, hence the LastR2 variable. 
    If Cell.Value <> vbNullString Then 'If the BlockCode range is blank don't do anything, this is kind of redundant since there should always be a value but its there. 
        BlockCode = Cell.Value 'Defines the BlockCode text String
            ActiveCell.Value = BlockCode 'Updates the value within the Data Entry Sheet
        ActiveCell.Offset(21, 0).Select Moves the activecell down 21 rows to the next block
    End If
Next Cell 'Resets loop to get the next BlockCode Text String and Update the Block name on the "Data Entry" Sheet

 

[JackDanIce]

This is based on #4 and reduces number of loop iterations code executes:

    Dim x   As Long
    Dim r   As Range
    Dim Srow As Long: Srow = 4
    
    Application.ScreenUpdating = False
    
    With ws3
        x = .Cells(.Rows.Count, 19).End(xlUp).row
        For Each r In .Cells(2, 19).Resize(x - 1).SpecialCells(xlCellTypeConstants)
            If r.Value <> "" Then
                ws2.Cells(Srow, 1).Value = r.Value
                Srow = Srow + 21
            End If
        Next r
    End With
            
    Application.ScreenUpdating = True

 

[Johnny Thunder]

Thanks for the help Jack,

This line is erroring out?

For Each r In .Cells(2, 19).Resize(x - 1).SpecialCells(xlCellTypeConstants)

"Runtime Error 1004: Unable to get the specialcells property of the range class"

 

[JackDanIce]

Hmm may need to look syntax up (replying on mobile, away from PC)
It’s meant to loop only on cells that are non-blank and without activating ws2.Range(“A4”); generally you never need to activate a cell to change its value.

Unless anyone helps correct, I’ll post update when back at PC in few hours.

Try searching for “VBA specialcells” as a start point to correct error

 

[Johnny Thunder]

Thanks for the quick response. I removed the ".SpecialCells(xlCellTypeConstants)" as a quick workaround and now the code runs great. I know this is still probably missing the piece that is erroring out but it at least lets me move forward with the rest of the project. Thanks again

 

[JackDanIce]

No probs, glad you discovered a solution to continue with

 

[JackDanIce]

Out of curiosity, what is the contents of column S, are they values or formulae? If formula, try swapping xlCellTypeConstants with xlCellTypeFormulas

SpecialCells Method. Select Specific Cell Types Excel VBA

SpecialCells Method in Excel VBA. Select Specific Cell Types

www.ozgrid.com