VBA Excel Insert

Posted on 2013-09-26
Medium Priority
Last Modified: 2013-09-26
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.
Question by:W.E.B
LVL 15

Assisted Solution

unknown_routine earned 200 total points
ID: 39525758
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
LVL 10

Expert Comment

ID: 39525763

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

Author Comment

ID: 39525769
appreciate your fast help.

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 35

Accepted Solution

[ fanpages ] earned 1000 total points
ID: 39525812

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
  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.


LVL 35

Expert Comment

by:[ fanpages ]
ID: 39525819
...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.

Author Closing Comment

ID: 39525837
Thank you very much guys.
Appreciate all your time and help.
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39525991
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.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question