Solved

Excel VBA incrementing a variable by 1 without loop or re-iteration

Posted on 2014-03-24
5
2,549 Views
Last Modified: 2014-03-25
Is this even possible? Is there a way to write this code so that it automatically increments "i" by one every line without the "i = i +1" inserted before every line? I know it's of no real consequence, it's just that I love code that's as compact and elegant as possible. Originally the code had fewer lines with numbers from 3 to 19 instead of i. But in this instance there is the possibility that a new column will get inserted before column 3 at some time, which is why I wanted to go the "i" route.

Thanks,
John
   Set lkprng = Range([A1], [A1].End(xlToRight).End(xlDown))
   i = 3
   seat_count = Application.VLookup(configIndex, lkprng, i, False)
   i = i + 1
   fclasstot = Application.VLookup(configIndex, lkprng, i, False)
   i = i + 1
   cwclasstot = Application.VLookup(configIndex, lkprng, i, False)
   i = i + 1
   wtpclasstot = Application.VLookup(configIndex, lkprng, i, False)
   i = i + 1
   wtclasstot = Application.VLookup(configIndex, lkprng, i, False)
   i = i + 1
   fclassrng1 = Application.VLookup(configIndex, lkprng, i, False)
   i = i + 1
   fclassrng = Application.VLookup(configIndex, lkprng, i, False)
   i = i + 1
   fclassLetters = Application.VLookup(configIndex, lkprng, i, False)
   i = i + 1
   cwclassrng1 = Application.VLookup(configIndex, lkprng, i, False)
   i = i + 1
   cwclassrng2 = Application.VLookup(configIndex, lkprng, i, False)
   i = i + 1
   cwclassLetters = Application.VLookup(configIndex, lkprng, i, False)
   i = i + 1
   wtpclassrng1 = Application.VLookup(configIndex, lkprng, i, False)
   i = i + 1
   wtpclassrng2 = Application.VLookup(configIndex, lkprng, i, False)
   i = i + 1
   wtpclassLetters = Application.VLookup(configIndex, lkprng, i, False)
   i = i + 1
   wtclassrng1 = Application.VLookup(configIndex, lkprng, i, False)
   i = i + 1
   wtclassrng2 = Application.VLookup(configIndex, lkprng, i, False)
   i = i + 1
   wtclassLetters = Application.VLookup(configIndex, lkprng, i, False)

Open in new window

0
Comment
Question by:gabrielPennyback
  • 2
  • 2
5 Comments
 
LVL 14

Expert Comment

by:Juan Ocasio
ID: 39951913
No real way, but you could do this:

  Set lkprng = Range([A1], [A1].End(xlToRight).End(xlDown))
   i = 3
   seat_count = Application.VLookup(configIndex, lkprng, i, False)

   fclasstot = Application.VLookup(configIndex, lkprng, i + 1, False)

   cwclasstot = Application.VLookup(configIndex, lkprng, i + 1, False)
...

Open in new window

0
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 39951919
You can set a function to increment your i as it goes, e.g.

Sub test()

   Set lkprng = Range([A1], [A1].End(xlToRight).End(xlDown))
   i = 2
   seat_count = Application.VLookup(configIndex, lkprng, nexti(i), False)
   fclasstot = Application.VLookup(configIndex, lkprng, nexti(i), False)
   cwclasstot = Application.VLookup(configIndex, lkprng, nexti(i), False)
   wtpclasstot = Application.VLookup(configIndex, lkprng, nexti(i), False)
   wtclasstot = Application.VLookup(configIndex, lkprng, nexti(i), False)
   fclassrng1 = Application.VLookup(configIndex, lkprng, nexti(i), False)
   fclassrng = Application.VLookup(configIndex, lkprng, nexti(i), False)
   fclassLetters = Application.VLookup(configIndex, lkprng, nexti(i), False)
   cwclassrng1 = Application.VLookup(configIndex, lkprng, nexti(i), False)
   cwclassrng2 = Application.VLookup(configIndex, lkprng, nexti(i), False)
   cwclassLetters = Application.VLookup(configIndex, lkprng, nexti(i), False)
   wtpclassrng1 = Application.VLookup(configIndex, lkprng, nexti(i), False)
   wtpclassrng2 = Application.VLookup(configIndex, lkprng, nexti(i), False)
   wtpclassLetters = Application.VLookup(configIndex, lkprng, nexti(i), False)
   wtclassrng1 = Application.VLookup(configIndex, lkprng, nexti(i), False)
   wtclassrng2 = Application.VLookup(configIndex, lkprng, nexti(i), False)
   wtclassLetters = Application.VLookup(configIndex, lkprng, nexti(i), False)

End Sub

Function nexti(i As Long) As Long

nexti = i + 1

End Function

Open in new window


Thomas
0
 
LVL 14

Expert Comment

by:Juan Ocasio
ID: 39951921
You could also create a function that will return an incremented value
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39951926
Edit to my code, with i set as a public variable

Public i As Long


Sub test()

   Set lkprng = Range([A1], [A1].End(xlToRight).End(xlDown))
   i = 2
   seat_count = Application.VLookup(configIndex, lkprng, nexti(), False)
   fclasstot = Application.VLookup(configIndex, lkprng, nexti(), False)
   cwclasstot = Application.VLookup(configIndex, lkprng, nexti(), False)
   wtpclasstot = Application.VLookup(configIndex, lkprng, nexti(), False)
   wtclasstot = Application.VLookup(configIndex, lkprng, nexti(), False)
   fclassrng1 = Application.VLookup(configIndex, lkprng, nexti(), False)
   fclassrng = Application.VLookup(configIndex, lkprng, nexti(), False)
   fclassLetters = Application.VLookup(configIndex, lkprng, nexti(), False)
   cwclassrng1 = Application.VLookup(configIndex, lkprng, nexti(), False)
   cwclassrng2 = Application.VLookup(configIndex, lkprng, nexti(), False)
   cwclassLetters = Application.VLookup(configIndex, lkprng, nexti(), False)
   wtpclassrng1 = Application.VLookup(configIndex, lkprng, nexti(), False)
   wtpclassrng2 = Application.VLookup(configIndex, lkprng, nexti(), False)
   wtpclassLetters = Application.VLookup(configIndex, lkprng, nexti(), False)
   wtclassrng1 = Application.VLookup(configIndex, lkprng, nexti(), False)
   wtclassrng2 = Application.VLookup(configIndex, lkprng, nexti(), False)
   wtclassLetters = Application.VLookup(configIndex, lkprng, nexti(), False)

End Sub

Function nexti() As Long

i= i + 1
nexti =i

End Function

Open in new window

0
 
LVL 1

Author Closing Comment

by:gabrielPennyback
ID: 39954841
Thanks, Thomas, Perfect! Less is More ....    

~ John
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question