Named Range Info Help

[Dr.Evil925]

I have a macro that sets a range named FoundCell.

Dim FoundCell As Range
Set FoundCell = .Find(what:=cbCust.Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=faulse)

How can i access the cell locations in it so i can change them, or reference them later in my code?

example
if "FoundCell" is range A1:A5 how can i see that info first, second how can i change that to A1:A10?

Thanks in advance.

 

[Dr.Evil925]

I want to put a value in the next empty cell under whatever cell FoundCell is.

 

[VoG]

See http://www.mrexcel.com/forum/showpost.php?p=2651871&postcount=6

 

[JoeMo]

Maybe this:

Dim FoundCell As Range, s As String
Set FoundCell = Cells.Find(what:=cbCust.Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not FoundCell Is Nothing Then
    If Not IsEmpty(FoundCell.Offset(1)) Then
        FoundCell.End(xlDown).Offset(1).Value = "NewCustomer"
    Else
        FoundCell.Offset(1).Value = "NewCustomer"
    End If
End If

 

[Dr.Evil925]

JoeMo
This looked like it would work but when i put it in i get a "Application-defined or object-defined error" on the "then" statement.

Dim FoundCell As Range
Application.ScreenUpdating = False
   With Worksheets("Account DB")
       With .Rows(1)
        Set FoundCell = .Find(what:=cbCust.Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=faulse)
        If Not FoundCell Is Nothing Then
        FoundCell.End(xlDown).Offset(1).Value = "New Customer"
        Else
        FoundCell.Offset(1).Value = "NewCustomer"
        End If
        End With

    End With

 

[Dr.Evil925]

Dont know what happened, something must have not copied over. I retyped the line and it works now.

Thanks JoeMo, and VoG

 

[Dr.Evil925]

OK found a problem. I get "Application-defined or object-defined error" when i dont have anything under "FoundCell" or rows 2,3,4... if i have something in row 2 then it works right.

 

[Dr.Evil925]

The problem was on my end.
Here is my finished code. I am open to any suggestions, but as it is now it works as i want it to.

Thanks for all your help.

Dim FoundCell As Range
Dim FoundName As Range
Application.ScreenUpdating = False
   With Worksheets("Account DB")
       With .Rows(1)
        Set FoundCell = .Find(what:=cbCust.Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=faulse)
            With FoundCell.EntireColumn
                Set FoundName = .Find(what:=cbName.Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=faulse)
                If FoundName Is Nothing Then
                    If Not IsEmpty(FoundCell.Offset(1)) Then
                    FoundCell.End(xlDown).Offset(1).Value = cbName.Value
                    Else
                    FoundCell.Offset(1).Value = cbName.Value
                    End If
                MsgBox "Added " & cbName.Value & "!"
                Else
                MsgBox cbName.Value & " already exists"
                End If
            End With
        End With
    End With
End Sub