Excel Help Thread 📊

Practice…
Google individual parts and borrow code from the internet, use the record function to do chunks what you want to do then copy and paste the chunks into one macro.
You may need to modify the recorded code sometimes, but step through it using F8 I think, and you should be able to see what’s going on.

3 Likes

That’s great. I’m thinking this code in the first reply will help me get close and I can kind of reverse learn things a bit by debugging and stepping through as described. (once I learn how to do that)

1 Like

Good luck with the job :slight_smile:

2 Likes

Thank you!

A few simple steps to get to grips with a macro:

  • Add the Developer tab to your excel ribbon if it isn’t already there (right click the top, select “customise ribbon” and then it should open a window with a check box to click for Developer)
  • On the Developer tab, click on “Record Macro”, give the Macro an arbitrary name or leave it as Macro1 if you’re chaotic
  • Once you’ve clicked through that window, do one small, minor task. Start small, maybe just copy the one cell into another and then click Stop Recording
  • Now, click on the Macros button to the right EDIT - LEFT, select your macro and click Step Into. This will open a VBA window where you’ll see the little line of code you just created. Mine looked like this:
Summary

Sub DayriderMacroCopy()

’ DayriderMacroCopy Macro


Range(“A3”).Select
Selection.Copy
Range(“C3”).Select
ActiveSheet.Paste
End Sub

So now you know what a small, relatively uncomplicated macro looks like, so you need to build out from there - the task they set won’t be easy to make using the record button as “find empty cells” requires something more nuanced, but the answer definitely exists on the internet so the next step is to find an example and then copy it into a format like that.

Note that you’ll need to look out for things like Sheet1 that might be referenced in the code, whereas your sheet might be called AugustInvoicesPaid or something. So if you borrow from elsewhere you’ll need to scan for references to sheets and cells.

When you come to create your own macro, you can also just hit Record Macro and then Stop Recording immediately after without doing anything on the sheet, which will give you a blank macro receptacle into which you can dump your code and test it out.

6 Likes

Thanks for all of the help. I now have a working code.

PROBLEM:

When I click the ‘Macros’ button on the ribbon, and run the macro from there it works as it should.

The task I’ve been set requires me to have the macro triggered by clicking a button on another worksheet. I’ve been able to right-click and assign the macro to the button, however once I do this and click the button I get the below message:

‘Run-time error ‘13’:

Type mismatch’

Is there something I need to do to the code after assigning the macro to the button? The videos I’ve watched show it being as easy as right-clicking, assigning and clicking for the macro to run but I’m getting this error message. Any advice?

If you google the error code, it’s saying it’s to do with a type mismatch

E.g.

Might help?

If your button is on a different worksheet, I don’t know much about VBA or Excel macros but just check all the references are pointing to the specific sheet they need to be, and not looking up the active sheet, because the active sheet when you press the button will be the one with the button on it, which might not be the one you were on when you created the macro

1 Like

Thanks for the reply. That article has me a bit more lost tbh cos I don’t really understand the stuff in it. When I assign the macro to the button the code doesn’t change which could be the problem but I haven’t seen any mention of that in any of the videos I’ve watched. Hopefully there’s someone in the office who can help pull some of these bits together.

Post the code here if you want. What @froglet said about object references not being valid from another worksheet is almost certainly the problem.

Recording a macro is a great way to start with VBA but you end up with code which only works in very specific circumstances with lots of references to ActiveSheet, ActiveCell and .Selection which might not exist when you trigger the code in another way.

Edit: also useful if you can highlight the line which causes the error.

2 Likes

recording macros is helpful because it’ll give you an idea of how to write code to do certain things that are essential, like selecting a cell or row, like changing a value in a cell, like copying and pasting etc. Once you have those building blocks then putting it into a loop (which is very simple) will be quite straight forward.

I agree it’s a good way to start and get an idea of how to achieve things but you do need to be aware of the limitations and issues when using it.

For example, it will always give you code which works in the most inefficient way possible because it’s mimicking the individual steps you make when carrying out an action, e.g. activate source worksheet, select a cell, copy value, activate target sheet, paste value.

1 Like

Sub MoveRows()

Dim ws2 As Worksheet: Set ws2 = Worksheets("Sheet2")    '**Change Sheet Name here
Dim ws4 As Worksheet: Set ws4 = Worksheets("Sheet4")    '**Change Sheet Name here
Dim arr, arr2, r As Long, c As Long, x As Long, ct As Long

Application.ScreenUpdating = False
ct = 1
arr = ActiveSheet.UsedRange
With ws2.UsedRange
    ReDim arr2(1 To .Rows.Count, 1 To .Columns.Count)
    For r = 1 To .Rows.Count
        For c = 1 To .Columns.Count
            If arr(r, c) = "" Then
                For x = 1 To .Columns.Count
                    arr2(ct, x) = arr(r, x)
                Next
                ct = ct + 1
                arr(r, 1) = ""
            End If
        Next
    Next
End With
ws2.Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
ws2.Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
ws4.Range("A2").Resize(UBound(arr2, 1), UBound(arr2, 2)) = arr2
Application.ScreenUpdating = True

End Sub

This is the code I’m working with and it flags the error as being here:

If arr(r, c) = “” Then

This is the problem. ActiveSheet is the one with the button on it and presumably no data in any of the cells so arr() is empty.

Which one is the source sheet and which the target?

2 Likes

Thanks! The button is on Sheet 1, how do I amend the code to reflect that?

Worksheets(“Sheet1”).Activate

2 Likes

image

3 Likes

I don’t think that’s right. You’re going to get a whole load of Out of Bounds errors as soon as you you start looping.

Which sheet is active when you run it from the ribbon? That’s the sheet you want to reference there. I think it should be arr = ws2.UsedRange as well.

Yeah it did throw up an error.

Sheet 2 is active when I run it from the ribbon.

Then this should work:
arr = ws2.UsedRange

You’re getting that error because the code:

  • sets a reference to the usedRange of the activesheet: arr()
  • loops over the usedrange of Sheet2: With ws2.UsedRange …
  • Tries to lookup an element in arr() based on the column and row index of ws2.usedRange

If the two aren’t the same size you get an error.

It also looks like you’re deleting rows from sheet2 at the end? Isn’t the goal to have the original data still in sheet4 and only the populated rows in sheet4?

Great thank you, I’ll give that a bash when I get a moment.

The overall aim is to have a button on sheet 1 which activates a macro that identifies any rows with empty cells on sheet 2 and moves the entire row to sheet 4, deleting them from sheet 2.

1 Like