trying to get the last cell address based on range giving wrong value

Question

I am trying to get the last cell address from the excel sheet for merging purposes based on starting address and range using the below code.

I have starting cell address like X and would like to get the end cell address using the given range. For example, starting address is X, and the range is 7, then the end cell address would be AD.

I have tried with the below approach and I am getting wrong end cell address

private static readonly char[] BaseChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();

private static readonly Dictionary<char, int> CharValues = BaseChars
           .Select((c, i) => new { Char = c, Index = i })
           .ToDictionary(c => c.Char, c => c.Index);

public static string IntToBase(int value)
{
    int targetBase = BaseChars.Length;
    // Determine exact number of characters to use.
    char[] buffer = new char[Math.Max(
               (int)Math.Ceiling(Math.Log(value + 1, targetBase)), 1)];

    var i = buffer.Length;
    do
    {
        buffer[--i] = BaseChars[value % targetBase];
        value /= targetBase;
    }
    while (value > 0);

    return new string(buffer, i, buffer.Length - i);
}

public static int BaseToInt(string number)
{
    _ = number ?? throw new ArgumentNullException(nameof(number));
    char[] chrs = number.ToCharArray();
    int m = chrs.Length - 1;
    int n = BaseChars.Length, x;
    int result = 0;
    foreach (char c in chrs)
    {
        x = CharValues[c];
        result += x * (int)Math.Pow(n, m--);
    }
    return result;
}

public static string GetLastCellAddress(string number, int cellCount)
{
    int startVal = BaseToInt(number);
    return Enumerable.Range(startVal, cellCount).Select(i => IntToBase(i)).Last();
}

And I am using above function like as below

var environmentsLastCellAddress =  ExcelBuilderExtensions.GetLastCellAddress(startColumnAddress, spaceTypeLibraryByPropertiesCount);

The above function gives the wrong end address if I have given starting cell address like X and count is 7, and I should get the end cell address as AD instead of I am getting address as BD.

Could anyone please let me know is there anything wrong with the above code? That would be very grateful to me. Many thanks in advance!!

JohnG · Accepted Answer · 2021-11-19T17:23:58.270

I am hoping the following will work for you.

int to string reference…

Convert an Excel column number to a column name or letter:

string to int reference…

Fastest method to remove Empty rows and Columns From Excel Files using Interop

static void Main(string[] args) {
  Console.WriteLine("Start: A + 0: " + GetAddedRange("A", 0));
  Console.WriteLine("Start: A + 1: " + GetAddedRange("A", 1));
  Console.WriteLine("Start: H + 4: " + GetAddedRange("H", 4));
  Console.WriteLine("Start: AA + 26: " + GetAddedRange("AA", 26));
  Console.WriteLine("Start: BA + 11: " + GetAddedRange("BA", 11));
  Console.WriteLine("Start: CAA + 11: " + GetAddedRange("CAA", 11));
  Console.WriteLine("Start: GAA + 11: " + GetAddedRange("GAA", 11));
  Console.WriteLine("Start: Z + 11: " + GetAddedRange("Z", 11));
  Console.WriteLine("Start: Z - 10: " + GetAddedRange("Z", -10));
  Console.ReadKey();
}

private static string ColumnIndexToColumnLetter(int colIndex) {
  int div = colIndex;
  string colLetter = String.Empty;
  int mod = 0;
  while (div > 0) {
    mod = (div - 1) % 26;
    colLetter = (char)(65 + mod) + colLetter;
    div = (int)((div - mod) / 26);
  }
  return colLetter;
}

private static int ParseColHeaderToIndex(string colAdress) {
  int[] digits = new int[colAdress.Length];
  for (int i = 0; i < colAdress.Length; ++i) {
    digits[i] = Convert.ToInt32(colAdress[i]) - 64;
  }
  int mul = 1;
  int res = 0;
  for (int pos = digits.Length - 1; pos >= 0; --pos) {
    res += digits[pos] * mul;
    mul *= 26;
  }
  return res;
}

private static string GetAddedRange(string startCol, int addedRange) {
  int startingCol = ParseColHeaderToIndex(startCol);
  return ColumnIndexToColumnLetter(startingCol + addedRange);
}

Also from your second to last comment… X + 7 would be AE… not AD.

Sorry, I am getting wrong address If i have given start address is like `D` and range would be 11 I should get `N`. Instead I am getting `O` as end address. The start address should include start cell address as well. — Glory Raj, Nov 19 '21 at 17:24
Sorry, but form my calculation D + 11 = O ... D ... e,f,g,h,i,j,k,l,m,n,o ... — JohnG, Nov 19 '21 at 17:26
So if you had any letter(s) + 1… then… it should return the same letter? — JohnG, Nov 19 '21 at 17:31
Well I am sorry, but this seems rather trivial since you could easily do range-1. In addition as my last comment states, it seems odd to have A+1 returning A. A+0 should return A. — JohnG, Nov 19 '21 at 17:37

score 0 · Answer 2 · answered Nov 19 '21 at 19:53

If you are in excel, you may also let excel do the job:

Private Function GetColName(ColNr As Integer) As String
Dim parts() As String
  parts = Split(Cells(1, ColNr).Address, "$")
  GetColName = parts(1)
End Function

Private Function GetColNr(ColName As String) As Long
  GetColNr = Range(ColName + "1").Column
End Function

Sub test()
  MsgBox GetColName(GetColNr("X") + 7) 'will post AE
End Sub

You may combine this to one function and also add error handling.

trying to get the last cell address based on range giving wrong value

2 Answers2