VBA and looping

[wigarth]

Hi!

I have little experience with loop functions, so I am looking for a bit of help.

I have provided a 2 part code that works fine, but the problem is that for each part of the code, I will need to change some row numbers.
I was simply wondering if i could loop the code somehow with defining a row number that increases for each run in the loop? If not i will need to make 45 parts of the same code...

The code will need to loop from the value 6 to 58
And of course, to make it complicated... The row numbers: 21-24 and 40-43 are to be skipped...

I am sure this can be done somehow, but i am not skilled enough yet to do so.
Can someone please help?

Best reggards:
Wigarth

'Code in 45 parts!!!... (Each "PART" has a designated row-number spanning from "6" to "58" in the columns "S" and "T"
'BUT: IMPORTANT!!! ROW/COLUMN (S21:T24) & (S40:T43) are supposed to be skipped somehow

'part 1 of code

AREA1_TRIP1: 'rownumber here is "6"
If ThisWorkbook.Sheets("Epost").Range("f6").Value = "" Then ' "F".value -changes in next part
GoTo AREA1_TRIP2
Else
End If

'"T".value below changes in next part
beskjed.beskjedtekst.Caption = " - Creating data for: " & ThisWorkbook.Sheets("Epost").Range("t6").Value
.Repaint

'Both "S".values below changes in next part
If ThisWorkbook.Sheets(ThisWorkbook.Sheets("Epost").Range("S6").Value).Range("y2").Value = 1 Then
ThisWorkbook.Sheets(ThisWorkbook.Sheets("Epost").Range("S6").Value).Range("A1:K34").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Sheets("Feilretting").Range("c20").Value & "Tur1-Rute1.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
GoTo AREA1_TRIP2
Else
End If

'Both "S".values below changes in next part
If ThisWorkbook.Sheets(ThisWorkbook.Sheets("Epost").Range("S6").Value).Range("y2").Value = 2 Then
ThisWorkbook.Sheets(ThisWorkbook.Sheets("Epost").Range("S6").Value).Range("A1:K70").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Sheets("Feilretting").Range("c20").Value & "Tur1-Rute1.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
GoTo AREA1_TRIP2
Else
End If

'Both "S".values below changes in next part
If ThisWorkbook.Sheets(ThisWorkbook.Sheets("Epost").Range("S6").Value).Range("y2").Value = 3 Then
ThisWorkbook.Sheets(ThisWorkbook.Sheets("Epost").Range("S6").Value).Range("A1:K105").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Sheets("Feilretting").Range("c20").Value & "Tur1-Rute1.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
GoTo AREA1_TRIP2
Else
End If

'The "S".value below changes in next part
ThisWorkbook.Sheets(ThisWorkbook.Sheets("Epost").Range("S6").Value).Range("A1:K140").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Sheets("Feilretting").Range("c20").Value & "Tur1-Rute1.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
'End of part 1


'PART 2 of code

AREA1_TRIP2: 'rownumber here is "7"
If ThisWorkbook.Sheets("Epost").Range("f7").Value = "" Then ' "F".value -changes in next part
GoTo AREA1_TRIP3
Else
End If

'"T".value below changes in next part
beskjed.beskjedtekst.Caption = " - Creating data for: " & ThisWorkbook.Sheets("Epost").Range("t7").Value
.Repaint

'Both "S".values below changes in next part
If ThisWorkbook.Sheets(ThisWorkbook.Sheets("Epost").Range("S7").Value).Range("y2").Value = 1 Then
ThisWorkbook.Sheets(ThisWorkbook.Sheets("Epost").Range("S7").Value).Range("A1:K34").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Sheets("Feilretting").Range("c20").Value & "Tur1-Rute2.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
GoTo AREA1_TRIP3
Else
End If

'Both "S".values below changes in next part
If ThisWorkbook.Sheets(ThisWorkbook.Sheets("Epost").Range("S7").Value).Range("y2").Value = 2 Then
ThisWorkbook.Sheets(ThisWorkbook.Sheets("Epost").Range("S7").Value).Range("A1:K70").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Sheets("Feilretting").Range("c20").Value & "Tur1-Rute2.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
GoTo AREA1_TRIP3
Else
End If

'Both "S".values below changes in next part
If ThisWorkbook.Sheets(ThisWorkbook.Sheets("Epost").Range("S7").Value).Range("y2").Value = 3 Then
ThisWorkbook.Sheets(ThisWorkbook.Sheets("Epost").Range("S7").Value).Range("A1:K105").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Sheets("Feilretting").Range("c20").Value & "Tur1-Rute2.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
GoTo AREA1_TRIP3
Else
End If

'The "S".value below changes in next part
ThisWorkbook.Sheets(ThisWorkbook.Sheets("Epost").Range("S7").Value).Range("A1:K140").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Sheets("Feilretting").Range("c20").Value & "Tur1-Rute2.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
'End of part 2

 

[wigarth]

First of all Thanks for all the help. I am learning new things here.

It still is not working, i feel somehow that there is some minor detail just spelled wrong or something now.

Code fails right after "IF ROWEND <>0"
And the editor marks upp all text after this and to all the "End if" in the bottom
trying to read the last part of code, but it is a bit confusing atm. To many variables

 

[wigarth]

SOLVED!

Turns out that the whole time the fact that the "sheets" was defined as "hidden" caused it to fail.
But now it works like a charm.

Thank you all so much for all the help.

 

[Alex Blakenburg]

Glad you were able to solve it. We were happy to help.