-
How to shorten this down?
Hail all mighty VBA-geeks ,
I'm working on my first big VBA-project and since I'm almost done, i want to 'clean' up my coding a little.
There's one function that annoys more than the others:
I have stored the projects settings in the first sheet. The routine loops
through the sheet and tries to find the stored values for each variable.
Since i have around 50 variables stored in that sheet, it takes a whole lot of code. Is there a way to make this more efficient?
[vba]
Dim bytRow As Byte
bytRow = 0
Do
bytRow = bytRow + 1
Loop Until Sheet1.Cells(bytRow, 3) = "gsngExchangeRate"
gsngExchangeRate = Sheet1.Cells(bytRow, 4)
[/vba]
As you can see the procedure loops through the sheet1 in column 3 until
it can find the name of the variable (in this case 'gsngExchangeRate').
In Column 4 the variables value is stored. This usually works pretty okay, but since I have about 50 of these blocks it devours my scroll bar ^^
Thanks in advance!
-
Use Find instead of looping
[vba]
gsngExchangeRate = Columns(3).Find("gsngExchangeRate").Offset(, 1)
MsgBox gsngExchangeRate
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
But of course!!!
Thanks a lot, i owe you
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules