admin - 2/3 - Houston Computer Classes

Excel VBA Function to SUM Unique Numbers Only – Unique SUMIF Function

November 23, 2016 in Excel, Reference, Tutorial /by admin

First add this code to your VBA Function

<br />
Function SUMIFUNIQUENUMS(rng2 As Range, rng1 As Range, rng3 As Range)</p>
<p>    num = rng1.Count - 1<br />
    Dim arr1() As Variant<br />
    ReDim arr1(num)<br />
    i = 0<br />
    For Each r In rng1</p>
<p>        If r.Value = rng3.Value Then<br />
            arr1(i) = 1 * rng2(i + 1)<br />
        Else<br />
            arr1(i) = 0<br />
        End If<br />
        i = i + 1</p>
<p>    Next r</p>
<p>    Dim arr As New Collection, a</p>
<p>    On Error Resume Next</p>
<p>      For Each a In arr1<br />
         arr.Add a, Str(a)<br />
      Next</p>
<p>      insum = 0</p>
<p>        For Each n In arr<br />
            insum = insum + n<br />
        Next n</p>
<p>        SUMIFUNIQUENUMS = insum</p>
<p>End Function

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

Function SUMIFUNIQUENUMS(rng2 As Range, rng1 As Range, rng3 As Range)

num = rng1.Count - 1

Dim arr1() As Variant

ReDim arr1(num)

i = 0

For Each r In rng1

If r.Value = rng3.Value Then

arr1(i) = 1 * rng2(i + 1)

Else

arr1(i) = 0

End If

i = i + 1

Next r

Dim arr As New Collection, a

On Error Resume Next

For Each a In arr1

arr.Add a, Str(a)

For Each n In arr

insum = insum + n

Next n

SUMIFUNIQUENUMS = insum

End Function

Then use it in your worksheet like this A B C D E 1 CA 11 CA =SUMIFUNIQUENUMS($B$1:$B$7,$A$1:$A$7,D1) 2 NY 14 NY 29 3 NY 15 IN 0 4 CA 11 5 CA 11 6 CA 14 7 CA 11 Sheet1 Posted by […]

VBA – Check Regular & Overtime Hours – Excel functions

December 29, 2015 in Excel, Reference, Tutorial /by admin

Below are 2 Excel VBA UDFs to check and divide regular and overtime hours based on 40 hour week, Monday through Friday Payroll. Date Hours Worked Regular Overtime 12/15/15 14 14 0 12/16/15 9 9 0 12/17/15 11 11 0 12/18/15 9 6 3 12/19/15 7 0 7 12/20/15 8 0 8 12/21/15 5 5 […]

VBA – Unique List of Data That Appears At Least Once in All Columns – Excel Macro

September 16, 2015 in Excel, Reference, Tutorial /by admin

Use this VBA code to extract a unique list of items that appear inside of multi-column data. The criteria is that the data needs to appear at least once in each column. This one has user friendly interface to run on any size data and get the results wherever user picks in the worksheet.

<br />
Sub UniqueListMatchingMultipleColumns()</p>
<p>Dim rng As Range<br />
Dim srcrng As Range</p>
<p>Set rng = Range("g1")</p>
<p>Set srcrng = Application.InputBox("Please select you data (Do not include column headings!)", , , Type:=8)<br />
Set rng = Application.InputBox("Click the cell where you'd like the output to start rendering (a single cell):", , Type:=8)</p>
<p>longstr = "||||"<br />
finalstring = "||||"<br />
For Each i In srcrng</p>
<p>If InStr(longstr, "||||" &amp; i.Value &amp; "||||") = 0 Then</p>
<p>    longstr = longstr &amp; i.Value &amp; "||||"<br />
End If</p>
<p>Next i</p>
<p>For Each word In Split(Mid(longstr, 5, Len(longstr) - 8), "||||")<br />
    n = 1<br />
    got = 0<br />
    For Each col In srcrng.Columns<br />
        mt = Application.Match(word, srcrng.Columns(n), 0)<br />
        If IsNumeric(mt) Then<br />
            got = 1<br />
        Else</p>
