How do you run a loop on a single column through a workbook in VBA?

Question

0.00/5 (No votes)

See more:

Trying to create a list of unique strings in VBA from column A throughout an entire workbook.

What I have tried:

Sub VBAStocks()

'label column data
Dim ticker As String
Dim yearly_change As Double
Dim percent_change As Double
Dim total_stock_volume As Long
Dim ws As Worksheet
Dim Summary_Table_Row As Integer

'Name columns

Range("I1").Value = "Ticker"
Range("J1").Value = "Yearly Change"
Range("K1").Value = "Percent Change"
Range("L1").Value = "Total Stock Value"

For each ws in Workbook

If ws.Cells(i + 1, 1).Value <>ws.Cells(i, 1).Value Then
ticker = Cells(i, 1).Value
Range("I" & Summary_Table_Row).Value = ticker

Summary_Table_Row = 2

Summary_Table_Row = Summary_Table_Row + 1

End if

End Sub

Posted 24-May-20 8:36am

Member 14842334

Updated 24-May-20 23:01pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2020-05-24T08:51:00

Solution 1

Your code is missing the end of loop 'Next'.
VBA - For Each Loops - Tutorialspoint[^]

Posted 24-May-20 8:51am

Patrice T

Comments

Member 14842334 24-May-20 14:59pm

"For Each ws In Worksheet" is still encountering a debug message

Patrice T 24-May-20 15:02pm

Show updated code.

Member 14842334 24-May-20 15:06pm

Sub VBAStocks()

'label column data
Dim ticker As String
Dim yearly_change As Double
Dim percent_change As Double
Dim total_stock_volume As Long
Dim ws As Worksheet
Dim Summary_Table_Row As Integer

'Name columns

Range("I1").Value = "Ticker"
Range("J1").Value = "Yearly Change"
Range("K1").Value = "Percent Change"
Range("L1").Value = "Total Stock Value"

For Each ws In Worksheet

If ws.Cells(i + 1, 1).Value <> ws.Cells(i, 1).Value Then

ticker = Cells(i, 1).Value

Range("I" & Summary_Table_Row).Value = ticker

Summary_Table_Row = 2

Summary_Table_Row = Summary_Table_Row + 1

End If
Next

End Sub

Patrice T 24-May-20 15:10pm

Use Improve question to update your question.
So that everyone can pay attention to this information.

Member 14842334 24-May-20 15:22pm

I need to do a for loop through column A on each sheet in workbook and then print each unique value in column I of the first sheet.

Maciej Los · Answer 2 · 2020-05-24T23:02:00

Take a look at below code:

VB

Option Explicit 'force variable declaration

Sub CopyDataSetUnique()
    Dim srcWsh As Worksheet, dstWsh As Worksheet
    Dim i As Integer, j As Integer
    Dim srcRange As Range
    
    On Error GoTo Err_CopyDataSetUnique
    
    'first sheet
    Set dstWsh = ThisWorkbook.Worksheets(1)
    j = 2 'first row t insert data
    'second to last
    For i = 2 To ThisWorkbook.Worksheets.Count
        'set variable - type of worksheet
        Set srcWsh = ThisWorkbook.Worksheets(i)
        'get first column and copy used range only
        Set srcRange = srcWsh.UsedRange.Columns(1)
        If Not srcRange.Address Like "$A$*" Then
            MsgBox "There's no data in column 'A' in sheet: '" & srcWsh.Name & "'", vbInformation, "Information"
            GoTo SkipNext
        End If
        srcRange.Copy
        dstWsh.Range("A" & j).PasteSpecial xlPasteValues
        j = j + srcRange.Rows.Count
SkipNext:
    Next
    'get unique values
    dstWsh.UsedRange.RemoveDuplicates Columns:=1, Header:=xlNo
    
Exit_CopyDataSetUnique:
    On Error Resume Next
    'clean up
    Set srcRange = Nothing
    Set srcWsh = Nothing
    Set dstWsh = Nothing
    
    Exit Sub
    
Err_CopyDataSetUnique:
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_CopyDataSetUnique
    
End Sub

Above code copies data from column A in worksheets {second to last one} into column A in worksheet{first}
After that, RemoveDuplicates method is called to get unique data.