VB.Net: Get Cell Address from Row and Column

Question

I know this question has been asked before, but I have tried answers to both of them and neither have worked for me..

Current Code:

Dim aString As String
Dim address As String
For Each thisTriangle In triangleArray
    drawTriangle(rowToDrawIn, thisTriangle.getMarkedCell)
    aString = String.Format("Activity Name: {0}", thisTriangle.getActivityDescription)

    //here is where I am trying to get the address
    address = xlWorkSheet.Cells.Address(rowToDrawIn, thisTriangle.getMarkedCell)
    xlWorkSheet.Range(address).AddComment(aString)
Next

As you can see, I'm attempting to get the address and then put a comment in that address. I am receiving a runtime error when this code is run. thisTriangle.getMarkedCell returns an int as the column number and rowToDrawIn is the row number.

Any and all help is much appreciated.

might be helpful to include details of the runtime error and also a sample of the data involved. — ADyson, Jan 04 '17 at 16:55
If you have the row number and column number, you can use `.Cells(row, col)` instead of `.Range(address)`. — Blackwood, Jan 04 '17 at 17:20
@blackwood are you saying doing something like this `xlWorkSheet.Cells(rowToDrawIn, thisTriangle.getMarkedCell)` because that doesn't work sadly. I thought about that as well — Bob, Jan 04 '17 at 17:48
If `rowToDrawIn` is the row number and `thisTriangle.getMarkedCell` is the column number, would expect that to work. — Blackwood, Jan 04 '17 at 17:54
@blackwood it won't even let me add on `.addComment` to that code bit — Bob, Jan 04 '17 at 17:56
@cyboashu the error message is of type `System.ArgumentException` — Bob, Jan 04 '17 at 17:58

cyboashu · Accepted Answer · 2017-01-04T18:29:00.113

Assuming that rowToDrawIn and thisTriangle.getMarkedCell are both positive ints.

Your fault is here Cells.Address. If you pass Cells.Address(1,2) you are expecting to get $B$1 but actually you get $1:$10485761 and now you are attempting add comments to this range and hence the argument error.

Cells.Address(1, 2) != Cells(1, 2).Address

Change this

  address = xlWorkSheet.Cells.Address(rowToDrawIn, thisTriangle.getMarkedCell)
    xlWorkSheet.Range(address).AddComment(aString)

To

xlWorkSheet.Cells.(rowToDrawIn, thisTriangle.getMarkedCell).Comment.Delete
xlWorkSheet.Cells.(rowToDrawIn, thisTriangle.getMarkedCell).AddComment(aString)

I see where you are coming from, but I am still getting a System.NullReferenceException and it's saying Object variable or With block variable not set — Bob, Jan 04 '17 at 18:38

VB.Net: Get Cell Address from Row and Column

1 Answers1