CODEWITHSUNDEEP
stringexcelvba
ben
ben

Reputation: 799

Should be a simple loop procedure in VBA

In Excel I have a column of words. I believe you call words "strings" in the programming world.

Row by row, I need to take each word in the column and put single inverted commas around it.

For example, if the word in the cell is dog, I need to change it to 'dog'.

I am trying to write a macro to do this, but I am already running into problems with the very first part of the code, which is just to import the column of words into from the excel spreadsheet.

My code is below. The Error message says "subscript out of range", but as you can see I have dimmed the array. What am I doing wrong? Thanks.

    Sub putquotes()
    Dim sym(1 To 162) As String
    For i = 1 To 162
       sym(i) = Worksheets("sheet1").Cells(i + 1, 1)
    Next i
    End Sub

Upvotes: 2

Views: 590

Answers (3)

brettdj
brettdj

Reputation: 55702

I think your issue is your sheet1 name which should probably be Sheet1

I would use something like this which will run on the first worksheet (see Set ws = Sheets(1))

Note that the third sheet would be Set ws = Sheets(3), or you could use Set ws = Sheets("Sheet1") if you did have such a sheet

This code:

  • will run independent of the sheet that is selected
  • looks from the first to last used cell in column A (rather than hard-coding 162 rows)
  • uses variant arrays rather than ranges for speed

  • adds a double '' to ensure the first is visible :)

    Sub PutQuotes()
Dim ws As Worksheet
Dim varList
Dim rng1 As Range
Dim lngCnt As Long
Set ws = Sheets(1)
Set rng1 = ws.Range(ws.[a1], ws.Cells(Rows.Count, "A").End(xlUp))
varList = rng1.Value2

For lngCnt = 1 To UBound(varList)
    If Len(varList(lngCnt, 1)) > 0 Then _
        varList(lngCnt, 1) = "''" & varList(lngCnt, 1) & "'"
Next
'dump updated array back over range
rng1.Value2 = varList
End Sub

Upvotes: 3

Dick Kusleika
Dick Kusleika

Reputation: 33175

You don't have a sheet named "Sheet1". Either:

  1. This code lives in a standard module in the workbook with the data and you've renamed the sheet, or
  2. The code lives in another workbook and you haven't properly qualified your Worksheets property

I'm going to assume the latter. When you use collection properties like Worksheets or Cells, Excel makes assumptions on who the parent is. An unqualified Worksheets call in a standard module will assume

ActiveWorkbook.Worksheets()

An unqualified Worksheets call in the ThisWorkbook module will assume

ThisWorkbook.Worksheets()

To check where the problem is, add this line to your code

Debug.Print Worksheets("Sheet1").Parent.Name

That will tell you which workbook Excel is using and may be different than you want.

To avoid bad guessing, it's best to fully qualify your references. For instance, if you're opening the workbook with the data, it might look like this

Sub putquotes()

    Dim wb As Workbook
    Dim sym(1 To 162) As String
    Dim i As Long

    Set wb = Workbooks.Open("Path\Name")

    For i = 1 To 162
        sym(i) = wb.Sheets("Sheet1").Cells(i + 1, 1)
    Next i

End Sub

Holding that wb reference is an easy way to qualify the reference. If you're not opening a separate file in code, you can just qualify explicitly like

ThisWorkbook.Worksheets("Sheet1")
ActiveWorkbook.Worksheets("Sheet1")
Workbooks("Mybook.xlsx").Worksheets("Sheet1")

A better way to read cell values into an array is like this

Sub putquotes()

    Dim wb As Workbook
    Dim sym As Variant
    Dim i As Long

    Set wb = Workbooks.Open("Path\Name")
    sym = wb.Sheets("Sheet1").Range("A2").Resize(162, 1).Value

    For i = LBound(sym, 1) To UBound(sym, 1)
        Debug.Print "'" & sym(i, 1) & "'"
    Next i

End Sub

That will give you a two-dimensional-base-1 array, which you may not like, but it's faster than reading them in one at a time.

Upvotes: 2

Rich Andrews
Rich Andrews

Reputation: 4188

I believe you want something like this...

Public Sub DoQuotes()
    Dim iRow As Integer
    Dim Result() As String
    iRow = 1

    Do While Not IsEmpty(Sheet1.Cells(iRow, 1))
        ReDim Preserve Result(iRow - 1)
        Result(iRow - 1) = "'" & Sheet1.Cells(iRow, 1) & "'"
    iRow = iRow + 1
    Loop

    For Each x In Result
        MsgBox (x)
    Next x

End Sub

However, bear in mind that Excel will treat the first quote as a text delimiter so it whilst the value in the array is 'something' it will look like something' in Excel.

Just a general aside point, try to avoid calls to Worksheets() instead use the strongly typed Sheet1 object - saves all sorts of future pain if the worksheets get renamed. You can see what the sheets are "really" called in the vba editor. It will say something like Sheet1(MyWorksheet)

Upvotes: 1

Related Questions