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.

 

[VoG]

FoundCell will only be a single cell. Try

Dim FoundCell As Range
Set FoundCell = .Find(what:=cbCust.Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not FoundCell Is Nothing Then MsgBox FoundCell.Address(False, False)

 

[Dr.Evil925]

ok

So how can i get the cell reference?
Or if it had an actual range how could i get the info?

 

[VoG]

You could assign it to a variable

Dim FoundCell As Range, s As String
Set FoundCell = .Find(what:=cbCust.Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not FoundCell Is Nothing Then s = FoundCell.Address(False, False)

But why not just use FoundCell directly?

 

[Dr.Evil925]

I have a row of "customers" and under each of them is a list of names. I am trying to find the first open cell under the customer picked by a combobox so i can add a new name to the list.

 

[VoG]

Try like this

Dim FoundCell As Range, s As String
Set FoundCell = .Find(what:=cbCust.Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not FoundCell Is Nothing Then FoundCell.Offset(1).Value = "New Customer"

 

[Dr.Evil925]

That works for the first name but if I have 2 or 3 names in the list already?
How can i get it to insert into an open cell, which might be row 2,3,4...?

 

[VoG]

Try like this

Range("A" & Rows.Count).End(xlUp).Offset(1).Value = "New Customer"

 

[Dr.Evil925]

Range("A" & Rows.Count).End(xlUp).Offset(1).Value = "New Customer"

maybe I am blind, but where would i put this.
after "If Not FoundCell Is Nothing Then" and change "A" to FoundCell"?

 

[VoG]

It isn't clear what you are trying to do. The last bit of code that I posted will add a value in the next empty row in column A.