object required error vba

Question

I have a macro which is supposed to concatenate various strings that the user puts in, to fill other cells for other active directory user attribs that are based on user provided attribs.

Option Explicit

Sub SubStringConcatenate()

'variables
Dim strFirstName As String
Dim strSurName As String
Dim strDomain As String
Dim strOU As String
Dim strChildOU As String

Dim intRowCount As Integer

Dim rngLastRow As Range

Set rngLastRow = Worksheets("NewADUserAttribs").Cells.End(xlUp)

'set first row to be processed
intRowCount = 2

'loop until all used rows have been processed
For Each rngLastRow In Sheets("NewADUserAttribs").UsedRange.Rows.Count

'define name variables
strFirstName = Cells(intRowCount, 1).Value
strSurName = Cells(intRowCount, 2).Value

'define initials cell location and concatenate
Cells(intRowCount, 3).Value = Left(strFirstName, 1) & Left(strSurName, 1)

' define fullname cell location and concatenate
Cells(intRowCount, 4).Value = strFirstName & " " & strSurName

'define SAM cell location and concatenate
Cells(intRowCount, 5).Value = Left(strFirstName, 2) & Left(strSurName, 4)

'define domain string variable and logon range and concatenate
Cells(intRowCount, 7).Value = strFirstName & "." & strSurName

'add 1 to row count to prepare for next row in table
intRowCount = intRowCount + 1

Next

End Sub

When I debug there are no errors. But when I try to run the macro, I get the object required error and it doesn't say where in the code it is. I think it is somewhere in the for each statement. I am using Excel 2010.

Answer 1

For Each rngLastRow In Sheets("NewADUserAttribs").UsedRange.Rows.Count 

should be

For Each rngLastRow In Sheets("NewADUserAttribs").UsedRange.Rows

but then you're not using rngLastRow within the loop. You can just use that (eg) like this:

rngLastRow.Cells(3).Value = Left(strFirstName, 1) & Left(strSurName, 1)