Translating Columns into number for a loop

[kkernohan]

Here is the code that I am working on.

Range(Cells(11, Imult + 1), Cells(11, Imult + 1)).Value = "=sumif(Data!E:E, Results!A11, Data!N:N)"
Range(Cells(11, Imult + 2), Cells(11, Imult + 2)).Value = "=sumif(Data!E:E, Results!A11, Data!O:O)"
Range(Cells(11, Imult + 3), Cells(11, Imult + 3)).Value = "=B11/C11"

So the macro goes through the spreadsheet and fills in the formula for multiple columns. Each formula however needs to use different rows in the sumif formula.

for example.

The first set of data should have "=sumif(Data!E:E, Results!A11, Data!N:N)"

the next set of data should have "=sumif(Data!F:F, Results!F11, Data!N:N)"

the next set of data should have "=sumif(Data!G:G, Results!K11, Data!N:N)"

and so on.

The first part (Data!E:E) should increase by 1 every time.
The second part (Results!A11) should increase its row by 5 each time.

I would have no problem doing this using rows (with a couple loops) but I am unsure how to do this for the columns. I know the cell function allows you to use columns as numbers but how do I do this for the text that I am trying to paste into each cell?

Thanks for the help

 

[texasalynn]

you could put it into a loop and have something like this

x = 5
y = 1
For i = 1 to LC
Range(Cells(11, Imult + 1), Cells(11, Imult + 1)).Value = "=sumif(Data!" & Columns(x) & ", Results!" & Cells(11, y) & ", Data!N:N)"
x = x + 1
y = y + 4
Next i

 

[kkernohan]

Hi Texasaylnn, thanks for the answer

I tried using the columns function as a way to put a number to a column but its not working.

I tried this line to see if it would work

Dim x As Integer
x = 1
Range("A30").Value = Columns(x)

im getting an Application-defined or object-defined error.


this would work perfectly for me if there was a function that translated

ex.

Columns(5) would give E

am I simply doing something incorrect? or does columns not work like that

you could put it into a loop and have something like this

x = 5
y = 1
For i = 1 to LC
Range(Cells(11, Imult + 1), Cells(11, Imult + 1)).Value = "=sumif(Data!" & Columns(x) & ", Results!" & Cells(11, y) & ", Data!N:N)"
x = x + 1
y = y + 4
Next i

 

[texasalynn]

this will give you the letter
x = Columns(Chr(69)).Address(0, 0)

 

[texasalynn]

ok so I didn't get to test before, but have tested this without the sheet references

x = Columns(Chr(69)).Address(0, 0)
y = 1
For i = 1 To LR
Range(Cells(11, Imult + 1), Cells(11, Imult + 1)).Value = "=sumif(" & x & ", " & Cells(11, y).Address(0, 0) & ", N:N)"
x = Columns(Chr(69 + 1)).Address(0, 0)
y = y + 4
Next i

 

[p45cal]

just a query; I see the likes of:

Range(Cells(11, Imult + 1), Cells(11, Imult + 1)).Value =
being used, but

1. Isn't
Range(Cells(11, Imult + 1), Cells(11, Imult + 1)).Value =
the same as
Cells(11, Imult + 1).Value =

2. Should you be using
.Formula
instead of
.Value

??

 

[kkernohan]

Hey, thanks for the help but I am still having a bit of a problem implementing the solution into my code. I decided to try the brute force method and use Chr to build the code then use a loop to go through the letters for columns.

the only problem is that I get an error every time i put chr(61) the equals sign. I tried also to put a dummy variable (kevin) and then try to find and replace with an equals sign after which is also giving me an error. i have put both examples below. Is there any way to get around either of these problems?

Thanks for the help everyone

Cells(11, Imult + 1) = chr(61) & "sumif " & Chr(40) & "Data " & Chr(33) & " " & Chr(69) & Chr(58) & Chr(69) & Chr(44) & " Results " & Chr(33) & Chr(65) & Chr(49) & Chr(49) & Chr(44) & " Data " & Chr(33) & Chr(78) & Chr(58) & Chr(78) & Chr(41)
Cells(11, Imult + 2) = "kevinsumif " & Chr(40) & "Data" & Chr(33) & Chr(69) & Chr(58) & Chr(69) & Chr(44) & "Results" & Chr(33) & Chr(65) & Chr(49) & Chr(49) & Chr(44) & "Data" & Chr(33) & Chr(79) & Chr(58) & Chr(79)
Cells(11, Imult + 3) = "kevinsumif " & Chr(66) & Chr(49) & Chr(49) & Chr(47) & Chr(67) & Chr(49) & Chr(49)

 

[Norie]

Have you considered using R1C1 notation?

Then you could actually use numbers for the columns instead of rows.

For the first 2 formulas in your original post you could try something like this.

Dim rng As Range
Dim IMult As Long
    For IMult = 1 To 10
    
        Set rng = Cells(11, IMult + 1)
    
        rng.FormulaR1C1 = "=SUMIF(Data!C5, Results!R11C" & (1 + (IMult - 1) * 5) & ", Data!C14)"

        rng.Offset(, 1).FormulaR1C1 = "=SUMIF(Data!C5, Results!R11C" & (1 + (IMult - 1) * 5) & ", Data!C15)"
        
    Next IMult

I know this won't do what you want, but for me anyway what that is isn't clear.

For example how exactly are you looping?

 

[kkernohan]

Thanks Norie. that might work, ive never used R1C1 before. I will give it a try.

 

[p45cal]

If Data!N:N stays the same throughout then try:

DataCol = 4
ResultsCol = 1
For i = 1 To 3 '4,5, whatever
  Cells(11, Imult + i).Formula = "=sumif(Data!" & Columns(DataCol + i).Address & ", Results!" & Cells(11, ResultsCol).Address & ", Data!N:N)"
  ResultsCol = ResultsCol + 5
Next i

If Data!N:N is supposed to change that's easy to adjust.