VBA: how can I use relative cell address

[h711]

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

 

[h711]

I should re-write my example. On an empty sheet:

Range ("A1").Value = "aaa"

The next value should be input to A2, then A3. These values could be changed. For example "bbb" could be inputted to A2, but if the condition of this input is not met, then "bbb" is dropped, and next item "ccc" will be inputted to A2. This condition check -- insert to the next row, will go on.

Sorry about the bad example I used first time.

 

[erik.van.geit]

not sure if this example is actually better...
I do not understand what it adds to the previous remarks?

everytime a cell to copy is found, you can increase a variable

i = i + 1
Range("A1").Offset(i, 0) = valuewhoneedstogothere

is this what you were after?

 

[h711]

If I use this i=i+1, I will have to add it to every insertion

if condition met,
Range("A1").Value="aaa"
if condition met, i=i+1
range("A1").Offset (i, 0) = "bbb"
if condition met, i=i+1
range("A1").Offset (i, 0) = "ccc"
...

This should work, just not sure if it is the best way to do this.

Thanks for your help.

 

[erik.van.geit]

I think it is time to provide

• a little table with data
• some actions you want to perform
• make clear how you start & what the expected results would be

think about something easy to work with

 

[Fazza]

Hi, h711.

There are usually many approaches to do any task in Excel and Excel VBA. Sometimes instead of asking questions related to one small step (that is then intended to be repeated many times in perhaps a far from efficient approach), it is better to explain the bigger picture, to canvas suggestions of a good approach. The performance difference between poor and good approaches can be many orders of magnitude. Such as (there are examples in forum threads) code that takes over and hour to do something that can instead be done in a fraction of a second!

Regards, Fazza

 

[vicsar]

*Old post, I know but here is an example that works for me. Plus there is always someone learning out there .

Type, or copy this into Excel

Column A	Column B
Start Time	End Time
4/7/2014 2:07:06 PM	4/7/2014 2:10:31 PM

<tbody>
</tbody>

Here a sample code, using realtive references

*For this example, before runing the code place the cursor in Column B (otherwise it won't give you the right answer)

Sub Calculate_Elapsed_Time()
' Using relative references set the formula to calculate the elapse time
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1]-RC[-2],""h:mm:ss"")"

' Copy and paste values
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    
' Free up memory (cleanup clipboard)
    Application.CutCopyMode = False

End Sub