Median of row with VBA, without excel built-in functions

[Technology]

Row 2 has the following values from left to right:

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

2​

2​

2​

2​

2​

2​

2​

2​

2​

2​

3​

3​

3​

3​

3​

3​

3​

3​

3​

3​

3​

3​

3​

3​

3​

3​

4​

4​

4​

4​

4​

4​

4​

4​

4​

4​

4​

4​

4​

4​

4​

5​

5​

5​

5​

5​

5​

5​

5​

5​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

8​

8​

8​

8​

8​

8​

8​

8​

8​

8​

8​

8​

8​

9​

9​

9​

9​

9​

9​

9​

9​

10​

10​

10​

10​

10​

Result needs to be placed in A3.

Any help will be greatly appreciated.

 

[mrshl9898]

Sub DoTheThing()

Dim sht As Worksheet
Dim LastCol As Long

Set sht = ActiveSheet

LastCol = sht.Cells(2, sht.Columns.Count).End(xlToLeft).Column

If LastCol Mod 2 = 1 Then

sht.Cells(3, 1) = sht.Cells(2, (LastCol / 2) + 1)

Else

sht.Cells(3, 1) = sht.Cells(2, (LastCol / 2))

End If
End Sub

 

[Peter_SSs]

I believe the code suggested in the previous post could return incorrect results. For example, try running it after changing cell BR2 to 5 instead of 4.

If your data is always 138 columns like your one sample then all you need is

Sub median_v1()
  Range("A3").Value = (Range("BQ2").Value + Range("BR2").Value) / 2
End Sub

However if the number of columns can vary then try

Sub median_v2()
  Dim LastCol As Long
  
  LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
  If LastCol Mod 2 = 1 Then
    Range("A3").Value = Cells(2, (LastCol + 1) / 2).Value
  Else
    Range("A3").Value = Application.Sum(Cells(2, LastCol / 2).Resize(, 2)) / 2
  End If
End Sub

 

[Technology]

@Peter_SSs
Your right! This is much more effective. Thank you!

 

[Peter_SSs]

@Peter_SSs
Your right! This is much more effective. Thank you!

You're welcome. Given that an answer in your related thread indicates the range is constant, presumably you can just use _v1 of my code.