Retrieve column of last non-zero value in VBA

[louiserace]

I have a spreadsheet which is created using VBA and I want to retrieve the column of the last non zero value on row 1 (range(K1:T1)). This is so I can use the column later in the code.

I am using Excel 2016.

Any help would be appreciated.

 

[steve the fish]

Heres one way:

myCol = Application.Match(10 ^ 10, Sheets("Sheet1").Range("K1:T1"))
If Not IsError(myCol) Then myCol = myCol + 10

The sheet will need changing as appropriate. The myCol variable will be an integer representing the last column with a number in the range.

 

[steve the fish]

Sorry just seen none zero. Maybe then:

myCol = Evaluate("=LOOKUP(2,1/((Sheet1!K1:T1<>0)*(ISNUMBER(Sheet1!K1:T1))),COLUMN(Sheet1!K1:T1))")

 

[louiserace]

Sorry just seen none zero. Maybe then:

myCol = Evaluate("=LOOKUP(2,1/((Sheet1!K1:T1<>0)*(ISNUMBER(Sheet1!K1:T1))),COLUMN(Sheet1!K1:T1))")

Thanks. This worked perfectly.