BY TOPIC

PROGRAMMING BLOGS

WRITING VBA BLOGS

BLOGS BY AUTHOR

BLOGS BY YEAR

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

Showing blogs 61-68 (out of 68)

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!

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.

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.

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.

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.

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.

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.

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!