Recommend online beginner course to VB for Excel

[rominjn]

Hi community,

I find myself needing more and more to know how to write and edit Excel macros at work. I'm a bit overwhelmed about where to begin and was looking into online courses I could take. I found an 8-hour course on Udemy but was wondering if you guys have better recommendations. I'm proficient in Excel in-cell formulas but a total foreigner to reading, let alone writing and editing, Excel macros. Any advice would be greatly appreciated.

Thanks.
Daniel

[mehmetcik]

Hi

If you are proficient in excel formulas

then I would use the macro recorder to create you code.

then learn how to use loops

and a few standard lines of code and you will be off
I have a macro in my personal workbook that writes all my most used code into excel
so I don't have to remember them or look for them.

Ok if you paste the attached code into your personal workbook and run the macro useful code

it will generate a new sheet in the active workbook that will give you some ....................useful code.

The first bits are examples on how to copy cells

the second range gives you info on your active workbook

the next area gives you info on the active work sheet

Eg LR = Last used row in column 1. Change the 1 to a 2 to look at column 2
LC = last used column in row 1 Change the 1 to a 2 to look at row 2

The next area is my deOptimise subroutine which undoes my Optimise Subroutine
The Optimise Subroutine turns off parts of the excel program to speed up processing
however it will also switch of some of the macro events, so you need to run deoptimise.

The final area shows you how to use Match, Instr and Find in VBA.

Enjoy.

Please Login or Register  to view this content.

[nigelbloomy]

I liked this one for getting some basic ideas down: http://easyexcelvba.com/introduction.html

[mehmetcik]

For clarity I am going to explain LOOPS and addressing separately

You can Loop using the built in VBA for next subroutine.

Eg:

Please Login or Register  to view this content.

You can always break out of the for next loop if you need to.

Please Login or Register  to view this content.

Sometimes it is simpler and safer to create your own for next functionality using Labels.

Eg:

Please Login or Register  to view this content.

[rominjn]

Thanks mehmetcik. I'm stuck on the next step.

I'm creating a command button that I can click to initiate the action of selecting and copying a range of cells based on a conditional statement. The button is on a worksheet called "Buttons", but the action that needs to take place is in another worksheet called "Aggregate". For some reason, the button only works for the worksheet that it's on, and not if the action has to take place in the Aggregate worksheet. Below is the actual code I've written and the bolded line is the error line. When I add the "Agg." worksheet designation, it doesn't work, but when I remove it, essentially telling the action to run on the same worksheet that the button is on, it works fine, but I need it to run on the Aggregate worksheet. Can you help me further?

Private Sub CommandButton9_Click()
Dim Agg As Worksheet
Dim LastRow As Integer
Dim i As Integer

Set Agg = Worksheets("Aggregate")
LastRow = Agg.Range("A" & Rows.Count).End(xlUp).Row

For i = 3 To LastRow
If Agg.Range("A" & i).Value = "Bucket A - Missing Docs" Then
Agg.Range("B" & i & ":O" & i).Select
Selection.Copy
End If
Next i

End Sub


Thanks!

[JOHN H. DAVIS]

Here's some from the Forum.

http://www.excelforum.com/excel-prog...materials.html

[mehmetcik]

Sorry

The server will not let me post here.

PM on its way.