VBA Excel Insert

Hello,
can you please help,

I need to find the last Rows Used in Column "D",
then   add a 5 comments into Column "E"   2 rows after last Row found..

Thanks for any help.
Sample.xlsx
W.E.BAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

unknown_routineCommented:
Here is the VBA code. Just add a button to excel file and then add this code for the button:

Private Sub CommandButton1_Click()
 Dim i
 
 Dim counter
 
 For i = 20 To 10000
    counter = i
    If Cells(i, 4).Value = "" Then
        Exit For
    End If
 Next i
 
 'insert comments
  For j = 1 To 5
    Cells(counter + 1 + j, 5).Value = "Comment " & j
   
     Next j
   
End Sub
0
FamousMortimerCommented:
Hi,

You can use the following code on the Sheet you wish you add the comments to.

Option Explicit

Private Sub EnterComments()
    With Range("D1048576").End(xlUp)
        .Offset(3, 1).Value = "Comment 1"
        .Offset(4, 1).Value = "Comment 2"
        .Offset(5, 1).Value = "Comment 3"
        .Offset(6, 1).Value = "Comment 4"
        .Offset(7, 1).Value = "Comment 5"
    End With
End Sub

Open in new window

see attached
Sample.xlsm
0
W.E.BAuthor Commented:
Hello,
appreciate your fast help.

the comments are not the same.
each line has a different comment.

thanks
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

[ fanpages ]IT Services ConsultantCommented:
Hi,

Here is another approach.

This code is within the code module, "basQ_28250815", of the attached workbook.

Option Explicit
Public Sub Q_28250815()

  Dim lngRow                                            As Long
  
  Worksheets("Sheet1").Select
  
  lngRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row   ' This is the last row in column [D]
  
  Cells(lngRow + 3&, "E") = "Comment #1"
  Cells(lngRow + 4&, "E") = "Comment #2"
  Cells(lngRow + 5&, "E") = "Comment #3"
  Cells(lngRow + 6&, "E") = "Comment #4"
  Cells(lngRow + 7&, "E") = "Comment #5"

End Sub

Open in new window


I have taken a copy of the original [Sheet1] worksheet, & named it [Sheet1 (Original)].

The current [Sheet1] worksheet in the workbook does not have any "Comments" rows in column [E], so you can see the outcome of running the above code.

(I did note, however, in your original worksheet, that there was a gap between Comments #4 & #5.  Was that intentional?)

To execute the code, please use the [ALT]+[F8] key combination to display the "Macro" dialog box, select "Q_28250815" as the "Macro name", & then click the [Run] button.

BFN,

fp.
Q-28250815.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
...each line has a different comment

Each of us (!) has chosen to display five comments numbered from 1 to 5.

You simply need to replace the text for each Comment as shown above with the text that you require.

FamousMortimer's code, & my proposal, make that task an easier process.
0
W.E.BAuthor Commented:
Thank you very much guys.
Appreciate all your time and help.
0
[ fanpages ]IT Services ConsultantCommented:
You are very welcome.

However, did you intend to miss FamousMortimer from the allocation of points?

I think he would appreciate some feedback about his solution in any respect.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.