Relative rather than absolute cell references in VBA

[ChristineJ]

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.

 

[Rick Rothstein]

(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]

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]

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]

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]

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]

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