Prev: Conditional formating of cells
Next: How to close excel worksheet after 15 mins? once it opened
From: Radhakant Panigrahi on 11 May 2010 08:27
Hi Can anybody help in making a VBA

I have column “A” and “B” and I need a comment "AUDIT"in column “C” at every
11 row of column “C”.

I have tried the below but does not work

Sub MyMacro()
Dim lngRow As Long
For lngRow = 11 To Cells(Rows.Count, "D").End(xlUp).Row Step 11
If Range("D" & lngRow).Comment Is Nothing Then
Range("D" & lngRow).AddComment "Audit"
End If
Next
End Sub

From: Mike H on 11 May 2010 08:41
Hin,

In what way doesn't it work because it looks fine to me but I would suggest
you qualify those ranges with a worksheet name

Sub MyMacro()
Dim lngRow As Long
With Sheets("Sheet2")
For lngRow = 11 To .Cells(Rows.Count, "D").End(xlUp).Row Step 11
If .Range("D" & lngRow).Comment Is Nothing Then
..Range("D" & lngRow).AddComment Text:="Audit"
End If
Next
End With
End Sub



--
Mike

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


"Radhakant Panigrahi" wrote:

> Hi Can anybody help in making a VBA
>
> I have column “A” and “B” and I need a comment "AUDIT"in column “C” at every
> 11 row of column “C”.
>
> I have tried the below but does not work
>
> Sub MyMacro()
> Dim lngRow As Long
> For lngRow = 11 To Cells(Rows.Count, "D").End(xlUp).Row Step 11
> If Range("D" & lngRow).Comment Is Nothing Then
> Range("D" & lngRow).AddComment "Audit"
> End If
> Next
> End Sub
>
From: Javed on 11 May 2010 08:50
On May 11, 5:27 pm, Radhakant Panigrahi <rkp....(a)gmail.com> wrote:
> Hi Can anybody help in making a VBA
>
> I have column “A” and “B” and I need a comment "AUDIT"in column “C” at every
> 11 row of column “C”.
>
> I have tried the below but does not work
>
> Sub MyMacro()
> Dim lngRow As Long
> For lngRow = 11 To Cells(Rows.Count, "D").End(xlUp).Row Step 11
> If Range("D" & lngRow).Comment Is Nothing Then
> Range("D" & lngRow).AddComment "Audit"
> End If
> Next
> End Sub


Sub MyMacro()

Dim lngRow As Long

For lngRow = 11 To Cells(Rows.Count, "D").End(xlUp).Row Step 10

If Range("D" & lngRow).Comment Is Nothing Then Range("D" &
lngRow).AddComment "Audit"

Next lngRow

End Sub

This will work.

are you sure that the column D contains values.Because if Column D is
not filled in then Cells(Rows.Count, "D").End(xlUp).Row will be zero



From: Radhakant Panigrahi on 11 May 2010 10:16
Hi Mike,

Thanks for your reply, the below VBA works only when there is data in column
"D" and it is not putting comment in the cell like any value. rather it is
putting comment like we use the option "inserting comment'.

Actually column "D" is blank and i need the Text "AUDIT" to be filled in the
cell of the 10th row like any other normal value that we put in cells.


regards,
rkp

"Mike H" wrote:

> Hin,
>
> In what way doesn't it work because it looks fine to me but I would suggest
> you qualify those ranges with a worksheet name
>
> Sub MyMacro()
> Dim lngRow As Long
> With Sheets("Sheet2")
> For lngRow = 11 To .Cells(Rows.Count, "D").End(xlUp).Row Step 11
> If .Range("D" & lngRow).Comment Is Nothing Then
> .Range("D" & lngRow).AddComment Text:="Audit"
> End If
> Next
> End With
> End Sub
>
>
>
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Radhakant Panigrahi" wrote:
>
> > Hi Can anybody help in making a VBA
> >
> > I have column “A” and “B” and I need a comment "AUDIT"in column “C” at every
> > 11 row of column “C”.
> >
> > I have tried the below but does not work
> >
> > Sub MyMacro()
> > Dim lngRow As Long
> > For lngRow = 11 To Cells(Rows.Count, "D").End(xlUp).Row Step 11
> > If Range("D" & lngRow).Comment Is Nothing Then
> > Range("D" & lngRow).AddComment "Audit"
> > End If
> > Next
> > End Sub
> >
From: Tom Hutchins on 11 May 2010 11:09
When I saw your post a few days ago and Jacob's reply, I wondered if you just
wanted the text AUDIT in the cell instead of a cell comment. I'm not sure if
you want something every 10th row or every 11th row because you have said it
both ways. If you want AUDIT in column D (which is otherwise empty) every
10th row, try this:

Enter AUDIT in D10. Select D1:D10. Find the fill handle at the lower right
corner of D10 (cursor changes to a small black plus sign). Holding the left
mouse button down, drag the fill handle down column D. The 9 blank cells and
the cell with AUDIT will be copied as you drag.

If you prefer a macro, here is a variation of the one already given you:

Sub MyMacro()
Dim lngRow As Long
For lngRow = 10 To Cells(Rows.Count, "A").End(xlUp).Row Step 10
Range("D" & lngRow).Value = "Audit"
Next
End Sub

Hope this helps,

Hutch

"Radhakant Panigrahi" wrote:

> Hi Mike,
>
> Thanks for your reply, the below VBA works only when there is data in column
> "D" and it is not putting comment in the cell like any value. rather it is
> putting comment like we use the option "inserting comment'.
>
> Actually column "D" is blank and i need the Text "AUDIT" to be filled in the
> cell of the 10th row like any other normal value that we put in cells.
>
>
> regards,
> rkp
>
> "Mike H" wrote:
>
> > Hin,
> >
> > In what way doesn't it work because it looks fine to me but I would suggest
> > you qualify those ranges with a worksheet name
> >
> > Sub MyMacro()
> > Dim lngRow As Long
> > With Sheets("Sheet2")
> > For lngRow = 11 To .Cells(Rows.Count, "D").End(xlUp).Row Step 11
> > If .Range("D" & lngRow).Comment Is Nothing Then
> > .Range("D" & lngRow).AddComment Text:="Audit"
> > End If
> > Next
> > End With
> > End Sub
> >
> >
> >
> > --
> > Mike
> >
> > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > introduces the fewest assumptions while still sufficiently answering the
> > question.
> >
> >
> > "Radhakant Panigrahi" wrote:
> >
> > > Hi Can anybody help in making a VBA
> > >
> > > I have column “A” and “B” and I need a comment "AUDIT"in column “C” at every
> > > 11 row of column “C”.
> > >
> > > I have tried the below but does not work
> > >
> > > Sub MyMacro()
> > > Dim lngRow As Long
> > > For lngRow = 11 To Cells(Rows.Count, "D").End(xlUp).Row Step 11
> > > If Range("D" & lngRow).Comment Is Nothing Then
> > > Range("D" & lngRow).AddComment "Audit"
> > > End If
> > > Next
> > > End Sub
> > >
 | 
Pages: 1
Prev: Conditional formating of cells
Next: How to close excel worksheet after 15 mins? once it opened