allow breaks in my code?

[d0wnt0wn]

Hi guys,

Im just wondering if anyone has a solution for this. In the code below I have a part with "for I = 20 to 108" which obviously uses the values from row 20 to row 108 to produce my values.... can I break this up in anyway so that I can have like row 20 to 25 then also row 28 to 35 etc?

Thanks

Sub Products()

Dim wsFlatEstimates As Worksheet
Dim wsPO As Worksheet
Dim Lastrow As Long 'last populated row column C, products sheet
Dim i As Long 'loop variable
Dim PoRowCounter As Long 'row counter for PO sheet

Set wsFlatEstimates = Sheets("FlatEstimates")
Set wsPO = Sheets("PO")

'find last populated row, column C, product sheet
With wsFlatEstimates
Lastrow = .Range("C" & .Rows.Count).End(xlUp).Row
End With

wsPO.Select
Cells.EntireRow.Hidden = False
wsPO.Range("a18:j50").Select
Selection.ClearContents

PoRowCounter = 18 'row PoRowCounter PO sheet, assuming header row
'loop through products sheet
For i = 20 To 108
'check if column C is empty
If wsFlatEstimates.Range("C" & i).Value <> "" Then
PoRowCounter = PoRowCounter + 1
wsPO.Range("A" & PoRowCounter).Value = wsFlatEstimates.Range("c" & i).Value
wsPO.Range("c" & PoRowCounter).Value = wsFlatEstimates.Range("b" & i).Value
wsPO.Range("h" & PoRowCounter).Value = wsFlatEstimates.Range("d" & i).Value
End If
Next i

'tidy up and free memory
Set wsFlatEstimates = Nothing

End Sub

 

[ttdk1]

right under the line For i = 20 to 108, insert the line If i = 26 then i = 28

 

[Joe4]

can I break this up in anyway so that I can have like row 20 to 25 then also row 28 to 35 etc?

One way would be to take the code inside your loop, and put it in its own procedure with parameters, then in your main query, call the new procedure and pass in the parameters.
Then you include a section for each "block" you have (20-25, 28-35, ...).

If there is any rhyme or reason to your "blocks", you might be able to programNatically figure something out instead of explicitly spelling out each block. But you would need to let us know what that logic is in order for us to try to help you with that.

 

[d0wnt0wn]

right under the line For i = 20 to 108, insert the line If i = 26 then i = 28

Hi ttdk1,

I am not sure how the if statement is supposed to work here? it is more of an "and" thing...... I = 20 to 25 "and" 28 to 35 and 40 to 55 for example

 

[d0wnt0wn]

Hi Joe,

Im sorry... I should have explained that I did not make this code myself... just trying to adjust it... I didn't understand much of what you said there except no there is no rhyme or reason for which blocks are used I would have to add all of those in manually.

 

[Joe4]

In a nutshell, you would have to repeat you block for as many times as you need, i.e.

...
For i = 20 To 25
'check if column C is empty
    If wsFlatEstimates.Range("C" & i).Value <> "" Then
        PoRowCounter = PoRowCounter + 1
        wsPO.Range("A" & PoRowCounter).Value = wsFlatEstimates.Range("c" & i).Value
        wsPO.Range("c" & PoRowCounter).Value = wsFlatEstimates.Range("b" & i).Value
        wsPO.Range("h" & PoRowCounter).Value = wsFlatEstimates.Range("d" & i).Value
    End If
Next i

For i = 28 To 35
'check if column C is empty
    If wsFlatEstimates.Range("C" & i).Value <> "" Then
        PoRowCounter = PoRowCounter + 1
        wsPO.Range("A" & PoRowCounter).Value = wsFlatEstimates.Range("c" & i).Value
        wsPO.Range("c" & PoRowCounter).Value = wsFlatEstimates.Range("b" & i).Value
        wsPO.Range("h" & PoRowCounter).Value = wsFlatEstimates.Range("d" & i).Value
    End If
Next i


For i = 40 To 55
'check if column C is empty
    If wsFlatEstimates.Range("C" & i).Value <> "" Then
        PoRowCounter = PoRowCounter + 1
        wsPO.Range("A" & PoRowCounter).Value = wsFlatEstimates.Range("c" & i).Value
        wsPO.Range("c" & PoRowCounter).Value = wsFlatEstimates.Range("b" & i).Value
        wsPO.Range("h" & PoRowCounter).Value = wsFlatEstimates.Range("d" & i).Value
    End If
