Prev: Inserting the values of a formula using vb
Next: Excel object model reference no longer available?
From: Elton Law on 22 Apr 2010 11:02
Dear Expert,
Have 2 columns ....
Column A is name
Column B is type
I want the content in column B to put as comments in respective column A.

For example,
Name Type
Elton PT
Jasmine PT
Tony FT, 10
Kammi PT, 12

Would like to put PT as comment in cell A2 (Elton)
Would like to put PT as comment in cell A3 (Jasmine)
Would like to put FT, 10 as comment in cell A4 (Tony)

It can certainly be done manually ... by copy, right click the cell, choose
insert comment .....repeat and repeat ...
I have 1000 cells to be done.
Hope VBA can automate the task. Thanks



From: Mike H on 22 Apr 2010 11:42
Hi,

Try this macro

Sub No_Comment()
Dim c As Range
Dim LastRow As Long
Set Sht = Sheets("Sheet1") ' change to suit
LastRow = Sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Sht.Range("A2:A" & LastRow)
For Each c In MyRange
With c
If .Comment Is Nothing Then
.AddComment c.Offset(, 1).Value
End If
End With
Next
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Elton Law" wrote:

> Dear Expert,
> Have 2 columns ....
> Column A is name
> Column B is type
> I want the content in column B to put as comments in respective column A.
>
> For example,
> Name Type
> Elton PT
> Jasmine PT
> Tony FT, 10
> Kammi PT, 12
>
> Would like to put PT as comment in cell A2 (Elton)
> Would like to put PT as comment in cell A3 (Jasmine)
> Would like to put FT, 10 as comment in cell A4 (Tony)
>
> It can certainly be done manually ... by copy, right click the cell, choose
> insert comment .....repeat and repeat ...
> I have 1000 cells to be done.
> Hope VBA can automate the task. Thanks
>
>
>
From: Elton Law on 22 Apr 2010 21:47
Hi Mike,
It does not work.

Stop here .... debug ... highlight this part.
Can you help?

..AddComment c.Offset(, 1).Value
Thanks


"Mike H" wrote:

> Hi,
>
> Try this macro
>
> Sub No_Comment()
> Dim c As Range
> Dim LastRow As Long
> Set Sht = Sheets("Sheet1") ' change to suit
> LastRow = Sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
> Set MyRange = Sht.Range("A2:A" & LastRow)
> For Each c In MyRange
> With c
> If .Comment Is Nothing Then
> .AddComment c.Offset(, 1).Value
> End If
> End With
> Next
> End Sub
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Elton Law" wrote:
>
> > Dear Expert,
> > Have 2 columns ....
> > Column A is name
> > Column B is type
> > I want the content in column B to put as comments in respective column A.
> >
> > For example,
> > Name Type
> > Elton PT
> > Jasmine PT
> > Tony FT, 10
> > Kammi PT, 12
> >
> > Would like to put PT as comment in cell A2 (Elton)
> > Would like to put PT as comment in cell A3 (Jasmine)
> > Would like to put FT, 10 as comment in cell A4 (Tony)
> >
> > It can certainly be done manually ... by copy, right click the cell, choose
> > insert comment .....repeat and repeat ...
> > I have 1000 cells to be done.
> > Hope VBA can automate the task. Thanks
> >
> >
> >
From: Dave Peterson on 23 Apr 2010 08:17
Mike's code worked ok for me (well, after I dimmed a couple more variables,
<vbg>).

So...

Did you change his code?
Is your worksheet protected?
Do you have errors in those adjacent cells?

If you changed his code, post your new version.
If your worksheet is protected, unprotect it first (either in code or manually)
and try again.
If you have errors in those adjacent cells, try using:
..AddComment c.Offset(, 1).Text



