VBA: using relative references in a for...next loop

[CedricMattelaer]

Just a really easy question, but after a day spent on the internet without finding the solution I thought I might subscribe to this forum and ask it here.

I currently have this line of code:

For i = 1 To 272
For j = 1 To 13

If Worksheets("exercice").Cells(i, 1) = "1" Then
Worksheets("exercice").Cells(344 + i, 3 + j) = "=C288"
Next i
Next j

Problem is, if j goes up with 1, I also want the relative reference to "=C288" to become "=D288".

Is there a way to do this?

I could write, for example, =Cells(288, 3+j) but then only the value of the cell is copied, and I need the relation between both (ie instead of "1" in that cell, I need "=D288")

I hope I explained it ok and thanks for sharing your knowledge!

 

[CedricMattelaer]

Thank you all for your remarks and lines of code.
As you probably have noticed, I am new to VBA programming, which is why I couldn't find another way to apply those formulas to different cells than with loops.

@Rick: your first solution worked ok but it is indeed quite inefficient to work with loops. Plus: it is difficult for me to implement the &Chr command if j is higher than 26.

@Hotpepper: Thanks for the idea. That works better, but how can I apply it downwards also? ie

If Cells(9, 18) = "1" Then Range("C344:P344").Formula = "=C288" Else

But now I want that the range below ("C345:P345") has the same formula, apart from the fact that the condition changes to Cells (10,18)="1"

@Rick: I tried your last code bit too but unfortunately it doesn't give me the solution I need. Too bad I don't have more VBA knowledge to be able to adapt it a bit.

Anyway, thank's a lot everybody!

 

[CedricMattelaer]

Perhpas I'll clarify a bit what I'm trying to do: I have one table (Table1) and I'm making another expanded version of that table putting zeroes in between the cells. I do that based on another table (Table2) with "1"'s and 0's where 1 means linking the tables and 0 means putting in a zero.

For example

Table 1:

3
4

Table 2:

1
0
0
1
0

Result
3 (well, =A1 in this case)
0
0
4
0

 

[CedricMattelaer]

Hey I finally got a perfectly working solution, probably not the best way to do it but here it is anyway:


If Cells(9 + i, 19) = "1" Then Range(Cells(344 + i, 3), Cells(344 + i, 16)).Formula = "=C288" Else

Thanks for your help!

 

[Rick Rothstein]

POST 1
-------------------
Table 1:

3
4

Table 2:

1
0
0
1
0

Result
3 (well, =A1 in this case)
0
0
4
0

POST 2
-------------------
Hey I finally got a perfectly working solution, probably not the best way to do it but here it is anyway:


If Cells(9 + i, 19) = "1" Then Range(Cells(344 + i, 3), Cells(344 + i, 16)).Formula = "=C288" Else

I must be missing something. First off, I'm guessing your Table #1 starts in C288 and runs to P559 (its a two dimensional table, right?); and that Table #2 runs from S9:S280; and that your output is to be located within the confines of range D344:Q615. Your Result table above seems to indicate that you want the first matching 1 from Table #2 to be placed in Column D at the corresponding row-offset with the formula =C288 and the second matching 1 from Table #2 to be placed in Column D at its corresponding row-offset with the formula =C289, etc.; however, unless I'm reading something wrong, your "working" solution appears to put the formula =C288 in each cell of these cells. Which did you want... =C288, =C289, =C290, etc. or =C288 repeated over and over again?

 

[Rick Rothstein]

A follow-up question to my last question. For your Result table... did you need the live link via the formulas you are placing there or would a copy (constant, not formula) of the data from the referenced cells be sufficient?

 

[CedricMattelaer]

Hi Rick,

Just in case you really are interested:
It is working but I explained it badly.

Actually, it is more like this:

table 1:

1
0
0
1
0

Table 2:

2 4
3 5

Result:

2 4
0 0
0 0
3 5
0 0

The dimensions of table 1 (in this simplified case, just one column) are C9:C281. I also have an aid to know which number it is from S9:S281 (ie 10010 becomes 11122). Both have dimension (273,1)
The dimension of table 2 was C288:P314 (dimension: 27,15)
The results table then has a dimension of (273,15), C344:P616.

I needed the references and nnot just the values.
The complete code I used was:

For i = 0 To 272


If Cells(9 + i, 3) = "1" Then

If Cells(9 + i, 19) = "1" Then Range(Cells(344 + i, 3), Cells(344 + i, 16)).Formula = "=C288" Else
If Cells(9 + i, 19) = "2" Then Range(Cells(344 + i, 3), Cells(344 + i, 16)).Formula = "=C289" Else

...
Else: Range(Cells(344 + i, 3), Cells(344 + i, 16)).Formula = "0"
Next i

 

[Rick Rothstein]

I'm guessing you have a lot of If..Then statements in your final code. If I understand your structure correctly (thanks for the additional description), then I think we can eliminate your "aid table" (located at S9:S281) and remove all of your If..Then tests. Here is the code that I believe will do what your current working code does...

Sub Huh()
  Dim Counter As Long, Rw As Range
  Const Table1 As String = "C9:C281"
  Const Table2 As String = "C288:P314"
  Const Result As String = "C344:P616"
  With Range(Table1).Offset(Range(Result)(1).Row - Range(Table1)(1).Row)
    .Value = Range(Table1).Value
    .Replace 0, "", xlWhole
    With Intersect(.SpecialCells(xlCellTypeConstants).EntireRow, Range(Result))
      Counter = Range(Table2)(1).Row
      For Each Rw In .Rows
        Rw.Formula = "=C" & Counter
        Counter = Counter + 1
      Next
      Range(Result).SpecialCells(xlCellTypeBlanks).Value = 0
    End With
  End With
End Sub

One thing concerns me, though, and that is your little "in this simplified case, just one column" comment... I'm not sure what your actual structure is nor how it would affect the code I posted above.

Just a note about this... it is almost always a bad idea to try and simplify your questions for us as it almost always leads to a working solution for a problem that you don't actually care about (you notice the bad start we all got off to) and, most times, the patching of the code to fit your actual conditons is usually more involved than your mind's eye is envisioning.

 

[CedricMattelaer]

Hi Rick,

Your code does indeed work and is more efficient than the one I posted.
The reason I posted a simplified version is twofold: firstly, it is difficult to explain the complete problem on such a forum (that, by my knowledge, doesn't allow for excel files to be attached to a post). Secondly, I want it to be possible to altter the code. That's why I need to understand it first, and only ask for a tiny thing to be translated in code.

But obviously you're right. If I then try to apply it to another set of data, things I thought wouldn't matter kick in and mess up the outcome.

So, sorry and thanks a lot