0

I am trying a calculate sum product using VBA. The code is as 

Sub Result()
    ActiveCell.Formula = "=SUMPRODUCT(B4:QF4,B5004:QF5004)"
End Sub

There is a variable in the program and the number of columns changes every time. I tried defining the column "QF" as a number, but this is showing an error. How do I define a column as a number in my code? I have tried the following code. 

Sub Result()
    ActiveCell.Formula = "=SUMPRODUCT(B4:(" & (FinalR - j) & ")4,B(" & (FR - nobis + j + 1) & "):(" & (FinalR - j) & ")5004)"
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Raj
  • 27
  • 2
  • 1
    `OFFSET` and `RESIZE` in your code you need `..B4:B" & (FinalR - j)....` – Nathan_Sav Apr 27 '17 at 12:17
  • Possible duplicate of [Function to convert column number to letter?](http://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter) – David Rushton Apr 27 '17 at 12:36
  • I would use `.resize` like @Nathan_Sav stated `"& Range("B4").Resize(1, (FinalR - j)).Address(0,0)` You may need to readjust your math the second is the number of columns to include. – Scott Craner Apr 27 '17 at 13:14
  • [Avoid .Select and .Activate](http://stackoverflow.com/q/10714251/1188513) - working against the `ActiveCell` is a sign that you're *Doing It Wrong™*. – Mathieu Guindon Apr 27 '17 at 13:44

1 Answers1

0

How about this:

Sub Result()
    ActiveCell.Formula = "=SUMPRODUCT(B4:" & Left(Cells(1, FinalR - j).EntireColumn.Address(False, False), InStr(Cells(1, FinalR - j).EntireColumn.Address(False, False), ":") - 1) & "4,B5004:" & Left(Cells(1, FinalR - j).EntireColumn.Address(False, False), InStr(Cells(1, FinalR - j).EntireColumn.Address(False, False), ":") - 1) & "5004)"
End Sub

I'm assuming that FinalR - j gives you the column number?

Jeremy
  • 1,337
  • 3
  • 12
  • 26
  • 1
    `.columns(FinalR - j).address(0,0)` would suffice for the complete columns address – Nathan_Sav Apr 27 '17 at 13:35
  • @Jeremy I need one more favor, How can I lock the cells. That is I want the code like below ActiveCell.Formula = "=SUMPRODUCT(B4:QF4,$B$5004:$QF$5004)" – Raj Apr 27 '17 at 14:15
  • I'm not sure exactly what you mean.. do you mean `"=SUMPRODUCT($B$4:$QF$4,$B$5004:$QF$5004)"`? – Jeremy Apr 27 '17 at 14:18
  • Sir, I need the code for the formula "=SUMPRODUCT(B4:QF4,$B$5004:$QF$5004)" – Raj Apr 27 '17 at 14:21
  • @Raj that is what you'll see in the excel cell but for your VBA code, you'll need to enter the code I gave you because you're dynamically referencing the column – Jeremy Apr 27 '17 at 14:26