VBA: how can I use relative cell address

h711

Board Regular
Joined
Jun 23, 2008
Messages
164
Hi, is there a way I can use relative cell address in VBA? For example, in the following table:

<table style="border-collapse: collapse; width: 96pt;" border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" height="20" width="64">
</td> <td style="width: 48pt;" width="64">value</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">item 1</td> <td>apple</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">item 2</td> <td>peach</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">item 3</td> <td>mango</td> </tr> </tbody></table>
Instead of use

Range("B4").Value = "mango"


to insert mango to cell B4, I can use an address that's one row under peach, or one column to the right of item 3?

Thanks for your help
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
To expand on that here are two possibilities.

Code:
Sub test100()
Dim Found As Range
Set Found = Columns("A").Find(what:="item 3")
If Not Found Is Nothing Then
    Found.Offset(0, 1).Value = "mango"
End If
End Sub

Code:
Sub test101()
Dim Found As Range
Set Found = Columns("B").Find(what:="peach")
If Not Found Is Nothing Then
    Found.Offset(1, 0).Value = "mango"
End If
End Sub
 
Upvote 0
Hi,

about this line
Code:
Set Found = Columns("A").Find(what:="item 3")

Some years ago, I found out something by getting into trouble, so thought to share that with you :)

It is better to add more arguments, because the previous find will determine the next ones. If you used "find" manually or by code before and were searching (e.g.) for values and partial match, then the code will still use those arguments.

kind regards,
Erik
 
Upvote 0
Ok, I found the .Offset (rowOffset:= , columnOffset:= )

Now my problem is: instead of counting every position from A1, how can I use my last modified cell as a reference point for the next cell location?

Thanks.
 
Upvote 0
Thanks for all the reply. I basically working 2 excel sheet, one to read from, and one to write the result to.

My current problem is the one that I want to output results. This sheet will be long, and some item might be dynamic, so I need a way to output the result to a position that's relative to the last cell I just modified.

Thanks.
 
Upvote 0
Probably ActiveCell.Offset(,)

I tried activecell, but correct me if I am wrong. When the code modify a cell, it does not active that cell by default. So the activecell way usually doesn't point to the position I wanted it to be.

For example range("A1").value="abc"

does not active that cell, so the pointer is still point to the last cell that's been activated.

Thanks.
 
Last edited:
Upvote 0
Please post the code that you have so far and indicate what has to be changed.
 
Upvote 0
but if you wrote to a cell, you know the address (at least VBA does)

that was the purpose of VOGII's line
Code:
Found.Offset(0, 1).Value = "mango"

If you do not get it, then show us how you write to the cell...

best regards,
Erik
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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