CODEWITHSUNDEEP
excelvbasimplify
Kris Hackett
Kris Hackett

Reputation: 3

Reducing line count - VBA Excel

Good afternoon all,

I have very limited range of knowledge on VBA. I am using it to create a UI in the form of userforms to store data within an Excel spreadsheet.

I'm looking to reduce the size of the file. I know one way to do this is to reduce the number of lines contained within my scripts.

I need advice, without too much laughter on how to best code something like this:

T7.Value = RecordRange(1, 1).Offset(0, 8).Value
T8.Value = RecordRange(1, 1).Offset(0, 9).Value
T9.Value = RecordRange(1, 1).Offset(0, 10).Value
T10.Value = RecordRange(1, 1).Offset(0, 11).Value
T11.Value = RecordRange(1, 1).Offset(0, 12).Value
T12.Value = RecordRange(1, 1).Offset(0, 13).Value
T13.Value = RecordRange(1, 1).Offset(0, 14).Value
T14.Value = RecordRange(1, 1).Offset(0, 15).Value
T15.Value = RecordRange(1, 1).Offset(0, 16).Value

The "T##" are text boxes that load up data from the RecordRange. The data response are answers to questions that come in in the order shown. But continues for 70+ lines. If my basic knowledge serves me right... I'm thinking, there must be a way to set a range with the start point being T1 and it loops through to an end range such as T78? Instead of having individual lines for each?

I can post more code, but i'm really just looking for a way to loop this and let VBA do the leg work...

I am not easily offended so will accept all amateur comments etc hahah

Many thanks

Upvotes: 0

Views: 142

Answers (1)

Zer0Kelvin
Zer0Kelvin

Reputation: 354

Dim I As Long

For I = 7 To 15
    Me.Controls("T" & I) = RecordRange(1, 1).Offset(0, I + 1)
Next I

Upvotes: 1

Related Questions