Next i
...

What I was saying is if you can extract the activity inside of the loop into another Procedure (let's call it "MyLoop"), then instead of repeating the code, you can just pass the parameters and call it, i.e.

Call MySpecialLoop(ProRowCounter, 20, 25)
Call MySpecialLoop(ProRowCounter, 28, 35)
Call MySpecialLoop(ProRowCounter, 40, 55)
...

You may need to declare ProRowCounter as a Global Variable to ensure it maintains you cumulative count between runs/blocks.

 

[d0wnt0wn]

hi Joe thanks a bunch...... I know its not pretty but here it is..... I understand what you mean by creating a new procedure to clean it up but for now this will work fine.

Regards,

Ken

Sub Products()

Dim wsFlatEstimates As Worksheet
Dim wsPO As Worksheet
Dim Lastrow As Long 'last populated row column C, products sheet
Dim i As Long 'loop variable
Dim PoRowCounter As Long 'row counter for PO sheet

Set wsFlatEstimates = Sheets("FlatEstimates")
Set wsPO = Sheets("PO")

'find last populated row, column C, product sheet
With wsFlatEstimates
Lastrow = .Range("C" & .Rows.Count).End(xlUp).Row
End With

wsPO.Select
Cells.EntireRow.Hidden = False
wsPO.Range("a18:j50").Select
Selection.ClearContents

PoRowCounter = 18 'row PoRowCounter PO sheet, assuming header row
'loop through products sheet
For i = 12 To 21
'check if column C is empty
If wsFlatEstimates.Range("C" & i).Value <> "" Then
PoRowCounter = PoRowCounter + 1
wsPO.Range("A" & PoRowCounter).Value = wsFlatEstimates.Range("c" & i).Value
wsPO.Range("c" & PoRowCounter).Value = wsFlatEstimates.Range("b" & i).Value
wsPO.Range("h" & PoRowCounter).Value = wsFlatEstimates.Range("d" & i).Value
End If
Next i

For i = 45 To 46
'check if column C is empty
If wsFlatEstimates.Range("C" & i).Value <> "" Then
PoRowCounter = PoRowCounter + 1
wsPO.Range("A" & PoRowCounter).Value = wsFlatEstimates.Range("c" & i).Value
wsPO.Range("c" & PoRowCounter).Value = wsFlatEstimates.Range("b" & i).Value
wsPO.Range("h" & PoRowCounter).Value = wsFlatEstimates.Range("d" & i).Value
End If
Next i


For i = 48 To 51
'check if column C is empty
If wsFlatEstimates.Range("C" & i).Value <> "" Then
PoRowCounter = PoRowCounter + 1
wsPO.Range("A" & PoRowCounter).Value = wsFlatEstimates.Range("c" & i).Value
wsPO.Range("c" & PoRowCounter).Value = wsFlatEstimates.Range("b" & i).Value
wsPO.Range("h" & PoRowCounter).Value = wsFlatEstimates.Range("d" & i).Value
End If

Next i
For i = 53 To 60
'check if column C is empty
If wsFlatEstimates.Range("C" & i).Value <> "" Then
PoRowCounter = PoRowCounter + 1
wsPO.Range("A" & PoRowCounter).Value = wsFlatEstimates.Range("c" & i).Value
wsPO.Range("c" & PoRowCounter).Value = wsFlatEstimates.Range("b" & i).Value
wsPO.Range("h" & PoRowCounter).Value = wsFlatEstimates.Range("d" & i).Value
End If

Next i

For i = 62 To 67
'check if column C is empty
If wsFlatEstimates.Range("C" & i).Value <> "" Then
PoRowCounter = PoRowCounter + 1
wsPO.Range("A" & PoRowCounter).Value = wsFlatEstimates.Range("c" & i).Value
wsPO.Range("c" & PoRowCounter).Value = wsFlatEstimates.Range("b" & i).Value
wsPO.Range("h" & PoRowCounter).Value = wsFlatEstimates.Range("d" & i).Value
End If

Next i

For i = 69 To 74
'check if column C is empty
If wsFlatEstimates.Range("C" & i).Value <> "" Then
PoRowCounter = PoRowCounter + 1
wsPO.Range("A" & PoRowCounter).Value = wsFlatEstimates.Range("c" & i).Value
wsPO.Range("c" & PoRowCounter).Value = wsFlatEstimates.Range("b" & i).Value
wsPO.Range("h" & PoRowCounter).Value = wsFlatEstimates.Range("d" & i).Value
End If

Next i

For i = 76 To 81
'check if column C is empty
If wsFlatEstimates.Range("C" & i).Value <> "" Then
PoRowCounter = PoRowCounter + 1
wsPO.Range("A" & PoRowCounter).Value = wsFlatEstimates.Range("c" & i).Value
wsPO.Range("c" & PoRowCounter).Value = wsFlatEstimates.Range("b" & i).Value
wsPO.Range("h" & PoRowCounter).Value = wsFlatEstimates.Range("d" & i).Value
End If

Next i

For i = 83 To 84
'check if column C is empty
If wsFlatEstimates.Range("C" & i).Value <> "" Then
PoRowCounter = PoRowCounter + 1
wsPO.Range("A" & PoRowCounter).Value = wsFlatEstimates.Range("c" & i).Value
wsPO.Range("c" & PoRowCounter).Value = wsFlatEstimates.Range("b" & i).Value
wsPO.Range("h" & PoRowCounter).Value = wsFlatEstimates.Range("d" & i).Value
End If

Next i

For i = 86 To 90
'check if column C is empty
If wsFlatEstimates.Range("C" & i).Value <> "" Then
PoRowCounter = PoRowCounter + 1
wsPO.Range("A" & PoRowCounter).Value = wsFlatEstimates.Range("c" & i).Value
wsPO.Range("c" & PoRowCounter).Value = wsFlatEstimates.Range("b" & i).Value
wsPO.Range("h" & PoRowCounter).Value = wsFlatEstimates.Range("d" & i).Value
End If

Next i

For i = 92 To 96
'check if column C is empty
If wsFlatEstimates.Range("C" & i).Value <> "" Then
PoRowCounter = PoRowCounter + 1
wsPO.Range("A" & PoRowCounter).Value = wsFlatEstimates.Range("c" & i).Value
wsPO.Range("c" & PoRowCounter).Value = wsFlatEstimates.Range("b" & i).Value
wsPO.Range("h" & PoRowCounter).Value = wsFlatEstimates.Range("d" & i).Value
End If

Next i

For i = 98 To 103
'check if column C is empty
If wsFlatEstimates.Range("C" & i).Value <> "" Then
PoRowCounter = PoRowCounter + 1
wsPO.Range("A" & PoRowCounter).Value = wsFlatEstimates.Range("c" & i).Value
wsPO.Range("c" & PoRowCounter).Value = wsFlatEstimates.Range("b" & i).Value
wsPO.Range("h" & PoRowCounter).Value = wsFlatEstimates.Range("d" & i).Value
End If

Next i

For i = 104 To 109
'check if column C is empty
If wsFlatEstimates.Range("C" & i).Value <> "" Then
PoRowCounter = PoRowCounter + 1
wsPO.Range("A" & PoRowCounter).Value = wsFlatEstimates.Range("c" & i).Value
wsPO.Range("c" & PoRowCounter).Value = wsFlatEstimates.Range("b" & i).Value
wsPO.Range("h" & PoRowCounter).Value = wsFlatEstimates.Range("d" & i).Value
End If


Next i
'tidy up and free memory
Set wsFlatEstimates = Nothing

End Sub

 

[ttdk1]

the if statement is like a divider between the sections.

suppose you want to do loop for 10-21, 25-36, 58-71.. then use
for i = 10 to 71
if i = 22 then i= 25
if i = 27 then i = 58
<code here="">
next i

instead of coding a loop for each sections separately, just use one loop and use if statements to divide into sections.</code>

 

[d0wnt0wn]

thank you for the explanation I will give that a try also to try and shorten my code.

regards

Ken

 

[Norie]

Ken

You could have 2 arrays with the same dimensions to store the start and end point of each loop.

Then you could loop through those arrays and use the start/end points for your main loop.


I'm probably not explaining that too well, I'll try to post an example later.