<p>            got = 0<br />
            Exit For<br />
        End If</p>
<p>        n = n + 1<br />
    Next col</p>
<p>    If got = 1 Then<br />
        finalstring = finalstring &amp; word &amp; "||||"</p>
<p>    End If</p>
<p>Next word<br />
r = 0<br />
For Each finalword In Split(Mid(finalstring, 5, Len(longstr) - 8), "||||")<br />
    rng.Offset(r, 0).Value = finalword<br />
    r = r + 1<br />
Next finalword</p>
<p>End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

Sub UniqueListMatchingMultipleColumns()

Dim rng As Range

Dim srcrng As Range

Set rng = Range("g1")

Set srcrng = Application.InputBox("Please select you data (Do not include column headings!)", , , Type:=8)

Set rng = Application.InputBox("Click the cell where you'd like the output to start rendering (a single cell):", , Type:=8)

longstr = "||||"

finalstring = "||||"

For Each i In srcrng

If InStr(longstr, "||||" & i.Value & "||||") = 0 Then

longstr = longstr & i.Value & "||||"

End If

Next i

For Each word In Split(Mid(longstr, 5, Len(longstr) - 8), "||||")

n = 1

got = 0

For Each col In srcrng.Columns

mt = Application.Match(word, srcrng.Columns(n), 0)

If IsNumeric(mt) Then

got = 1

Else

got = 0

Exit For

End If

n = n + 1

Next col

If got = 1 Then

finalstring = finalstring & word & "||||"

End If

Next word

r = 0

For Each finalword In Split(Mid(finalstring, 5, Len(longstr) - 8), "||||")

rng.Offset(r, 0).Value = finalword

r = r + 1

Next finalword

End Sub

[…]

VBA – Loop Through All Textboxes in a Form – Excel Macro

September 16, 2015 in Excel, Reference, Tutorial /by admin

This VBA code will loop though all textboxes in your userform that are following the naming convention textBox[x] (ex. textBox1, textBox2, textBox3 ….. textBox255)

<br />
For Each txtboxitem In Me.Controls<br />
    If Left(txtboxitem.Name, 6) = "textBox" Then<br />
        If txtboxitem.Text &lt;&gt; "" Then</p>
<p>                'do something</p>
<p>            Exit For<br />
        End If<br />
    End If<br />
Next txtboxitem

1

2

3

4

5

6

7

8

9

10

For Each txtboxitem In Me.Controls

If Left(txtboxitem.Name, 6) = "textBox" Then

If txtboxitem.Text <> "" Then

'do something

Exit For

End If

Next txtboxitem

Posted by Excel Instructor: http://www.houstoncomputerclasses.com/excel-classes/

VBA – Loop Through All (.txt) Files in a User Specified Directory – Excel Macro

August 7, 2015 in Excel, Reference, Tutorial /by admin

This VBA code will loop though all the text files in a directory that user picks from an application box and print a list of them. This code can be modified to be used in many scenarios for any file type.

<br />
Sub LoopThroughAllTextFilesInFolder()</p>
<p>    'Declarations<br />
        Dim wb As Workbook<br />
        Dim DirPath As String<br />
        Dim FileName As String<br />
        Dim FileExt As String<br />
        Dim UserFolderChoice As FileDialog<br />
        Dim i As Integer<br />
        Dim howmany As Integer<br />
    'Declarations End</p>
<p>    'Regular Stuff<br />
        Application.ScreenUpdating = False<br />
        Application.EnableEvents = False<br />
        Application.Calculation = xlCalculationManual<br />
    'Regular Stuff End</p>
<p>    'User's Folder Choice<br />
        Set UserFolderChoice = Application.FileDialog(msoFileDialogFolderPicker)</p>
<p>        With UserFolderChoice<br />
          .Title = "Select A Target Folder"<br />
          .AllowMultiSelect = False<br />
            If .Show &lt;&gt; -1 Then GoTo AllGood<br />
            DirPath = .SelectedItems(1) &amp; "\"<br />
        End With<br />
    'User's Folder Choice End</p>
