Relative rather than absolute cell references in VBA

ChristineJ

Well-known Member
Joined
May 18, 2009
Messages
564
Office Version
  1. 365
Platform
  1. Windows
In my worksheet cell P3 contains the text value ID. I will have many other similar worksheets where ID appears in row 3, but not necessarily in column P. The value ID appears only once in the entire worksheet.

I pulled out three snippets of code (out of many) from a macro.

1. ThisWorkbook.ActiveSheet.Range("P4").Value & "'"


2. Select Case InfoBox.Popup("Take " & Range("Q4").Value - 1 & " of


3. Range("O19:O23").Select

What I would like for #1 above is instead of "P4" being typed in above, for it to locate the cell where ID is in the worksheet and automatically set the cell address to the same column ID is in, row 4.

For#2 above, instead of "Q4" being typed in above, it would locate the cell where ID is in the worksheet and automatically set the cell address to one column to the right, row 4.

Similarly, for #3 above, instead of "O19" and "O23" being typed in, it would locate the cell where ID is in the worksheet and automatically set the cell address to one column to the left, rows 19 and 23 respectively.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,311
Office Version
  1. 2010
Platform
  1. Windows
(Untested) Something like this should work...

1) MsgBox ThisWorkbook.ActiveSheet.Rows(4).Find("ID", , xlValues, xlWhole).Value & "'"

2) Select Case InfoBox.Popup("Take " & Rows(4).Find("ID", , xlValues, xlWhole).Offset(,1).Value - 1 & " of"

3) Intersect(Rows("19:23"), Rows(4).Find("ID", , xlValues, xlWhole).Offset(,-1).EntireColumn)
 

ChristineJ

Well-known Member
Joined
May 18, 2009
Messages
564
Office Version
  1. 365
Platform
  1. Windows
Thanks for your quick reply. I'm trying your code for #1. If I include the MsgBox, I get a syntax error. If I don't, the error is Object variable or With block variable not set. I'm not if taking the code out of context matters, so here is the full line.

sSQL = "SELECT * FROM Exercise1 WHERE [ID] ='" & ThisWorkbook.ActiveSheet.Range("P4").Value & "' AND [Take] = " & ThisWorkbook.ActiveSheet.Range("C7").Value

Appreciate it so much and will try to learn from your feedback.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,311
Office Version
  1. 2010
Platform
  1. Windows
Thanks for your quick reply. I'm trying your code for #1. If I include the MsgBox, I get a syntax error. If I don't, the error is Object variable or With block variable not set.

The only reason I can think of for that error is because the text ID is not in a cell by itself (no other text in the cell with it) anywhere on Row 4. I got the impression from your original message that at least one cell in the specified row would have ID in it... if that is not the case, perhaps I misunderstood what range you wanted to search.
 

ChristineJ

Well-known Member
Joined
May 18, 2009
Messages
564
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It is looking in row 3 for ID rather than row 4; that column letter should then be the column letter used in the cell value in row 4 that is in the code(my #1 above).

That said, when I change Rows(4) to Rows(3) in my #3 above, that one works PERFECTLY because you have the "19:23" in there. Very awesome.

I tried to tweak #1 but with no luck. Is there away to adjust #1?

Thank you so much!

 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,311
Office Version
  1. 2010
Platform
  1. Windows
It is looking in row 3 for ID rather than row 4; that column letter should then be the column letter used in the cell value in row 4 that is in the code(my #1 above).
Okay, try this then...

ThisWorkbook.ActiveSheet.Rows(3).Find("ID", , xlValues, xlWhole).Offset(1).Value & "'"
 

ChristineJ

Well-known Member
Joined
May 18, 2009
Messages
564
Office Version
  1. 365
Platform
  1. Windows
Yes, sir, that is it! I figured out #2 based on the other two. I'm just learning this, so it still seems like magic when it works. I appreciate your sticking with me on this. All the best, C
 

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,199
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top