0

I have tried everything that I have found and can think of with no luck.

I have a snipped of code that writes a sumifs for me based on set ranges which are defined.

Problem is the once the sumifs is written to a variable, I can not output that variable to a cell or range. I keep getting a 'application-defined or object-defined' error.

Line of code that is a problem:

spr = a

I don't believe the defined range for output is the problem because I am able to output a letter (entered to test) but when I try to output the variable it doesn't work and keeps giving me the 'application-defined or object-defined' error.

Please help!

Full code:

Sub create_SPAAT_Data()

Dim difs As Integer
Dim dife As Integer
Dim sps As Integer
Dim spe As Integer
Dim ros As Integer
Dim roe As Integer
Dim spr As Range

Set ro = Worksheets("RO_History_Processed")
Set sp = Worksheets("Shipment Processed")
Set sku = Worksheets("skus")
Set spa = Worksheets("SPAAT")
Set spr = spa.Range("O211")

' find start and end rows for differene, sp and ro in SPAAT front end
lrow = sku.Cells(Rows.Count, 1).End(xlUp).Row
difs = 5
dife = difs + (lrow - 1)
sps = dife + 1
spe = sps + ((lrow * 2) - 1)
ros = spe + 1
roe = ros + (lrow - 1)

' Calculate sp
    lrow2 = sp.Cells(Rows.Count, 2).End(xlUp).Row
    a = "=SUMIFS('Shipment Processed'!$C$1:$C$lrow2,'Shipment Processed'!$A$1:$A$lrow2,'SPAAT'!$Asps,'Shipment Processed'!$AB$1:$AB$lrow2,'SPAAT'!O$2,'Shipment Processed'!$AC$1:$AC$lrow2,'SPAAT'!O$4,'Shipment Processed'!$Z$1:$Z$lrow2,'SPAAT'!$Isps))"
        a = Replace(a, "lrow2", lrow2)
        a = Replace(a, "sps", sps)
    spr = a

End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Rob
  • 1
  • 1
  • Why are you limiting the ranges instead of using full column references? –  Mar 10 '19 at 19:51
  • 1
    Your formula ends with `))`. It should end with `)`. On a side note, `Replace` is not a good way to [insert a variable into Excel formula](https://stackoverflow.com/q/42503316/11683). – GSerg Mar 10 '19 at 20:06
  • 2
    Have you tried `spr.Formula = a` instead of `spr = a`? – Matteo NNZ Mar 10 '19 at 20:10
  • Thanks Gserg! That was the issue. Can't believe I missed that. – Rob Mar 10 '19 at 20:26
  • @GSerg - Good eye. That's odd that when you do this with VBA, you get an error...but if you type in a cell `=Sum(1,2))` Excel asks you if you mean `=SUM(1,2)` and doesn't just error out instead. But I suppose it does make sense, as you should correct the code itself rather than have the VBEditor make an assumption. – BruceWayne Mar 11 '19 at 02:08

0 Answers0