Keep getting error, wont run macro! HELP!

  • Thread starter Thread starter Myles
  • Start date Start date
M

Myles

Okay so using some code i have tried and tried to get this to work in
different ways but cant figure out why it's not working


For easy sakes we will say that

intRowNum = 2
strDescription = "Comment goes here"

' This should basically have theCell return as like "D2" or whichever letter
it is

theCell = ColumnLetter(LastColumn(intRowNum)) & intRowNum
Range(theCell).AddComment (strDescription)



Function LastColumn(Row As String) As String
' This gets the last column in a row when the row is specified
LastColumn = ActiveSheet.Cells(intRowNum,
Columns.Count).End(xlToLeft).Column + 1
End Function


Function ColumnLetter(ColumnNumber As Integer) As String
' This converts the column back to the appropriate letter
If ColumnNumber > 26 Then
ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
Chr(((ColumnNumber - 1) Mod 26) + 65)
Else
ColumnLetter = Chr(ColumnNumber + 64)
End If
End Function



Basically i already know what the row is, i just need to find the last
column of that row that has no text in it, and then add my own comment to it.

Thanks for the help guys!
 
You can use cells() instead of Range() and it'll make life easier:

cells(intrownum,"D").addcomment strdescription
or
cells(intrownum,4).addcomment strdescription

So

Dim LastCol as long
with activesheet
lastcol = .cells(intrownumb,.columns.count).end(xltoleft).column
.cells(intrownum,lastcol).addcomment strdescription
'or for one cell to the right of that last column
.cells(intrownum,lastcol+1).addcomment strdescription
end with
 
Hi

This should do it

Sub test()
Dim intRowNum As Integer
intRowNum = 2
strDescription = "Comment goes here"

LastColumn = ActiveSheet.Cells(intRowNum,
Columns.Count).End(xlToLeft).Column + 1
' This should basically have theCell return as like "D2" or whichever letter
it is

theCell = Cells(intRowNum, LastColumn).Address
Range(theCell).AddComment (strDescription)
'or
' cells(intRowNum, LastColumn).AddComment(strDescription)
End Sub

Regards,
Per
 
Back
Top