Wise Owl Training - Writing VBA blogs (page 4 of 4)

Showing blogs 61-68 (out of 68)

Free VBA training

Posted by Andy Brown on 16 August 2011

As well as running introductory and advanced VBA training courses in the UK for small groups, we've also published this online training course teaching how to code macros using Visual Basic for Applications (VBA) within Excel. The tutorial covers everything from basic recording through to creating classes - something for everyone!

Tags:   VBA macros | General    |    Writing VBA | VBA tutorial

Relative Selection using Excel VBA Macros

Posted by Andy Brown on 16 August 2011

This blog shows the various techniques within Excel Visual Basic for moving around and selecting cells relative to the active cell or selection. The blog includes a discussion of the Offset, End, Resize, Range, EntireRow, EntireColumn and CurrentRegion methods.

Selecting Cells using Excel VBA Macros - Absolute

Posted by Andy Brown on 11 August 2011

This blog gives the various possible ways to select cells, ranges, columns and rows using Visual Basic for Applications in Excel. Useful for the VBA newbie, but even gurus might find one or two commands they'd missed!

Introduction to Visual Basic for Applications (VBA)

Posted by Andrew Gould on 01 July 2011

If you've heard people in your office talking about macros or VBA but don't understand what they mean, this blog series will make everything clear. It's an introduction to the most basic skills you'll need to start creating your own programs in any of the Microsoft Office applications.

Tags:   VBA macros | General    |    Writing VBA | Other

How to record macros in Excel Visual Basic

Posted by Andy Brown on 30 June 2011

If you're not sure what the Excel Visual Basic for Applications language is (or how it can help you) this blog attempts to explain! Learn what VBA is, and how you can record macros and then replay them again and again to automate work in Excel.

Tags:   Writing VBA | VBA tutorial    |    VBA macros | Recording macros

Adding data labels to a chart using VBA

Posted by Andrew Gould on 08 June 2011

Adding basic data labels to a chart can be done without using any programming code. This blog, however, shows you how to fine-tune Excel chart labels using VBA (Visual Basic for Applications) macros.

Tags:   Excel | Charts    |    Writing VBA | Other    |    VBA macros | Working with charts

Using sheet codenames in Microsoft Excel VBA

Posted by Michael Allsop on 01 June 2011

Do your macros keep falling over due to users renaming their worksheets in Excel? Read this article to find out if sheet codenames could be the solution you need.

Tags:   VBA macros | General    |    Writing VBA | Other

Commenting out code in VBA

Posted by Andy Brown on 28 April 2011

Commenting code is an art form! This series of blogs explains why, how and when to comment in VBA.

Tags:   VBA macros | General    |    Writing VBA | VBA tutorial
This page has 1 thread Add post
09 Feb 17 at 15:24

Firstly please let me introduce myself, I'm Charlie, I'm new to coding and I have been given a task. The task is to create code to generate discrete part descriptions for every part number our system can generate. I have attached the code that was written to generate the part numbers, I have also attached the code that I am trying to use to generate the descriptions which has compile error type mismatch occurring.

I would very much like to understand the original code and then create new rather than someone writing the code and just accepting that it works, as has happened before, hence the current situation! My first major hurdle is understanding the "i" in for example:

Dim i1 As Long

If Cells(i2, 2) Mod 2 <> 0 And Cells(i4, 4)

and

Next i5

Amongst other areas of the code.

Sub series325()
Dim a As Long
    Dim b As Long
    Dim c As Long
    Dim d As Long
    Dim e As Long
    Dim i1 As Long
    Dim i2 As Long
    Dim i3 As Long
    Dim i4 As Long
    Dim i5 As Long
    Dim counter As Long
     
    counter = 1
     
    a = Range("A1").End(xlDown).Row
    b = Range("B1").End(xlDown).Row
    c = Range("C1").End(xlDown).Row
    d = Range("D1").End(xlDown).Row
    e = Range("E1").End(xlDown).Row
     
    For i1 = 2 To a
        For i2 = 2 To b
            For i3 = 2 To c
                For i4 = 2 To d
                    For i5 = 2 To e
                        If Cells(i2, 2) Mod 2 <> 0 And Cells(i4, 4) Mod 2 = 0 Then GoTo Nexti
                        If Cells(i2, 2) Mod 2 <> 0 And Cells(i4, 4) = "-2" Then GoTo Nexti
                        If Cells(i2, 2) > 61 And Cells(i4, 4) = "-1" Then GoTo Nexti
                        Cells(counter, 10) = Cells(i1, 1) & Cells(i2, 2) & Cells(i3, 3) & Cells(i4, 4) & Cells(i5, 5)
                        counter = counter + 1
Nexti:
                    Next i5
                Next i4
            Next i3
        Next i2
    Next i1
End Sub
 

I would like to apply the same method to the descriptions. I was hoping the code below might work, sadly it didn't.

Sub series325PartDescriptionTest()
    Dim a As String
    Dim b As Long
    Dim c As String
    Dim d As String
    Dim e As String
    Dim f As String
    Dim g As String
    Dim i1 As String
    Dim i2 As Long
    Dim i3 As String
    Dim i4 As String
    Dim i5 As String
    Dim i6 As String
    Dim i7 As String
    Dim counter As String
     
    counter = 1
     
    a = Range("A1").End(xlDown).Row
    b = Range("B1").End(xlDown).Row
    c = Range("C1").End(xlDown).Row
    d = Range("D1").End(xlDown).Row
    e = Range("E1").End(xlDown).Row
    f = Range("F1").End(xlDown).Row
    g = Range("G1").End(xlDown).Row
     
    For i1 = 2 To a
        For i2 = 2 To b
            For i3 = 2 To c
                For i4 = 2 To d
                    For i5 = 2 To e
                        For i6 = 2 To f
                            For i7 = 2 To g
                                If Cells(i2, 2) Mod 2 <> 0 And Cells(i5, 5) Mod 2 = 0 Then GoTo Nexti
                                If Cells(i2, 2) Mod 2 <> 0 And Cells(i5, 5) = "Double Row formation. " Then GoTo Nexti
                                Cells(counter, 10) = Cells(i1, 1) & Cells(i2, 2) & Cells(i3, 3) & Cells(i4, 4) & Cells(i5, 5) & Cells(i6, 6) & Cells(i7, 7)
                                counter = counter + 1
Nexti:
                            Next i7
                        Next i6
                    Next i5
                Next i4
            Next i3
        Next i2
    Next i1
End Sub
 

I am struggling to add the spreadsheets for reference, the formatting falls apart when I use copy & paste.

 

10 Feb 17 at 13:00

Hi Charlie, thanks for your question! It's a tricky one to answer in a simple comment like this one because, as you say, you're new to programming. Fortunately, we've created both written and video tutorials on VBA that will help you to understand all of the elements that go into making up the procedure you're trying to create.

This page lists the written articles that are part of our main Excel VBA tutorial. We've written plenty of other articles on more esoteric aspects of the language but the articles in that tutorial will give the core skills you need.

If you'd prefer to watch rather than read, you can see a list of our Excel VBA videos here. The main ones you'll need in order to understand the code you've posted are the videos on Selecting Cells, Variables in VBA, If Statements in VBA and For Next Loops.

I hope that helps!