MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VB Code Corrected Solutions

  Asked By: Aubrey    Date: Aug 27    Category: MS Office    Views: 1594

my Current code is:

Range ("B9").Select
Dim i As Integer
For i = 4 to 7
ActiveCell.FormulaR1C1 = "R[-i]C[4]"
Next i
End Sub

This does not work. Im trying to set cell B9= to A6 (in this example),
then repeat the code with i=5 meaning B9 should now = A5 (in this
example). Could anyone help please.



3 Answers Found

Answer #1    Answered By: Eloise Lawrence     Answered On: Aug 27

I'm not sure I understand exactly what you want, but I'll try to help  you.

For starters, you seem to want a For loop running backwards (from 7 to
4, instead of from 4 to 7) if this is right, you shold use "For i = 7
to 4 step -1", where the "step -1" part will make it go backwards.

Secondly, the assignation line could be better like this:

The first number within Cells() is the row, and the second is the colmn.

I'm not sure why would you want the loop to start in 7 and use a "-1"
to get the 6, so I set  the loop to start in 6. If I understood what
you want to achieve, the code  should work  like this:

Dim iMyRow as integer

For iMiRow = 7 to 4 step -1

Answer #2    Answered By: Doyle Gonzalez     Answered On: Aug 27

Yes it is an odd question. Im not actually counting backward as such,
just that using the VB notation of R[i]C[4], i wasnt sure if it could
be incremented from 7 to 4 so i changed it backwards 4 to 7!
But using your 'Cells' notation is a lot easier.

This part of the code  is only the begining. Essentially i have 3 or a
range of cells. I want the sum of those cells to appear in the B9
cell. Normally this would be straight forward, but ultimately the 3
cells will depend on a criteria. So all may be summed or just 2 or
just the one depending if the meet the criteria!!!

Answer #3    Answered By: Balbir Kaur     Answered On: Aug 27

There are several problems that I see:

- Formulas start with an equals sign. Your formula doesn't have one,
so you're just plugging a text string into the cell  and it will
presumably display
exactly that string.

- The "i" in your string is simply that - "i". You want to put 4, 5, 6, 7
in instead.
You will need to break the string and concatenate the value of i or -i
into it. I.e.
"=R[" & -i & "]C[4]" or "=R[-" & i & "]C[4]"

- Your example  doesn't match your explanation. C[4] off B9 is
column E, not column A. You could use C[-1] to grab the column to the
left of your formula cell, or simply C1 to grab column 1. Similarly,
-i based on B9 will give you rows 5, 4, 3 and 2 respectively for i = 4
to 7.

- And last ... even if all this was correct, you would plug four formulas
the same cell in very quick succession. So ... it will show all four
but far too quickly for you to see the first three.

Didn't find what you were looking for? Find more on VB Code Corrected Solutions Or get search suggestion and latest updates.