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! :-)

Improve this question

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Will the following section of code work if the Row contains Dates and General Text? ws.Range("1:1").NumberFormat = "dd.mm.yyyy"
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
It's not that my dates are stored as text, rather that this row has both dates and general text in it.
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
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.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.