CODEWITHSUNDEEP
excelvbascripting
CathalMF
CathalMF

Reputation: 10055

Type mismatch in excel vba when appending to a cell

Im trying to append some text to each of the header columns in my excel document with a vba script. 

Dim c As Range

For Each c In Sheet13.Rows(1)
    c.Value = c.Value & "bleh"
Next

However this is giving me a type mismatch error 13 and i dont know why. The cells just contain text.

Upvotes: 0

Views: 179

Answers (2)

A.S.H
A.S.H

Reputation: 29332

For Each c In Sheet13.UsedRange.Rows(1).Cells

.Cells to get individual cells, not entire rows.

.UsedRange to avoid going to the end of the row (XFD1). You can tweak it to get only non-empty, constant cells, i.e.:

For Each c In Sheet13.UsedRange.Rows(1).SpecialCells(xlCellTypeConstants)

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33682

Try the code below:

Option Explicit

Sub AddBleh()

Dim c As Range
Dim LastCol As Long
Dim HeaderRng As Range

With Sheet13
    ' find last column with data in header row
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

    ' set Range to only header row where there is data
    Set HeaderRng = .Range(.Cells(1, 1), .Cells(1, LastCol))

    For Each c In HeaderRng.Cells
        c.Value = c.Value & "bleh"
    Next

End With

End Sub

Upvotes: 2

Related Questions