Object Required error

[jamesblack90]

Hey guys,

This is the first thing I've ever created in excel, so teaching myself from google as I go. There are two parts to my question, first of all, I am getting an 'object required' error when this code runs and it highlight the sub declaration as the issue...

Private Sub get_names()


Dim names(4) As String
Dim i As Integer, j As Integer


Set i = 20
Set j = 1


Do Until i = 26
    If IsEmpty(Home!Cells(i, 3)) Then
        names(1) = "Please enter names on Home Page"
        Exit Do
    End If
    
    names(j) = Home!Cells(i, 3).Value
    i = i + 2
    j = j + 1
    
Loop


End Sub

Basically I have the user input (up to) four names into Home!C20, 22, 24 and 26. I want the code to assign names(j) to the text that's been input into each of those cells (I originally had a really long and complicated if statements to test what cells were empty etc - a really silly way of doing it now that I discovered arrays). If anyone knows why I'm getting this error that would be great!

My second question would be, how do I then write something that determines how many names there are and inserts into a specific cell "Proposal for [name 1], [name 2]..." etc etc while putting the comma and the 'and' in the correct spot in the sentence (this was initially done in my super bad if statements setting an str variable manually, but surely there must be an easier way?

Thank you everyone in advance, I'm sure I'll have many, many more questions to come, but hopefully as I learn I'll be able to contribute back to you all as well

Cheers

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

OK, one question at a time. Your code should look more like this:

Private Sub get_names()

    Dim names(4) As String
    Dim i As Integer, j As Integer
    
    Let i = 20
    Let j = 1
    
    Do Until i = 26
        If IsEmpty(Worksheets("Home").Cells(i, 3)) Then
            names(1) = "Please enter names on Home Page"
            Exit Do
        End If
        
        names(j) = Worksheets("Home").Cells(i, 3).Value
        i = i + 2
        j = j + 1
        
    Loop

End Sub

"Let" is used to assign simple things like numbers and strings while "Set" is used to assign Objects like Worksheets or Ranges.
In practice, no-one uses Let any more. So saying: i - 1, is enough by itself - as you have done later on.

Also, Excel has annoying differences between what works in a worksheet and what is required in VBA. The way to define a worksheet is just one of many. One way to specify a worksheet called "Home" would be to say: Worksheets("Home").

 

[jamesblack90]

Thanks so much, RickXL!

I have adapted my code and now have the following, but with the following problems:
- If the user doesn't necessarily enter a full name on the Home worksheet (ie "James" instead of "James Lastname" the code breaks here:

names(j) = Left(Worksheets("Home").Cells(i, 3).Value, InStr(Worksheets("Home").Cells(i, 3), " ") - 1)

Logically, this is expected, but I can't work out how to get around this?

- I am assuming that when I declare my array names() it automatically defines them with something? Is there a better way of doing my IF statements? I've worked out that none of names(1 to 4) are actually null, even if nothing is typed into the appropriate cells on the Home sheet. :/ This means that even if all of the 'name' cells are empty, B15 is just returning "Proposal for , , and ."

Thanks again. hopefully my last question for the night!

Private Sub get_names()


Dim names(4) As String
Dim intro As String
Dim i As Integer, j As Integer


Let i = 20
Let j = 1


Do Until i = 26
    If IsEmpty(Worksheets("Home").Cells(i, 3)) Then
        Exit Do
    End If


    names(j) = Left(Worksheets("Home").Cells(i, 3).Value, InStr(Worksheets("Home").Cells(i, 3), " ") - 1)
    i = i + 2
    j = j + 1


Loop


If names(1) = vbNullString Then
    intro = "Proposal."
End If
    
If names(2) = vbNullString Then
    intro = "Proposal for " + names(1) + "."
End If


If names(3) = vbNullString Then
    intro = "Proposal for " + names(1) + " and " + names(2) + "."
End If


If names(4) = vbNullString Then
    intro = "Proposal for " + names(1) + ", " + names(2) + " and " + names(3) + "."
    Else
    intro = "Proposal for " + names(1) + ", " + names(2) + ", " + names(3) + " and " + names(4) + "."
End If


Range("B15").Value = intro


End Sub

Hi and welcome to the MrExcel Message Board.

OK, one question at a time. Your code should look more like this:

Private Sub get_names()

    Dim names(4) As String
    Dim i As Integer, j As Integer
    
    Let i = 20
    Let j = 1
    
    Do Until i = 26
        If IsEmpty(Worksheets("Home").Cells(i, 3)) Then
            names(1) = "Please enter names on Home Page"
            Exit Do
        End If
        
        names(j) = Worksheets("Home").Cells(i, 3).Value
        i = i + 2
        j = j + 1
        
    Loop

End Sub

"Let" is used to assign simple things like numbers and strings while "Set" is used to assign Objects like Worksheets or Ranges.
In practice, no-one uses Let any more. So saying: i - 1, is enough by itself - as you have done later on.

Also, Excel has annoying differences between what works in a worksheet and what is required in VBA. The way to define a worksheet is just one of many. One way to specify a worksheet called "Home" would be to say: Worksheets("Home").

 

[RickXL]

A few things:

Dim Names(4) as string

will give you five array elements 0 to 4, inclusive. So your first one was not being processed correctly.

I have used the Split command to split a string into parts. It returns the answers to a variant which it turns into an array. The first element is again 0.
The Until loop did not execute for i=26 so I changed it to greater than.
It is easier to concatenate all the strings as you read them in because you need the same type of loop to do it.
I have invented a connector variable (conn) that is added to the start of each name. It starts as "for" and then changes to "and". "Proposal" is added at the beginning and "." is added at the end.
Functions like Array, Split and Join arte well worth learning.
Also, I used a "with" construct to specify the worksheet. So when I say .Cells() later on, it means Worksheets("Home").Cells()

Private Sub get_names()

    Dim Name As Variant     ' This will be used as an array of names i.e. first name and last name
    Dim intro As String
    Dim i As Integer, j As Integer
    Dim conn As String      ' This starts as " for " then becomes " and "
    
    i = 20
    j = 1
    
    intro = "Proposal"
    conn = " for "
    
    With Worksheets("Home")
        Do Until i > 26
            If IsEmpty(.Cells(i, 3)) Then Exit Do
                
            Name = Split(.Cells(i, 3).Value)
            intro = intro + conn + Name(0)
            conn = " and "
            
            i = i + 2
            j = j + 1
        
        Loop
        
        intro = intro + "."
        
        .Range("B15").Value = intro

    End With

End Sub

 

[jamesblack90]

Thanks again so much Rick! I have read up on Split and Join so fingers crossed I won't need to ask anything related to those again

FYI I added this into your code so that instead of just outputting name AND name AND name AND name it puts commas and 'and' in their correct spots.

        intro = intro + conn + name(0)
        
        If IsEmpty(.Cells(i + 4, 3)) Then
            conn = " and "
        Else: conn = ", "
        End If

 

[RickXL]

Good thinking about the commas.

I have had a bad day today. i think I have got something wrong with every question I have looked at

Still, it should be easy for things to get better