VBA-Select Cell that Intersects with specified row/column

darin100K

Board Regular
Joined
May 17, 2005
Messages
97
I'm writing a macro and need to select the cell that intersects with the column of a specified Emp ID and the last row in column A with text.

Both the date in Column A and the Emp ID are being pulled from another worksheet.

For instance, I have inserted 09/05/2006 in A13 and need to find the column that contains the specified Emp ID. So given the Emp ID is in D1, I need to select cell D13.

Another example, I have inserted 9/08/06 in A14 and the Emp ID is in column P. I need to select cell P14.

I hope I've explained this correctly. If someone could just give me a headstart on how to accomplish this I can tweak it to fit my needs.

Thanks for your help!!!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

abiram01

New Member
Joined
Aug 30, 2006
Messages
30
Hi,

Here in the folowing code, A1 is the column in which you insert the date and Columnd D contains the emp id

Code:
Dim rng
Dim i As Integer

rng = Range("A1").End(xlDown).Row

Cells(rng, 4).Select
 

darin100K

Board Regular
Joined
May 17, 2005
Messages
97
Hi,

Thanks for the quick response. The only problem I still have is that each date in Column A will have a different corresponding Emp id/Column. How can I use what you've posted but search for the appropriate number of columns?

Thanks again.
 

abiram01

New Member
Joined
Aug 30, 2006
Messages
30
Hi,

Thanks for the quick response. The only problem I still have is that each date in Column A will have a different corresponding Emp id/Column. How can I use what you've posted but search for the appropriate number of columns?

Thanks again.

I thought all the emp id will be in the same column. Is there any way that you add emp id data? can u send the work sheet ?


Regards,
Abhiram
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

Try
Code:
Sub sample()
Dim r As Range, c As Range, myId, myDate As Date
myId = CDate(InputBox("Enter Id"))
myDate = InputBox("Enter Date")
Set r = Cells.Find(myId,,,XlWhole)
If r Is Nothing Then MsgBox "Id Not found" : Exit Sub
Set c = Columns("a").Find(myDate,,,xlWhole)
If c Is Nothing Then MsgBox myDate & " Not found" : Exit Sub
MsgBox Intersect(r.EntireColumn, c.EntireRow).Address
End Sub
 

darin100K

Board Regular
Joined
May 17, 2005
Messages
97
The Emp IDs range from Column B to Column ZZ and will need to change to accomodate more/less employees.

I'm starting by importing a date from another worksheet into the first empty row in column A. I then need to find the column holding the correct Emp ID and select the cell that intersects this column with the row in column A with the imported date.

If this doesn't help explain my needs, I'll work on posting the spreadsheet. It's just such a hassle.

Thanks.
 

abiram01

New Member
Joined
Aug 30, 2006
Messages
30
Hi Darin,
Will the Emp ID in the work sheet is unique? If Emp Id unique then we can search for the emp id and find the cell. But the problem will be how do we get the emp id to search in the sheet.

Regards,
Abhiram.
 

Forum statistics

Threads
1,141,734
Messages
5,708,171
Members
421,550
Latest member
Dtcfire

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
Top