How to dim a range based upon multiple variables.

[davidhall80]

I need to Dim the cell in column E based on columns B & C value.
If in column B it says "New York" & in column C is says "David". I need the cell to the right of "David" to be my Dim...Let's say... Dim Found as Range.

If Cell B15 said "New York" & Cell C15 said "David", I'd want to Dim cell D15 as "Found" as I could call upon Range(Found) later on in my Macro.


Thanks in advance....

 

[Norie]

david

That just doesn't make much sense, to me anyway.

What do you mean by 'Dim the cell'?

If you wanted to create a reference to a cell you would use something like this.

Dim Found As Range
Set Found = Range("E15")

Now you could use Found in the rest of the code.

 

[davidhall80]

Row 15 is a variable....It could be in another row. The only thing that I know is that New York is in column B & David is in column C

 

[VoG]

Perhaps you mean something like this:

Sub test()
Dim Found As Range
Set Found = Columns(2).Find(what:="New York")
If Found Is Nothing Then
    MsgBox "Text not found"
Else
    MsgBox "Found is in " & Found.Address(False, False)
End If
End Sub

 

[davidhall80]

Column 2 has "New York", Column 3 has "David", I need to Dim the cell in column 4 which will be in the same row that New york & David are...I am familiar with the Find function, but I don't know how to use it for multiple variables....only one at a time.....

In the below example, I'd want to Dim Cell D4 ($97.75) as Found because New York is in column B & David is in column C...two variables.

A B C D E
1 Boston David $15.36
2 Philly David $58.06
3 New York Joe $48.28
4 New York David $97.75
5

 

[davidhall80]

A..........B.............C................D
1......Boston........David........$15.36
2.......Philly.........David....... $58.06
3...... New York ...Joe .........$48.28
4 ......New York ..David .......$97.75

 

[VoG]

Using a loop:

Sub test()
Dim Found As Range, LastRow As Long, i As Long
Dim S1 As String, S2 As String
S1 = "New York"
S2 = "David"
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
For i = 1 To LastRow
    If Cells(i, 2).Value = S1 And Cells(i, 3).Value = S2 Then
        Set Found = Cells(i, 4)
        Exit For
    End If
Next i
If Found Is Nothing Then
    MsgBox "Text not found"
Else
    MsgBox "Found is in " & Found.Address(False, False)
End If
End Sub

This will be slow if there are a lot of rows.

 

[davidhall80]

Perfect....thanks a million