Hi Everyone!

I have a stock sheet with all models which I have already and now want to get some spcific models stock in a new sheet,,,

I use =SUMPRODUCT(([@[Model No]]=RefModelsT[Model No])*RefModelsT[Onhand])

to get the value, now i want to do it with VBA IF formula,,,  but when i use

Sub macro()


If range("NewT[@Model No]").value = range("RefModelsT[Model No]").value Then

What i want to do,,,, but error showing on the start of IF formula criteria ...

What i need to do if want to match this,, 

Please any of you have solution then please reply,,,

I attach below some screenshot and a attach for understanding,,,






4 Replies


Sub MyMacro()
Dim Destws As Worksheet
Dim lastrow As Long

Set Destws = Worksheets("NewT")
lastrow = Destws.Cells(Destws.Rows.Count, "A").End(xlUp).Row
    Destws.Range("C2:C" & lastrow).FormulaR1C1 = "=SUMPRODUCT(([@[Model No]]=RefModelsT[Model No])*RefModelsT[Onhand])"

End Sub

Maybe with these lines of code. In the attached file you can click the button in cell G2 to run the macro.

Bro yours is ok,,, but I want to do it by VBA IF function,,,if you can then Please
best response confirmed by KAM_Mumin (Contributor)


Do you want to sum in VBA only with IF THEN ELSE END IF statement without using a standard sum formula (SUMIF, SUMIFS, SUMPRODUCT)? If so i can't help you unfortunately.

can i change the cel in same time A1 TO B1 AND Reverse