Another bit of tidying up of Excel code, Looping through Ranges J - Q 2

[JasonEnsor]

Hey Guys,

I assure you I am almost done cleaning this code up so you will get a break from me soon. I'm trying to see if there is a better way of handling the code detailed below. Normally i would loop through it but as i am using J -Q in the range it's not possible...well to my knowledge. Any ideas?

 With NewPaymentForm
        .txtHol1.Value = Format(SwimmerDetails.Range("J" & SwimmerT.SwimmerRow), "dd/mm/yyyy")
        .txtHol2.Value = Format(SwimmerDetails.Range("K" & SwimmerT.SwimmerRow), "dd/mm/yyyy")
        .txtHol3.Value = Format(SwimmerDetails.Range("L" & SwimmerT.SwimmerRow), "dd/mm/yyyy")
        .txtHol4.Value = Format(SwimmerDetails.Range("M" & SwimmerT.SwimmerRow), "dd/mm/yyyy")
        .txtHol5.Value = Format(SwimmerDetails.Range("N" & SwimmerT.SwimmerRow), "dd/mm/yyyy")
        .txtHol6.Value = Format(SwimmerDetails.Range("O" & SwimmerT.SwimmerRow), "dd/mm/yyyy")
        .txtHol7.Value = Format(SwimmerDetails.Range("P" & SwimmerT.SwimmerRow), "dd/mm/yyyy")
        .txtHol8.Value = Format(SwimmerDetails.Range("Q" & SwimmerT.SwimmerRow), "dd/mm/yyyy")
    End With

Many thanks in advance

Jason

 

[PHV]

What about this ?

Dim iCol As Integer
With NewPaymentForm
  For iCol = 10 To 17 ' J to K
    .Controls("txtHol" & (iCol - 9)).Value = Format(SwimmerDetails.Cells(SwimmerT.SwimmerRow, iCol)), "dd/mm/yyyy")
  Next
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mintjulep]

See CHR()

CHR(65) --> "A"

CHR(74) -->"J"

If you have an integer counter

For Counter = 1 to 8
MyLetter = CHR(Counter + 73)
Next Counter

 

[JasonEnsor]

Thanks again PHV, the solution worked like a charm. Also thank you mintjulep.

Both solutions will be added to my personal knowledgebase for future reference

Regards

Jason