0

I am new to both stackoverflow.com and VBA within Excel, so go easy on me :-)

I am looking to have a button in my excel sheet that when clicked, will search the entire or row 1 for a date (located in another cell). If it finds the date in a call in row 1, it will enter some text in the cell below it. If it doesn't find the date, it will add the date to the next free cell in the row and then add the text in the cell below that.

I know I have asked a lot and I am happy to accept partial answers as I know how to do some of the aspect of this. For example the pasting of text and such. The part I am finding difficult is the finding on the date in the entire row 1 and then finding the next blank cell if no date is found.

Any help or pointers will be highly appreciated!

However, I would be even happier if I get a response, the person also explains how the code works as I am very keen to learn VBA and use it again in the future and not just copy and paste.

Thanks in advance for any replies! :-)

braX
  • 11,506
  • 5
  • 20
  • 33
Petay87
  • 1,700
  • 5
  • 24
  • 39

1 Answers1

1

Give this a try. I've commented code in details, but if you have some questions, ask in comments:)

Sub test()
    Dim ws As Worksheet
    Dim rng As Range
    Dim targetDate As Range

    'change sheet1 to suit
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    'change address of your cell with target date
    Set targetDate = ws.Range("A4")

    'tries to find target date in first row
    Set rng = ws.Range("1:1").Find(What:=targetDate, LookAt:=xlWhole, MatchCase:=False)

    If rng Is Nothing Then
        'if nothing found - search for last non empty column
        Set rng = ws.Range("1:1").Find(What:="*", LookAt:=xlWhole, MatchCase:=False, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)           
        If rng Is Nothing Then
            'if row 1 is empty
            Set rng = ws.Range("A1")
        Else
            'take next column after last non empty
            Set rng = rng.Offset(, 1)
        End If
        'write target date
        rng = targetDate
        rng.NumberFormat = "dd.mm.yyyy"
        'write something below the date
        rng.Offset(1) = "test"
    Else
        'if date is found - write something below the date
        rng.Offset(1).Value = "test2"
    End If
End Sub
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • Will the following section of code work if the Row contains Dates and General Text? ws.Range("1:1").NumberFormat = "dd.mm.yyyy" – Petay87 Feb 20 '14 at 14:23
  • your dates formated as `General`? why don't you like `date format`? If your dates stored as text, just remove this line `ws.Range("1:1").NumberFormat = "dd.mm.yyyy"` - should work – Dmitry Pavliv Feb 20 '14 at 14:26
  • It's not that my dates are stored as text, rather that this row has both dates and general text in it. – Petay87 Feb 20 '14 at 14:41
  • Actually it's not a problem, after formatting entire row as date, text would still stored as text, while dates as date. Btw, ok, see my updated answer. Now format chanded for single cell - when we paste new date – Dmitry Pavliv Feb 20 '14 at 14:46
  • Ah, that makes sense. I'm applying it now to my worksheet and so far it all appears to be working perfectly. No doubt I will come across more questions as I think this project may become rather elaborate. For example, how to check the last cell in a row contains certain data. – Petay87 Feb 20 '14 at 14:52
  • `how to check the last cell in a row contains certain data.` - see this line `Set rng = ws.Range("1:1").Find(What:="*", LookAt:=xlWhole, MatchCase:=False, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)`. This line finds last *non empty* cell in first row and returns this cell to `rng` variable. Btw, here is good article on the topic: http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920 – Dmitry Pavliv Feb 20 '14 at 14:55
  • I see, I can then compare the contents of the variable to something of my choice. – Petay87 Feb 20 '14 at 15:47
  • yup, just use sth like this: `If rng.Value="something" Then` – Dmitry Pavliv Feb 20 '14 at 15:48