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.

sorry I don't know what it changed the layout - I could figure out how to edit it either - if anyone could tell me how to edit it and reorganize my code I would be happy to — user1647145
– user1647145, Commented Sep 4, 2012 at 19:22
I think error is with your for each loop. rngLastRow is a range but Sheets("NewADUserAttribs").UsedRange.Rows.Count returns an integer. Have you tried just using Sheets("NewADUserAttribs").UsedRange? — Alex P
– Alex P, Commented Sep 4, 2012 at 19:28
Awesome! That did it! Thank you so much I have been swearing at this thing for hours. This is my first time playing with vba - more different from .net that I thought it was. — user1647145
– user1647145, Commented Sep 4, 2012 at 19:47
@user1647145 if you found an answer or someone gave you an answer you might want to post an answer of how it was solved — Nate-Wilkins
– Nate-Wilkins, Commented Oct 4, 2012 at 18:01

Tim Williams · Accepted Answer · 2012-09-04 19:32:10Z

4

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)

answered Sep 4, 2012 at 19:32

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

object required error vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related