Loop VBA and increase values in the code

[Damen]

Hi there, it has been a few years since I last posted.

Is there a way that I can loop the following code 27 times, and each time it loops I want to increment every instance of DirectDebit in the code, i.e. DirectDebit01, DirectDebit 01n etc - loop - DirectDebit02, DirectDebit 02n etc - loop - all the way to DirectDebit27? Thanks so much in advance.

'DirectDebit01
Cells.Find(What:="DirectDebit01", After:=[A1], LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext _
, MatchCase:=False).Select
Selection.Offset(0, 1).Name = "DirectDebit01n"
[DirectDebit01n].Offset(1, 0).Name = "DirectDebit01TL"
[DirectDebit01TL].Offset(11, 2).Name = "DirectDebit01BR"
[DirectDebit01TL:DirectDebit01BR].Name = "DirectDebitRange01"
ThisWorkbook.Names("DirectDebit01TL").Delete
ThisWorkbook.Names("DirectDebit01BR").Delete
myvar = Left([ThisFileName].Value, 3)
Cells.Find(What:=myvar, After:=[DirectDebit01n], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) _
.Offset(0, 2).Name = "DirectDebit01"

 

[dispelthemyth]

Possibly try a for i x to y loop and refer to the iteration number in a name like the below


So use a for i = 1 to 27 to loop

inside the loop use;

sDirectDebit = "DirectDebit" & Format(i, "00")

Then use that where ever you need to loop the name, i.e. "DirectDebit01BR" becomes sDirectDebt & "BR"

 

[Damen]

Possibly try a for i x to y loop and refer to the iteration number in a name like the below


So use a for i = 1 to 27 to loop

inside the loop use;

sDirectDebit = "DirectDebit" & Format(i, "00")

Then use that where ever you need to loop the name, i.e. "DirectDebit01BR" becomes sDirectDebt & "BR"

Thanks, I'll give that a try