<p>AllGood:</p>
<p>    'Check if path is empty<br />
      If DirPath = "" Then GoTo JumpToEnd</p>
<p>    'File Extension<br />
      FileExt = "*.txt"</p>
<p>    'Let's find the first file &amp; how many total files we have<br />
      FileName = Dir(DirPath &amp; FileExt)<br />
      howmany = Len(Dir(DirPath &amp; FileExt))</p>
<p>        'let's loop though them<br />
            For i = 1 To howmany</p>
<p>                'print the name of the file<br />
                Debug.Print FileName</p>
<p>                'move to the next file<br />
                FileName = Dir<br />
            Next i</p>
<p>'Done!!!</p>
<p>JumpToEnd:</p>
<p>    'Regular Stuff<br />
        Application.EnableEvents = True<br />
        Application.Calculation = xlCalculationAutomatic<br />
        Application.ScreenUpdating = True<br />
    'Regular Stuff End</p>
<p>End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

Sub LoopThroughAllTextFilesInFolder()

'Declarations

Dim wb As Workbook

Dim DirPath As String

Dim FileName As String

Dim FileExt As String

Dim UserFolderChoice As FileDialog

Dim i As Integer

Dim howmany As Integer

'Declarations End

'Regular Stuff

Application.ScreenUpdating = False

Application.EnableEvents = False

Application.Calculation = xlCalculationManual

'Regular Stuff End

'User's Folder Choice

Set UserFolderChoice = Application.FileDialog(msoFileDialogFolderPicker)

With UserFolderChoice

.Title = "Select A Target Folder"

.AllowMultiSelect = False

If .Show <> -1 Then GoTo AllGood

DirPath = .SelectedItems(1) & "\"

End With

'User's Folder Choice End

AllGood:

'Check if path is empty

If DirPath = "" Then GoTo JumpToEnd

'File Extension

FileExt = "*.txt"

'Let's find the first file & how many total files we have

FileName = Dir(DirPath & FileExt)

howmany = Len(Dir(DirPath & FileExt))

'let's loop though them

For i = 1 To howmany

'print the name of the file

Debug.Print FileName

'move to the next file

FileName = Dir

Next i

'Done!!!

JumpToEnd:

'Regular Stuff

Application.EnableEvents = True

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

'Regular Stuff End

End Sub

Posted by Excel Instructor: http://www.houstoncomputerclasses.com/excel-classes/

VBA – Get Information From a Web Page – Excel Macro – Amazon Example

July 13, 2015 in Excel, Reference, Tutorial /by admin

This is a basic VBA code to get started with InternetExplorer.Application object and work with web browsers and data extraction. This code is written strictly for learning proposes and should be used as such. Everything is well commenced out so it should be really easy to understand. In this example we use Internet Explorer to open […]

VBA – Get All Numbers in a Text String & Incresae by One – Excel Macro

May 14, 2015 in Excel, Reference, Tutorial /by admin

The following procedure will take any numbers within text and increase them by 1. For Example: For20Example51Text will transform into For21Example52Text

<br />
Sub UpNumbersByOne()</p>
<p>cell = "YOUR16STRING1HERE10"</p>
<p>Dim v As Variant<br />
 endresult = ""</p>
<p>With CreateObject("VBScript.RegExp")<br />
    .Pattern = "(\d+|\D+)"<br />
    .Global = True<br />
        v = Split(Mid(.Replace(cell, "|$1"), 2), "|")<br />
        For Each num In v<br />
        If IsNumeric(num) = True Then<br />
        num = num + 1<br />
        End If<br />
        endresult = endresult &amp; num<br />
        Next num<br />
End With</p>
<p>Debug.Print endresult</p>
<p>End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

Sub UpNumbersByOne()

cell = "YOUR16STRING1HERE10"

Dim v As Variant

endresult = ""

With CreateObject("VBScript.RegExp")

.Pattern = "(\d+|\D+)"

