New to VBA, could use a hand :)

SimonTH

New Member
Joined
Jun 24, 2015
Messages
2
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
picture1.jpg


Then a picture of what it should look like.
picture2.jpg


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?
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
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

Well-known Member
Joined
Apr 24, 2015
Messages
518
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

New Member
Joined
Jun 24, 2015
Messages
2
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.

Code:
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

Banned user
Joined
May 24, 2014
Messages
1,336

ADVERTISEMENT

@ 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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
FWIW a formula approach would have been fairly simple too.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,462
Messages
5,596,280
Members
414,051
Latest member
tabecker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top