Excel help please

  • RyanDS 14 Sep 2018 11:26:12 12,175 posts
    Seen 52 seconds ago
    Registered 10 years ago
    Fucked if I can find the old thread. Google / forum search doesn't show it, I assume a poster got banned and took the thread with them.

    Anyway, I just need some simple VBA to throw into a macro for work.

    The entirety of what I need is:

    Look at cell $F$2 (This will be a number 1-52)

    Copy the whole row that is that number +8
    Paste whole row as Values.
    Update the cell in column AK in that row with "Yes"
    If possible lock down all cells so they can't be amended.

    So if cell F2 is 7, copy and paste row 15 and strip out the formulas.

    It is annoying me, as I am sure this should be very simple, but I am missing something in my skillset to achieve this. (Basicaly the point is each week I want someone to press a button to lock down the data so the old stuff doesn't get amended.)

    Any ideas kind people?

    Edited by RyanDS at 11:28:33 14-09-2018
  • Doozler 14 Sep 2018 14:57:37 100 posts
    Seen 2 days ago
    Registered 5 years ago
    How about this? Doesn't protect cells as yet, but should do the rest.




    Sub main()
    Dim rn As Integer
    'read in row to copy
    rn = ActiveSheet.Cells(2, 6)
    'copy and paste row as values
    ActiveSheet.Rows(rn).EntireRow.Copy
    ActiveSheet.Cells(rn, 1).PasteSpecial Paste:=xlPasteValues
    'amend column AK to "yes"
    ActiveSheet.Cells(rn, 37) = "yes"
    'clear clipboard
    Application.CutCopyMode = False
    End Sub
  • Doozler 14 Sep 2018 15:06:51 100 posts
    Seen 2 days ago
    Registered 5 years ago
    Locking the cells is a bit weird, as by default I believe all the cells are "locked" but the worksheet is not "protected". So I think you would have to:

    Unlock all the cells
    Password protect the worksheet
    Then lock the individual rows in VBA

    Problem is I don't think the copy rows approach will work... let me have a think.
  • RyanDS 14 Sep 2018 15:18:29 12,175 posts
    Seen 52 seconds ago
    Registered 10 years ago
    Dude! You are a fucking legend! Thank you.
  • Doozler 14 Sep 2018 15:41:54 100 posts
    Seen 2 days ago
    Registered 5 years ago
    Ok, the problem is that you need to protect a woksheet for Locking to have any effec- and then all the cells are locked by default.

    So:

    First UNLOCK all cells that a user might need to change (eg, all columns 1 - AK)

    Then use the code below. You'll end up with a sheet protected by the password "password". Obviously this is not super-secure - anyone who can see the caro code can see the password - but might be good enough for accidental error, which I presume is what you're worried about.



    Sub main()
    Dim rn As Integer
    Dim i As Integer


    'read in row to copy
    rn = ActiveSheet.Cells(2, 6)
    'copy and paste row as values
    ActiveSheet.Unprotect "password"

    'amend column AK to "yes"
    ActiveSheet.Cells(rn, 37) = "yes"
    ActiveSheet.Range(Cells(rn, 37), Cells(rn, 37)).Locked = True

    For i = 1 To 36
    ActiveSheet.Cells(rn, i).Copy
    ActiveSheet.Cells(rn, i).PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Cells(rn, i).Locked = True
    Next


    'clear clipboard
    Application.CutCopyMode = False
    ActiveSheet.Protect "password", True, True

    End Sub
  • Doozler 18 Sep 2018 15:30:24 100 posts
    Seen 2 days ago
    Registered 5 years ago
    @RyanDS Any use?
  • RyanDS 18 Sep 2018 16:33:24 12,175 posts
    Seen 52 seconds ago
    Registered 10 years ago
    @Doozler

    As above, it worked perfectly (well one small tweak on the rows.)

    Thank you so much!
  • JanetSnakehole 19 Sep 2018 07:47:25 459 posts
    Seen 23 minutes ago
    Registered 4 months ago
    ISSUE
    S
    S
    U
    E

    Edit: this made sense earlier before a banning.

    Edited by JanetSnakehole at 13:26:15 19-09-2018
  • Doozler 19 Sep 2018 14:52:24 100 posts
    Seen 2 days ago
    Registered 5 years ago
    @RyanDS Great. It passed the time on a slow friday afternoon!
  • RyanDS 28 Sep 2018 12:04:31 12,175 posts
    Seen 52 seconds ago
    Registered 10 years ago
    EDIT: I am a fucking moron. My yes was YES in the macro, but lower case in the cell. All works now. Typing the above made me realise.

    Leaving this here anyway for the (interesting to me) note at the bottom about the Wait functions resolving random issues.

    Hi All again.

    Just a quick one this time (and thanks again so much Doozler, working back from your VBA I managed to learn a lot.)

    I have the following code, all it does is look at a cell (which states if macro has been run previous), if it says Yes then I want a pop up saying "Already Posted", if No, then run the macro.

    It works fine, in that if the macro has run previous for this period it skips to end and does nothing, if macro has not run then it des what it should. but why does the msg box not appear?


    Sub PayrollPost()
    If Range("G2").Value = "YES" Then
    Application.Wait Now() + TimeValue("00:00:01")
    MsgBox "Already Posted"
    ElseIf Range("G2").Value = "No" Then
    Application.Wait Now() + TimeValue("00:00:01")



    End If
    End Sub
    Also as a random aside. The entire macro (not posted in full) only works 50% of the time. It is utterly random. F8 through the macro works perfectly, 100% success rate. But when running in full it skips sections. I have no idea why, it is really simple stuff, just some copy and pasting and amending some cells. Googling says that sometimes Excel gets confused and starts the next step before completing the previous which is what can cause this. My laptop is pretty good so I can't see this happening, especially with a simple macro like this, but adding in some Wait functions has resolved. Hence the random inserts above.

    Edited by RyanDS at 12:06:46 28-09-2018
  • Drakesmoke 28 Sep 2018 15:43:31 467 posts
    Seen 4 hours ago
    Registered 4 years ago
    Hope I'm not impolite for spotting this thread and sticking another question in prior to Ryan's being solved...

    I have a workbook with two sheets. Sheet one has a list of dates to the left (let's say column A for simplicity) and various costs and stuff against those dates, ending with a final monetary value (let's say column B for simplicity).

    On sheet 2, I have a list of dates in column A. I want to use some kind of match/lookup to search for matching dates from worksheet 1, and when one is found, sheet 2 pulls the monetary value from column B sheet 1 that matches this date. This would be shown in column B, sheet 2.

    I can't seem to find any matching examples on line.

    Thanks for listening!
  • Drakesmoke 28 Sep 2018 15:43:33 467 posts
    Seen 4 hours ago
    Registered 4 years ago
    Double post.

    Edited by Drakesmoke at 18:19:40 28-09-2018
  • mrpon 28 Sep 2018 15:48:59 34,780 posts
    Seen 2 days ago
    Registered 12 years ago
    @Drakesmoke on Sheet2, column B, try something like this:

    =VLOOKUP(A:A,Sheet1!A:B,2)
  • robthehermit 28 Sep 2018 15:50:28 6,931 posts
    Seen 8 hours ago
    Registered 14 years ago
    @Drakesmoke

    =SUMIF(Sheet1!A:A,Sheet2!A:A,Sheet1!B:B)

    Edited by robthehermit at 16:10:53 28-09-2018

    Edited by robthehermit at 16:13:12 28-09-2018
  • robthehermit 28 Sep 2018 15:52:12 6,931 posts
    Seen 8 hours ago
    Registered 14 years ago
    mrpon wrote:
    @Drakesmoke on Sheet2, column B, try something like this:

    =VLOOKUP(A:A,Sheet1!A:B,2)
    This won't work if you have more than one matching date.
  • RyanDS 28 Sep 2018 16:04:15 12,175 posts
    Seen 52 seconds ago
    Registered 10 years ago
    Are the dates unique? If so use Vlookup as per MrPon, if not then they will have to be summed as per Rob.
  • mrpon 28 Sep 2018 16:09:09 34,780 posts
    Seen 2 days ago
    Registered 12 years ago
    Bubbles are on ice!!
  • robthehermit 28 Sep 2018 16:14:07 6,931 posts
    Seen 8 hours ago
    Registered 14 years ago
    @Drakesmoke Updated my broken formula to correctly point at the cells that need adding up.
  • RyanDS 28 Sep 2018 16:34:05 12,175 posts
    Seen 52 seconds ago
    Registered 10 years ago
    Anyone got any ideas on my F8 versus running macros issue?

    As I said, it is resolved, but I can't seem to see why.
  • robthehermit 28 Sep 2018 16:37:30 6,931 posts
    Seen 8 hours ago
    Registered 14 years ago
    Not here I'm afraid, despite using it everyday for the last too many years, my skills end at VLOOKUP and SUMIF.
  • Drakesmoke 28 Sep 2018 18:19:13 467 posts
    Seen 4 hours ago
    Registered 4 years ago
    Thanks all. I'll give this a go later on,.

    Got cocky after successfully making a formula which did one sum based on whether the entry was above or below a certain level only to be stumped by this!
  • Drakesmoke 29 Sep 2018 16:41:17 467 posts
    Seen 4 hours ago
    Registered 4 years ago
    Rob takes the trophy. SUMIF worked a treat.

    Thank you to all that contributed.
  • robthehermit 29 Sep 2018 18:45:55 6,931 posts
    Seen 8 hours ago
    Registered 14 years ago
    \o/
  • mrpon 29 Sep 2018 19:58:13 34,780 posts
    Seen 2 days ago
    Registered 12 years ago
    Pffftt
Log in or register to reply

Sometimes posts may contain links to online retail stores. If you click on one and make a purchase we may receive a small commission. For more information, go here.