VBA SUMIFS with R1C1

[grinex]

I have this formula that i need to interpret into a vba script using, the formula is in " ";

the formula works fine within excel, i just cant get my macro to input the same formula into my designated cell.

ActiveCell.FormulaR1C1 = "=SUMIFS(H:H,D:D,">="&R[-2]C[0],D:D,"<="&R[-1]C[0])"

cheers for any help

 

[pgc01]

Hi
Welcome to the board

You cannot mix R1C1 and A1 references in a formula. Either one or the other.

What is the formula you'd write in the worksheet and in which cell would you write it?
Also, do you prefer to write it using R1C1 or A1 syntax?

 

[grinex]

Thanks,

id like to use RC

This is the code I require;

ActiveCell.Offset(1, 0).Select
With Selection
'SUMIFS(H:H,D:D,">="&B14,D:D,"<="&B15)
End With
   
ActiveCell.Offset(1, 0).Select
With Selection
'22.8-B16
End With


ActiveCell.Offset(1, 0).Select
With Selction
'SUMIFS(M:M,D:D,">="&B14,D:D,"<="&B15,I:I,"=Y")
End With

 

[pgc01]

Hi

Remark: you don't usually use Select in vba. Makes the code confusing and inefficient.

If I understand correctly you want to write the formula

=SUMIFS(H:H,D:D,">="&B14,D:D,"<="&B15)

in B16 using the R1C1 notation

Try:

Range("B16").FormulaR1C1 = "=SUMIFS(C[6],C[2],"">=""&R[-2]C,C[2],""<=""&R[-1]C)"

Remark 2: all the addresses in the formula you posted are relative. Is this really what you want?

 

[database_coder]

You can just finish your code with .Value or .Formula instead

 

[pgc01]

You can just finish your code with .Value or .Formula instead

Hi

I really don't understand your post.

Can you explain what you you mean?

I asked the OP if he preferred the R1C1 or A1 notation and he clearly said that, in this case, he prefers R1C1.

Also it's true that you can calculate and post in the cell the value corresponding to the result of the formula but that's also not what was asked.

Really confused here.

Can you please clarify?

 

[database_coder]

Try

Range("B16").Formula = "=SUMIFS(C[6],C[2],"">=""&R[-2]C,C[2],""<=""&R[-1]C)"

 

[database_coder]

Try

Range("B16").Formula = "=SUMIFS(C[6],C[2],"">=""&R[-2]C,C[2],""<=""&R[-1]C)"

Sorry I think that is not right. Please try this. Also as a beginner in VBA it is sometimes very confusing to use R1C1 notation. I use it but for simplicity you should change your options to using A1 notation as the code I wrote is for A1 notation.

are you only entering this in one cell? if so use -

Worksheets("Master_List").Range("B16").Formula = "=VLOOKUP(F2,'1.0'!A:A,1,0)"

using multiple cells, and finding the number of cells that you need to enter the SAME formula in

Dim RowCount As Long

RowCount = Worksheets("Sheet1").Range("B16").End(xlDown).Row
Worksheets("Sheet1").Range("B16:B" & RowCount).Formula = "=VLOOKUP(F2,'1.0'!A:A,1,0)"

Hopefully this helps.