[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-03-24
5
Medium Priority
?
3,020 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 15

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 2000 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 15

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

834 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