Macro for updating diary entries

[Difflam88]

Hi everyone,

Having a bit of an issue finding the solution here.

I'm trying to create a simple diary

Say I have a 2 sheet excel document

Sheet 2 contains

A B
Name Comment
John
July

Sheet 1 contains

A1 a drop down box made from Sheet 1
A2 a date field with todays date
A3 a comment box
A4 a submit button.
A5 code that will show the existing comments in Sheet 2 .... written as =VLOOKUP(A1, 'Sheet2!A2:B6,2,FALSE)
A6 code that will consolidate the existing comments in A5 with A2 & A3 .... written as =CONCATENATE(A5," - ",A2," - ",A3 " - ")

So what I need is a macro that when clicked will copy the text data from A6 of Sheet 1 and paste into the B column of Sheet 2 (depending on which name was selected from the drop down menu in Sheet 1).

I figure the way to do this would be;

*Record*
Click A1 in Sheet 1
Copy
Click Sheet 2
Click 'find'
Paste
Click enter
Click exit
Click right direction arrow once
Click paste
Click Sheet 1
*End recording*

But this doesn't seem to work as the copy and paste into the find search function doesnt seem to record at all. Any ideas on how I may do this?

[antoka05]

You could use this code:

Sub Macro1()
   Dim sh1 As Worksheet, sh2 As Worksheet
   Dim myName As String, myRange As Range
   Dim myText As String
   
   With ThisWorkbook
      Set sh1 = .Sheets(1)
      Set sh2 = .Sheets(2)
   End With
   
   'name to find
   myName = sh1.Range("a1")
   
   Set myRange = sh2.Range("a:a").Find(myName, LookIn:=xlValues)
   If Not myRange Is Nothing Then
      'name found
   
      'new comment = old comment + date + new comment
      myText = myRange.Offset(, 1) & " - " & Format(sh1.Range("A2"), "dd/mm/yyyy") & " - " & sh1.Range("a3") & " - "
      'offset ,1 = 2nd column of 2nd sheet
      myRange.Offset(, 1) = myText
   Else
      'name not found
      MsgBox ("Name not found")
   End If
End Sub

Regards,
Antonio

[Difflam88]

Thanks Antonio,

These macro codes are a little difficult to get my head around so I might have to do some reading on how to interpret what you have written here.

I'm a quick learner and I think I will be able to understand this with a day of learning macro code. Any suggestion of where to start?

[antoka05]

I tried to add other comments so it's a little more easy to understand.

Sub Macro1()
   'Here defines two variables, sh1 and sh2 for a faster reference to the first and second sheet to avoid to write each time thisworkbook.sheets(1) and so on 
   Dim sh1 As Worksheet, sh2 As Worksheet

   Dim myName As String, myRange As Range
   Dim myText As String
   
   'Here sets variables to reference to the sheets, thisworkbook is the workbook in wich there are macros so if you have more than one workbook opened not creates problems:
   With ThisWorkbook
      Set sh1 = .Sheets(1)
      Set sh2 = .Sheets(2)
   End With
   
   'In the range A1 of the first sheet there is the name to find that I put in the variables myName
   myName = sh1.Range("a1")
   
   'Here the instruction find go to find the name (that is in the variable myName) in column A (range ("a:a")) of the 2nd sheet:
   Set myRange = sh2.Range("a:a").Find(myName, LookIn:=xlValues)
   'if not found the name myRange is equal to Nothing and so tests the myRange value
   If Not myRange Is Nothing Then
      'name found
   
      'here the find instruction found the name in column A of the 2nd sheet and myRange is a the range where found the name, for example A2, A5...

      'new comment = old comment + date + new comment
      'the instruction offset(number of rows, number of columns) move from a range to another range of rows and columns specified
      'here offset (,1) move of one column on the right and take the value of the column B of the 2nd sheet
      myText = myRange.Offset(, 1) & " - " & Format(sh1.Range("A2"), "dd/mm/yyyy") & " - " & sh1.Range("a3") & " - "
      'offset ,1 = 2nd column of 2nd sheet
      'myRange was the range where the find instruction found the name, for example A5 and so myrange.offset (,1) will be the 2nd column, B5
      myRange.Offset(, 1) = myText
   Else
      'name not found
      MsgBox ("Name not found")
   End If
End Sub

I hope that new comments helped a little more you.

Best regards,
Antonio

[Difflam88]

Thanks Antonio,

That is very helpful actually, i think i've learnt quite a bit from these additional comments, i can't thank you enough!

Just one quick question, the 'Sheets' part. My workbook has about 10 different sheets. Now i can't remember the order they were created in but I do have them in a specific order now.

So sheet 1 is now technically sheet 2 (in terms of the order of the worksheet panels at the bottom of the page left to right) and I have called this Submission form
Sheet 2 is now sheet 6 (in terms of the order of the worksheet panels at the bottom of the page left to right) and I have called this 'Data Sheet'

So do i change this first part to to read;

Sub Test()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim myName As String, myRange As Range
Dim myText As String

With ThisWorkbook
Set sh1 = .Sheets(2)
Set sh2 = .Sheets(6)
End With

OR

Sub Test()
Dim sh1 As 'Submission form'!, sh2 As 'Data Sheet'!
Dim myName As String, myRange As Range
Dim myText As String

With ThisWorkbook
Set sh1 = .Sheets(2)
Set sh2 = .Sheets(6)
End With

Also the data on the second page is now in a column 90 so i assume i just change the offset from 1 to 89. Is that correct?

[antoka05]

You can referene a sheet using a number ( sheets(1)... sheets(10)) or using the sheet's name, like sheets("data sheet"), sheets("summary sheet") and so on that it's more easy to use.

To reference the 90th column you can rightly change the offset in 89 or use the 'Cells' instruction that requires row and column, like cells (number of row, number of column):

sh2.Cells (myRange.Row, 90) = myText
'or:
sh2.Cells (myRange.Row, "cl") = myText.

Please, when you paste the code on this forum, select the code and press the button with '#' so the code has the right tag.

Regards,
Antonio