VBA to VLookup and then insert cell data into first blank cell (in result column)

[lookingforhelp1]

Hi,

I've started to venture into VBA to imrpove functionality of a spreadsheet I've created.

I type a comment into Sheet1 and then hit a "submit comment" button on my front end sheet (called New Database) and this places the comment into a background sheet called "data"

I have partially achieved my result, but this just puts the comment into the first empty cell found on the data sheet.

What I need this to do is almost do a VLOOKUP (the value in cell D5 on my front end sheet) to find the right row in the data sheet and then insert the comment to the first blank cell accross.

My code at present is as follows:

Sub Add_Comment_Button()

Application.ScreenUpdating = False
Dim database As Worksheet
Dim data As Worksheet
Dim Customer_selection As String



Set database = Worksheets("New Database")
Set data = Worksheets("Data")


database.range("J67").Copy
With data
.Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

database.range("J66:S66").ClearContents


End Sub

Thanks in advance!

[bulina2k]

Can you upload your file ? (after deleting the sensible/confidential data of course)

[lookingforhelp1]

Can you upload your file ? (after deleting the sensible/confidential data of course)

Hi,

I've now attached a sample file to my original post.

You will see that regardless of which customer is selected in the drop down list, when you add a comment it will only add to the first blank (or available) cell in row 1.

I need the comment to only add to the first blank cell;

a) in the correct customer line - that matches the selection
b) to start at the "comments" section (I purposely left one email address field blank for testing purposes)

[bulina2k]

check the file attached. let me know what you think.

[lookingforhelp1]

check the file attached. let me know what you think.

Hi

Many thnaks for taking a look at this for me.

It sort of works, but for some reason if I start with a "blank" set of comment cells in the data sheet - It is creating a new table column at the end and adding it to there (and not adding it to "comment 1" first)

Any ideas please?

[bulina2k]

replace the

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

[lookingforhelp1]

replace the

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

I was in process of changing numbers in the code to see if I could figure this out...

I changed this to (rr.Row, 9) upon your response and this just used the first blank cell available (for example, I left an email address field blank for testing purposes) - In my real data I have alot more information cells than the sample I sent.

So, I tried to change this to 20 and this seemed to work for the first 2 comment entried, but when I tried to add the third comment - It just overwrites comment 1



Edit - I changed this to 25, and it seems to work well on the first customer I add the comments to. However then if I selected the 2nd customer to add a comment - Ideally, I need this to add the first comment (to the first comment cell - rather than the 7th) as per screenshot (attachment below)

example.jpg


Further edit - Changing this table back into a regular range seems to have reoslved the issue!

[lookingforhelp1]

I have tried to further develop this, so rather than the "comments" box on the main front end being a vlookup into the data table - The code actually pastes data into the colums.

So on my front end I have 3 x cells (Date, User, Comment).

I have also set my spreadsheet up to call =TODAY() in one cell, and the application user in another.

These are displaying and pasting into the background data with no issue.

My issue is that I almost need to offset by 1 if the row on my front end has information in.

As at the moment, the data is just over writing the first row of the comments summary.

My apologies in advance for the code (as I imagine it not being as elagant as it could be).

I was thinking possibley something like:

If database.range("L58").Value = "" Then

database.range("L58").PasteSpecial Paste:=xlPasteValues

Else

THIS IS WHERE I NEED TO OFFSET PASTE

My current code is as follows:

Sub Add_Comment_Button()

Application.ScreenUpdating = False
Dim database As Worksheet
Dim data As Worksheet, rr As range
Dim Customer_selection As String, lc As Long
Dim User As String

Set database = Worksheets("New Database")
Set data = Worksheets("Data")



If database.range("l67").Value = "" Then
MsgBox "No Comments Entered", vbExclamation
Exit Sub
End If


database.range("L68").Copy

With data
lr = .Cells(Rows.Count, 1).End(xlUp).Row
Set rr = .range("A2:A" & lr).Find(database.range("D5"), lookat:=xlWhole)
lc = .Cells(rr.Row, 50).End(xlToLeft).Column + 1
If lc < 4 Then lc = 4

.Cells(rr.Row, lc).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

database.range("K58").PasteSpecial Paste:=xlPasteValues





database.range("L70").Copy

With data
lr = .Cells(Rows.Count, 1).End(xlUp).Row
Set rr = .range("A2:A" & lr).Find(database.range("D5"), lookat:=xlWhole)
lc = .Cells(rr.Row, 50).End(xlToLeft).Column + 1
If lc < 4 Then lc = 4

.Cells(rr.Row, lc).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

database.range("L58").PasteSpecial Paste:=xlPasteValues





database.range("L66").Copy

With data
lr = .Cells(Rows.Count, 1).End(xlUp).Row
Set rr = .range("A2:A" & lr).Find(database.range("D5"), lookat:=xlWhole)
lc = .Cells(rr.Row, 50).End(xlToLeft).Column + 1
If lc < 4 Then lc = 4

.Cells(rr.Row, lc).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

database.range("M58").PasteSpecial Paste:=xlPasteValues




End With
End With
End With




Application.CutCopyMode = False
Application.ScreenUpdating = True

database.range("L66:R66").ClearContents


End Sub

[bulina2k]

I took the liberty to rewrite tour code. From your code I noticed that you copy 3 cells values to 2 different locations so I put them into an array and pasted them all at once. Take a look and tell me what you think.

Please Login or Register  to view this content.

[lookingforhelp1]

Hello again!

Thank you for tidying up the code, it definitley gives me some good pointers in VBA code etiquette.

Regards to the code, the data is still pasting perfectly into the Data sheet in the correct blocks of 3 (Date, Application.Username, Comment) - However, on the main sheet (called New Database), it is just overwriting the first row's data. What I need is for the code to check to find the first empty row between cells K58:M100 (for example - I'm still deciding how many cells down I will be making the box).

...Perhaps eventually, I will throw in an ActiveX scrollable text box to show this data but this is definitley a good learning curve for me (being very new to VBA) - Or perhaps it would be easier to copy the data into an ActiveX text box?

[bulina2k]

I just edited my last post. Recopy it and give it a try.

[lookingforhelp1]

I'm guessing that the part that pastes the comments is:

lr = database.Cells(Rows.Count, 11).End(xlUp).Row + 1

database.Range("K"& lr & ":M" & lr)= arr()

Would I be able to set the database range to match where the comments start (K58 to be precise)?

At the moment, the comments are pasting to the very bottom of my real database.