Loop works but not the if

[Eddie Estrella]

Good Afternoon everyone,
I am trying to get this Sub Routine to add the formula based on if the cell is blank or not.
The loop goes through it's cycle, but only inserts a formula into cell Q2. This is my first attempt, but I can't see what I am missing. Thank you for you help

Sub getRealDemand()

Sheets("MatReq").Range("Q1").Value = "Combined OH"


If Range("C2").Value = "" Then

Range("Q2").Value = "=INDEX(Table1[OH],MATCH(B2,Table1[P#],0))+SUMIF(Portalinfo!$B$2:$B$766,B2,Portalinfo$H$2:$H$766)"

Else
Range("Q2").Value = "=INDEX(Table1[OH],MATCH(C3,Table1[Compo],0))"

End If



Range("D2").Select
Range("D2:D1841").Formula = "=SUM(E22)"

' Final Format
Range("A1:Q1").Interior.Color = RGB(146, 205, 220)
Range("A:Q").Columns.AutoFit
Range("D2").Select
ActiveWindow.FreezePanes = True
ActiveSheet.Range("A:Q").AutoFilter



End Sub

 

[vletm]

1) Where is a loop?
2) If loop somewhere then You're always setting something to cell [Q2] !
... a basic version of You code ...

Sub Starts_here()
    If Range("C2").Value = "" Then
        Range("Q2").Value = something
    Else
        Range("Q2").Value = something_else
    End If
End Sub

 

[Eddie Estrella]

Thank you for the reply.
I guess I don't have a loop... I'm trying to make it insert one formula if C2 is blank and the other is C2 isn't blank and continue down the list until the end. So far on Q2 has gotten the formula and I can see why. Can you help me with this? the example I've read are true/false and then make msgbox pop up.

 

[Nebu]

Hi:

Try this code, not tested.

Sub getRealDemand()
Application.ScreenUpdating = False

With Sheets("MatReq")
    i& = .Cells(Rows.Count, 3).End(xlUp).Row
    .Range("Q1").Value = "Combined OH"
    .Range("Q2").Value = "=IF(C2="",INDEX(Table1[OH],MATCH(B2,Table1[P#],0))+SUMIF(Portalinfo!$B$2:$B$766,B2,Portalinfo$H$2:$H$766),INDEX(Table1[OH],MATCH(C3,Table1[Compo],0)))"
    .Range("Q2:Q" & i).FillDown
    .Range("D2").Formula = "=SUM(E2:P2)"
    .Range("D2:D" & i).FillDown
End With

' Final Format
With Sheets("MatReq").Range("A:Q")
    .Columns.AutoFit
    .AutoFilter
End With
Sheets("MatReq").Range("A1:Q1").Interior.Color = RGB(146, 205, 220)
Sheets("MatReq").[D2].Activate
ActiveWindow.FreezePanes = True

Application.ScreenUpdating = True
End Sub

Thanks

 

[Eddie Estrella]

Thank you for the response. I tried it out, but I got a Run-time error '1004' and debug highlights:
.Range("Q2").Value = "=IF(C2="",INDEX(Table1[OH],MATCH(B2,Table1[P#],0))+SUMIF(Portalinfo!$B$2:$B$766,B2,Portalinfo$H$2:$H$766),INDEX(Table1[OH],MATCH(C3,Table1[Compo],0)))"

 

[NARAYANK991]

Hi ,

Change the line of code to :

.Range("Q2").Value =
=IF(C2="",INDEX(Table1[OH],MATCH(B2,Table1[P'#],0))+SUMIF(Portalinfo!$B$2:$B$766,B2,Portalinfo!$H$2:$H$766),INDEX(Table1[OH],MATCH(C3,Table1[Compo],0)))

Narayan

 

[NARAYANK991]

Hi ,

Sorry , but the corrected version would be :

.Range("Q2").Value = "=IF(C2="""",INDEX(Table1[OH],MATCH(B2,Table1[P'#],0))+SUMIF(Portalinfo!$B$2:$B$766,B2,Portalinfo!$H$2:$H$766),INDEX(Table1[OH],MATCH(C3,Table1[Compo],0)))"

where the corrections have been highlighted in green.

Narayan

 

[Eddie Estrella]

That worked! Thank you very much