Elton Law wrote:
>
> Hi Mike,
> It does not work.
>
> Stop here .... debug ... highlight this part.
> Can you help?
>
> .AddComment c.Offset(, 1).Value
> Thanks
>
> "Mike H" wrote:
>
> > Hi,
> >
> > Try this macro
> >
> > Sub No_Comment()
> > Dim c As Range
> > Dim LastRow As Long
> > Set Sht = Sheets("Sheet1") ' change to suit
> > LastRow = Sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
> > Set MyRange = Sht.Range("A2:A" & LastRow)
> > For Each c In MyRange
> > With c
> > If .Comment Is Nothing Then
> > .AddComment c.Offset(, 1).Value
> > End If
> > End With
> > Next
> > End Sub
> > --
> > Mike
> >
> > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > introduces the fewest assumptions while still sufficiently answering the
> > question.
> >
> >
> > "Elton Law" wrote:
> >
> > > Dear Expert,
> > > Have 2 columns ....
> > > Column A is name
> > > Column B is type
> > > I want the content in column B to put as comments in respective column A.
> > >
> > > For example,
> > > Name Type
> > > Elton PT
> > > Jasmine PT
> > > Tony FT, 10
> > > Kammi PT, 12
> > >
> > > Would like to put PT as comment in cell A2 (Elton)
> > > Would like to put PT as comment in cell A3 (Jasmine)
> > > Would like to put FT, 10 as comment in cell A4 (Tony)
> > >
> > > It can certainly be done manually ... by copy, right click the cell, choose
> > > insert comment .....repeat and repeat ...
> > > I have 1000 cells to be done.
> > > Hope VBA can automate the task. Thanks
> > >
> > >
> > >

--

Dave Peterson
From: Elton Law on 23 Apr 2010 11:07
Thanks mike and dave ....
It works now. Thanks ....

"Dave Peterson" wrote:

> Mike's code worked ok for me (well, after I dimmed a couple more variables,
> <vbg>).
>
> So...
>
> Did you change his code?
> Is your worksheet protected?
> Do you have errors in those adjacent cells?
>
> If you changed his code, post your new version.
> If your worksheet is protected, unprotect it first (either in code or manually)
> and try again.
> If you have errors in those adjacent cells, try using:
> ..AddComment c.Offset(, 1).Text
>
>
>
> Elton Law wrote:
> >
> > Hi Mike,
> > It does not work.
> >
> > Stop here .... debug ... highlight this part.
> > Can you help?
> >
> > .AddComment c.Offset(, 1).Value
> > Thanks
> >
> > "Mike H" wrote:
> >
> > > Hi,
> > >
> > > Try this macro
> > >
> > > Sub No_Comment()
> > > Dim c As Range
> > > Dim LastRow As Long
> > > Set Sht = Sheets("Sheet1") ' change to suit
> > > LastRow = Sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
> > > Set MyRange = Sht.Range("A2:A" & LastRow)
> > > For Each c In MyRange
> > > With c
> > > If .Comment Is Nothing Then
> > > .AddComment c.Offset(, 1).Value
> > > End If
> > > End With
> > > Next
> > > End Sub
> > > --
> > > Mike
> > >
> > > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > > introduces the fewest assumptions while still sufficiently answering the
> > > question.
> > >
> > >
> > > "Elton Law" wrote:
> > >
> > > > Dear Expert,
> > > > Have 2 columns ....
> > > > Column A is name
> > > > Column B is type
> > > > I want the content in column B to put as comments in respective column A.
> > > >
> > > > For example,
> > > > Name Type
> > > > Elton PT
> > > > Jasmine PT
> > > > Tony FT, 10
> > > > Kammi PT, 12
> > > >
> > > > Would like to put PT as comment in cell A2 (Elton)
> > > > Would like to put PT as comment in cell A3 (Jasmine)
> > > > Would like to put FT, 10 as comment in cell A4 (Tony)
> > > >
> > > > It can certainly be done manually ... by copy, right click the cell, choose
> > > > insert comment .....repeat and repeat ...
> > > > I have 1000 cells to be done.
> > > > Hope VBA can automate the task. Thanks
> > > >
> > > >
> > > >
>
> --
>
> Dave Peterson
> .
>
 | 
Pages: 1
Prev: Inserting the values of a formula using vb
Next: Excel object model reference no longer available?