New to VBA, could use a hand :)

[SimonTH]

Hi people,

I have a rather simple VBA job, but I'm very new to VBA so I need a hand.

I have multiple data sets where I need to to move the date from I.E. Column 1 row 3, and then paste in column 5, row 3 to 18, because at column 1 row 19 I get a new date.

first a picture of what it looks like right now

Then a picture of what it should look like.

The dates they should look for all begin with Mandag / Tirsdag / Onsdag / Torsdag / Fredag / Lørdag / Søndag.

is this something you can help with?

 

[DocAElstein]

Hi, SimonTH

……

I have a rather simple VBA job, …….
…..I have multiple data sets where I need to to move the date from I.E. Column 1 row 3, and then paste in column 5, row 3 to 18, because at column 1 row 19 I get a new date.

first a picture of what it looks like right now
……..
The dates they should look for all begin with Mandag / Tirsdag / Onsdag / Torsdag / Fredag / Lørdag / Søndag.
…

… Yes it is simple. So simple that even I could probably do it easily for you! But you have made it difficult for anyone to help as you have not checked out the Posting guidelines | Forum rules | FAQs etc. etc.
….. For one thing your Thread title is not very good – It does not help to make the Thread useful in the future for anyone with a similar problem to find and use it.
. You have given a very clear Before and After picture which is very good. But you have used Images which we cannot copy to a spreadsheet.
. If you care to take the time to repost those Before and After Pictures by one of the following four methods below, then if no one else does it for you I will give you a solution Tomorrow:

Alan

…………………………………………………

Notes for producing a good “Before” and “After” “Picture” in a MrExcel Thread:

. 1) - post two screenshots ( see notes for how to do that in my signature –Please do not post an image as we cannot copy that to a spreadsheet!)
. or
. 2) - Up left in the Thread editor is a Table icon. Click that, create an appropriately sized table and fill it in. (To get the table icon up in the Reply window you may need to click on the “Go Advanced” Button next to the Reply Button)
. or
.3 ) – attach a File with two sheets, - a “Before” and an “After” )
. For example send over this free thing: Box Net,
.Remember to select Share after uploading and give us the link they provide.
. or , only as a very last resort.
.4 ). – Private Message me, and I will reply with my E-mail address and you can contact me and attach a File.( To Private Message me: ---You must be logged in. ---Click on my name DocAElstein above the Picture in the left margin. ---Select Private Message. –The rest should be obvious ).

………..
. Make sure there is just enough data to make it clear what is needed, so reduce to a maximum of about 40 rows. Remember to desensitize the data If necessary – make the data up if you like, as long as it is representative of all possible scenarios and data types.
. - So the “Before” should have just your initial data.
. - The “After”, hand written by you should show exactly how it should look as a result of a Formula or code based on the exact sample data you give in the BEFORE.

 

[LloydFinancials]

I agree with the tables needing to be copy-able. That does help immensely. I am not convinced on the more specific subject, for if you look, his cryptic one got at least 300% more hits. Take it for what it's worth.

 

[SimonTH]

Thank you for the replies, sorry that I missed the posting guidelines, I however did get the solution when I discussed it with a friend.

Sub JammyPackers()
       Dtes = Array("Mandag", "Tirsdag", "Onsdag", "Torsdag", "Fredag", "Lørdag", "Søndag") ' 

   EndRow = Range("A" & Rows.Count).End(xlUp).Row
   LastDte = "Unk"
   For i = 1 To EndRow
       Cells(i, 1).Activate
       For j = LBound(Dtes) To UBound(Dtes)
           If InStr(Cells(i, 1).Value, Dtes(j)) > 0 Then
                 LastDte = Cells(i, 1).Value
                 Exit For
           End If
      Next
      Cells(i, 5).Value = LastDte ' A = 1, B = 2, C= 3, etc...
   Next

End Sub

 

[DocAElstein]

@ LloydFinancials

I agree with the tables needing to be copy-able. That does help immensely. I am not convinced on the more specific subject, for if you look, his cryptic one got at least 300% more hits. Take it for what it's worth.

…. I sort of take your point, I was just going along with the Posting guidelines | Forum rules ..
.... you might want to check out this Thread:
http://www.mrexcel.com/forum/lounge...rum-posting-guidelines-using-forum-tools.html


.....................................................

@ SimonTH

Thank you for the replies, sorry that I missed the posting guidelines, I however did get the solution when I discussed it with a friend.
……..

. Thanks for letting us know. I do appreciate that All the Posting guidelines are a bit much to take in initially, and getting the screen shots Tools up and running can also be a pain initially.
. Think you can understand that typing in all your values to test was a pain. That is why I offered ways for you to drop off a file or screenshot.
. Thanks for sharing that solution with us. A very nice compact one. I would not have thought of that Method, so I have learnt something from your contribution. Thanks

Alan
P.s. Welcome to the Board.

 

[RoryA]

FWIW a formula approach would have been fairly simple too.

 

[LloydFinancials]

Good point Doc.