How to dim a range based upon multiple variables.

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
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....
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
david

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

What do you mean by 'Dim the cell'?

If you wanted to create a reference to a cell you would use something like this.
Code:
Dim Found As Range
Set Found = Range("E15")
Now you could use Found in the rest of the code.
 
Upvote 0
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
 
Upvote 0
Perhaps you mean something like this:

Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Using a loop:

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top