CODEWITHSUNDEEP
excelvba
acmh
acmh

Reputation: 13

Select Columns using single integer in Excel VBA

How can I loop through Excel columns using a single integer as input such that if

myInteger = 1

then

X = ThisWorkbook.Sheets("Sheet").Range("B3:B253")
Y = ThisWorkbook.Sheets("Sheet").Range("C3:C253")

and if

myInteger = 2

then

X = ThisWorkbook.Sheets("Sheet").Range("D3:D253")
Y = ThisWorkbook.Sheets("Sheet").Range("E3:E253")

and so on for any value of myInteger (let's say from 1 to 1000 such that manual IF-ELSE doesn't work)?

Upvotes: 1

Views: 511

Answers (2)

David Zemens
David Zemens

Reputation: 53623

If you need to account for many values and case select/if/else are not appropriate, then try the range .Offset method.

Try:

x = ThisWorkbook.Sheets("Sheet").Range("A3:B253").Offset(0,MyInteger)
y = ThisWorkbook.Sheets("Sheet").Range("A3:B253").Offset(0,MyInteger+1)

Upvotes: 0

Santosh
Santosh

Reputation: 12353

Using select case 

Sub test()

myInteger = 1
    Select Case myInteger

    Case 1
        X = ThisWorkbook.Sheets("Sheet").Range("B3:B253")
        Y = ThisWorkbook.Sheets("Sheet").Range("C3:C253")

    Case 2


        X = ThisWorkbook.Sheets("Sheet").Range("D3:D253")
        Y = ThisWorkbook.Sheets("Sheet").Range("E3:E253")

    Case Else

        X = ThisWorkbook.Sheets("Sheet").Range("D3:D253")
        Y = ThisWorkbook.Sheets("Sheet").Range("E3:E253")
    End Select
End Sub


using If...Else..

Sub test()

    myInteger = 1
    If myInteger = 1 Then
        X = ThisWorkbook.Sheets("Sheet").Range("B3:B253")
        Y = ThisWorkbook.Sheets("Sheet").Range("C3:C253")
    ElseIf myInteger = 2 Then


        X = ThisWorkbook.Sheets("Sheet").Range("D3:D253")
        Y = ThisWorkbook.Sheets("Sheet").Range("E3:E253")

    Else

        X = ThisWorkbook.Sheets("Sheet").Range("D3:D253")
        Y = ThisWorkbook.Sheets("Sheet").Range("E3:E253")
    End If
End Sub

Upvotes: 2

Related Questions