.Global = True

v = Split(Mid(.Replace(cell, "|$1"), 2), "|")

For Each num In v

If IsNumeric(num) = True Then

num = num + 1

End If

endresult = endresult & num

Next num

End With

Debug.Print endresult

End Sub

Posted by Excel Instructor: http://www.houstoncomputerclasses.com/excel-classes/

VBA – Get First Number & Then Text & Following Number – Excel Macro

May 14, 2015 in Excel, Reference, Tutorial /by admin

This Excel Macro will transform the text data placed in column A and Break it down into 3 Columns. Data Format Required: 15654 Text goes Here 16 2 6 1595 1 Different Text 1 6 number (space) text of any lenght (space) number (space) other characters Output will return: First column: full first number Second […]

Libraries in Chicago

April 23, 2015 in News, Reference /by admin

List of Chicago Libraries with address and phone number. NAME Address PHONE Albany Park 3401 W. Foster Avenue, Chicago, IL 60625 (773) 539-5450 Altgeld 13281 S. Corliss Avenue, Chicago, IL 60827 (312) 747-3270 Archer Heights 5055 S. Archer Avenue, Chicago, IL 60632 (312) 747-9241 Austin 5615 W. Race Avenue, Chicago, IL 60644 (312) 746-5038 Austin-Irving […]

VBA – Delete Every Other Row – Odd or Even – Excel Macro

April 23, 2015 in Excel, Reference, Tutorial /by admin

This Excel Macro will go through all the cells in column A, find the last cell that has data in it, and then delete every other row in that data range. To Delete Odd Rows

<br />
Sub Delete_Odd()</p>
<p> Application.ScreenUpdating = False<br />
lr = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row</p>
<p>If lr Mod 2 = 0 Then<br />
lr = lr - 1<br />
End If</p>
<p>For i = lr To 1 Step -2<br />
    Rows(i &amp; ":" &amp; i).Delete Shift:=xlUp<br />
Next i</p>
<p> Application.ScreenUpdating = True</p>
<p>End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

Sub Delete_Odd()

Application.ScreenUpdating = False

lr = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

If lr Mod 2 = 0 Then

lr = lr - 1

End If

For i = lr To 1 Step -2

Rows(i & ":" & i).Delete Shift:=xlUp

Next i

Application.ScreenUpdating = True

End Sub

To Delete Even Rows

<br />
Sub Delete_Even()</p>
<p> Application.ScreenUpdating = False<br />
lr = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row</p>
<p>If lr Mod 2 = 0 Then<br />
Else<br />
lr = lr - 1<br />
End If</p>
<p>For i = lr To 1 Step -2<br />
    Rows(i &amp; ":" &amp; i).Delete Shift:=xlUp<br />
Next i</p>
<p>  Application.ScreenUpdating = True</p>
<p>End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

Sub Delete_Even()

Application.ScreenUpdating = False

lr = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

If lr Mod 2 = 0 Then

Else

lr = lr - 1

End If

For i = lr To 1 Step -2

Rows(i & ":" & i).Delete Shift:=xlUp

Next i

Application.ScreenUpdating = True

End Sub

Posted by Excel Instructor: http://www.houstoncomputerclasses.com/excel-classes/

About admin

Entries by admin

Excel VBA Function to SUM Unique Numbers Only – Unique SUMIF Function

VBA – Check Regular & Overtime Hours – Excel functions

VBA – Unique List of Data That Appears At Least Once in All Columns – Excel Macro

VBA – Loop Through All Textboxes in a Form – Excel Macro

VBA – Loop Through All (.txt) Files in a User Specified Directory – Excel Macro

VBA – Get Information From a Web Page – Excel Macro – Amazon Example

VBA – Get All Numbers in a Text String & Incresae by One – Excel Macro

VBA – Get First Number & Then Text & Following Number – Excel Macro

Libraries in Chicago

VBA – Delete Every Other Row – Odd or Even – Excel Macro

Locations:

Author Archive for: admin

About admin

Entries by admin

Locations: