how do I get a cell's location as a range in excel vba?

dwstein

i'm trying to lookup an email in a column, then use that email's location to find other information in the relevant row. here's my code so far:

Dim wsUsers As Worksheet            
Set wsUsers = Worksheets("userOutput.csv")
Dim userRows As Integer
userRows = wsUsers.Cells(Rows.Count, 1).End(xlUp).Row

Dim userEmail As Range

Dim userEmailColumn As Range
Set userEmailColumn = wsUsers.Range(Cells(2, 4), Cells(userRows, 4))
Dim findEmail As Variant
Set findEmail = userEmailColumn.Find("[email protected]")

Dim userEmailAd As Range
Set userEmailAd = findEmail.Address
MsgBox userEmailAd
stop

I'm getting runtime error 424, object required and it highlights the line: Set userEmailAd = findEmail.Address

How do I get a range so I can then use offset to find other information in the row? Or, is there a better way? I'm not using vlookup because column is not the first one in the overall range.

Patrick Lepelletier
userRows = wsUsers.Cells(Rows.Count, 1).End(xlUp).Row

Set userEmailColumn = wsUsers.Range(Cells(2, 4), Cells(userRows, 4))

should rather be:

userRows = wsUsers.Cells(wsUsers.Rows.Count, 1).End(xlUp).Row

Set userEmailColumn = wsUsers.Range(wsUsers.Cells(2, 4), wsUsers.Cells(userRows, 4))

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How to select range in Excel ListColumn's DataBodyRange from second to second last cell with VBA

How do I get a cell's position within a range?

Excel VBA: How do I assign a range to a variable?

How can I locate the cell and do math by the data resulted from other cell in Excel-VBA?

excel vba replace range cell

How do I get rid of a circular reference in a VBA dynamic range?

Get Cell reference for TOP 3 numbers within a Range (Excel VBA)

How do I get Excel to refresh a specific cell

How to get a list of a cell's font values in Excel using VBA

How do I prevent an excel table's formatting from overwriting a range's existing formatting in VBA?

How do I make the cell Range work in VBA?

VBA - Excel - Finding the cell location of a userform TextBox populated by a for i loop?

How do I get the day name into a cell in Excel?

Get one cell from passed column/row/range VBA Excel

Excel VBA: How do I PasteSpecial into same row of Cell location after Cell is changed?

How do I get partial cell styling in excel using EPpplus?

How do I change the value in an Excel table cell without specifying a cell reference in VBA

In Excel, how do I check if a cell is in a list of values (a range of cells)

How do I write a regex for Excel cell range?

How in VBA Excel get the range of arguments from formula in the cell?

VBA - how do I divide a range of cells by a fixed cell

How do I get the Cell value in Excel using an environmental variable for a VBA If statement

How do I find out why I get an error when writing to an Excel cell with VBA?

How do I convert the Excel formula =STDEV.S(IF(FREQUENCY(range,range),range)) into VBA code?

VBA How do I conditionally format a range of cells based on the value of one cell in that range?

Selecting a Named Range Cell Location EXCEL VBA

How do I check which cells belong to a merged cell and get the merged cell's value using c# Excel Interop?

How do I change a cell's value based on the movement of a shape (or textbox, or picture) in excel VBA

Excel VBA - get cell row for a range from another cell value

TOP Ranking

HotTag

Archive