Declaring a Variable

[bhsoundman]

Can someone tell me what I'm doing wrong? This macro returns a 1004 error. I know the problem is regarding addr. Thanks in advance

Sub Venue_Rolodex()

Dim venue As String
Dim finalrow As Integer
Dim i As Integer
Dim addr as string

venue = Sheets("rolo").Range("f1").Value
finalrow = Sheets("rolo").Range("a1000").End(xlUp).Row
addr = Sheets("Rolo").Range("A" & i).Value


For i = 2 To finalrow
If Cells(i, 1) = venue Then
Range("G5").Value = addr

End If

Next i
Application.CutCopyMode = False
Range("f1").Select

End Sub

 

[Cubist]

There are a few issues with your code but try this.

Sub Venue_Rolodex()

    Dim venue As String
    Dim finalrow As Long
    Dim i As Long

    With Sheets("rolo")
        venue = .Range("F1").Value
        finalrow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For i = 2 To finalrow
            If .Cells(i, 1).Value = venue Then
                .Range("G5").Value = .Cells(i, 1).Value
                Exit For
            End If
        Next i
    End With

End Sub

 

[Alex Blakenburg]

As far as I can tell i = 0 at this point
addr = Sheets("Rolo").Range("A" & i).Value

 

[bhsoundman]

There are a few issues with your code but try this.

Sub Venue_Rolodex()

    Dim venue As String
    Dim finalrow As Long
    Dim i As Long

    With Sheets("rolo")
        venue = .Range("F1").Value
        finalrow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For i = 2 To finalrow
            If .Cells(i, 1).Value = venue Then
                .Range("G5").Value = .Cells(i, 1).Value
                Exit For
            End If
        Next i
    End With

End Sub

Thanks so much for the clean up! Is using addr not advised?

Thanks again!

 

[bhsoundman]

As far as I can tell i = 0 at this point
addr = Sheets("Rolo").Range("A" & i).Value

The part that was hanging was making a cell = addr

 

[Cubist]

Thanks so much for the clean up! Is using addr not advised?

Thanks again!

It is not that it's not advised but your syntax isn't doing what you expected it to be doing. Two issues:
You declared addr outside the loop so it doesn't increment with the row. It should be inside the for loop.
Secondly, as it's not getting incremented, the default value for declaring an integer is 0 so you're assigning the value of cell A0 to addr but cell A0 doesn't exist.

 

[bhsoundman]

It is not that it's not advised but your syntax isn't doing what you expected it to be doing. Two issues:
You declared addr outside the loop so it doesn't increment with the row. It should be inside the for loop.
Secondly, as it's not getting incremented, the default value for declaring an integer is 0 so you're assigning the value of cell A0 to addr but cell A0 doesn't exist.

Thanks for the explanation! I understand now.

 

[Cubist]

You're welcome.