This page has 1 thread | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

Showing blogs 21-28 (out of 28)

Advice on how to scrape tables of data or HTML from webpages using VBA

Posted by Andy Brown on 13 January 2014

You can use VBA to extract data from web pages, either as whole tables or by parsing the underlying HTML elements. This blog shows you how to code both methods (the technique is often called "web-scraping").

Tags:
VBA macros > References Writing VBA > Other

How to use environment variables like USERNAME in VBA

Posted by Andy Brown on 25 November 2013

You can get at all sorts of system information within Visual Basic for Applications by using environment variables - this blog shows you how to get at your user's name, computer name and much more besides.

Tags:
VBA macros > Variables, arrays Writing VBA > Other

How to loop over enumerations in VBA

Posted by Andy Brown on 07 November 2013

A short blog explaining how to use Visual Basic for Applications to loop over enumerations.

Tags:
VBA macros > Enumerations Writing VBA > Other

Want to wind up your neighbour? Try this annoying Excel VBA code!

Posted by Andy Brown on 18 September 2024

If you have a colleague who trusts you, you could always betray this trust by sending them a workbook which misbehaves: it won't close and you can't leave it!

Tags:
VBA macros > Events Writing VBA > Other

Problem with using VBA to create chart labels

Posted by Andy Brown on 04 April 2013

If your chart has more than a thousand data labels, this blog explains why you may experience problems.

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

Using Excel VBA to Label Multiple Series in a Chart

Posted by Andrew Gould on 30 May 2012

This follow-up article to a previous blog describes how to label the data points in multiple series in Excel using VBA.

Tags:
VBA macros > Working with charts Writing VBA > Other

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 VBA macros > Working with charts Writing VBA > Other

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

Some other pages relevant to the above blogs include:

Classes in VBA

This page has 1 thread

Add a new post

From:	EDAC-Charlie
When:	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.

From:	Andrew G
When:	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!

Add a new post

BY TOPIC

PROGRAMMING BLOGS

WRITING VBA BLOGS