If statement

[goldie12]

Wanted to know how I can have the following done only if there is a value in Column E. Basically, to have the formula only filled in up until there is no value in Column E.

Thanks

Range("I10").FormulaR1C1 = "=+RC[-5]/5280"
    Range("I10").Select
    Selection.AutoFill Destination:=Range("I10:I65536"), Type:=xlFillDefault

[Bob Phillips]

Wanted to know how I can have the following done only if there is a value in Column E. Basically, to have the formula only filled in up until there is no value in Column E.

Thanks

Range("I10").FormulaR1C1 = "=+RC[-5]/5280"
    Range("I10").Select
    Selection.AutoFill Destination:=Range("I10:I65536"), Type:=xlFillDefault

With Range("I10")
    .FormulaR1C1 = "=RC[-5]/5280"
    .AutoFill Destination:=.Resize(Cells(Rows.Count, "E").End(xlUp).Row - 9), Type:=xlFillDefault
End With

But maybe you mean

.FormulaR1C1 = "=RC[-4]/5280"

[goldie12]

I get the following error message:

Runtime error '1004'
AutoFill method of Range Class failed

and it points to:

.AutoFill Destination:=.Resize(Cells(Rows.Count, "I").End(xlUp).Row - 9), Type:=xlFillDefault

[Bob Phillips]

I get the following error message:

Runtime error '1004'
AutoFill method of Range Class failed

and it points to:

.AutoFill Destination:=.Resize(Cells(Rows.Count, "I").End(xlUp).Row - 9), Type:=xlFillDefault

I had corrected it, you obviously got it before I did. Try

With Range("I10")
    .FormulaR1C1 = "=+RC[-5]/5280"
    .AutoFill Destination:=.Resize(Cells(Rows.Count, "E").End(xlUp).Row - 9), Type:=xlFillDefault
End With

[goldie12]

Thanks I changed it and it worked fine.

One more question:

Wanted to know how I can have the following done only if there is a value in Column Row 9 Column. Basically, to have the formula only filled in to the right up until there is no value in Row 9 Column N.

Range("N10:N16").Select 
   Selection.AutoFill Destination:=Range("N10:BN16"), Type:=xlFillDefault

[Bob Phillips]

Thanks I changed it and it worked fine.

One more question:

Wanted to know how I can have the following done only if there is a value in Column Row 9 Column. Basically, to have the formula only filled in to the right up until there is no value in Row 9 Column N.

Same principle

Dim iLastColumn As Long
    iLastColumn = Cells(9, Columns.Count).End(xlToLeft).Column
    If iLastColumn > 14 Then
        With Range("N10:N16")
            .AutoFill Destination:=.Resize(, iLastColumn - 13), Type:=xlFillDefault
        End With
    End If

[goldie12]

Thank you

Can you use this same principle when assigning the seriescollection values for a chart?
Example:

As long as there is a value in Row 9 Column N, then the series is equal to Row 10 Column N all the way to the right.

[Bob Phillips]

Thank you

Can you use this same principle when assigning the seriescollection values for a chart?
Example:

As long as there is a value in Row 9 Column N, then the series is equal to Row 10 Column N all the way to the right.

If you mean such as extending it with

Worksheets(1).ChartObjects(1).Chart. _     SeriesCollection.Extend Worksheets(1).Range("c1:c10")

then yes, same principle.

[goldie12]

I'm not sure that Extends will work, because the range is not definite.

How about assigning the source data? What I have now is the user inputting this into an input box. I really don't want them to have to do this, but it is the only thing that I have come up with. Any suggestions?

Example:

The source data range will be:

=Data!$K9:$K16,Data!$N9:$(This value will depend on the data range selected by the user, Basically I need an IF statement to say set this value depending on how far to the right the values go in Row 9 Column N to the right)16(This value will always be 16 because that is the ending value in the Row)

[Bob Phillips]

I'm not sure that Extends will work, because the range is not definite.

How about assigning the source data? What I have now is the user inputting this into an input box. I really don't want them to have to do this, but it is the only thing that I have come up with. Any suggestions?

Example:

The source data range will be:

=Data!$K9:$K16,Data!$N9

:$(This value will depend on the data range selected by the user, Basically I need an IF statement to say set this value depending on how far to the right the values go in Row 9 Column N to the right)16(This value will always be 16 because that is the ending value in the Row)

Anywhere a range is defined, you apply that principle, as it is just a way of determing an end point to the range.

Why don't you try it and see. You won't learn anything if I just do it for you every time. Try it and post back your code when you reach a block.

[goldie12]

Sub LastCellInRow() 
Sheets("Data").Select 
Static myDataRange As Range 
Dim MyCell As String
For i = 1 To 16 
If [N9].Offset(0, i).Value = 0 Then 
Else 
MyCell = [N9].Offset(0, i).Address(rowabsolute:=False, columnabsolute:=False) 
End If 
Next i
ActiveSheet.ChartObjects(1).Activate 
ActiveChart.SetSourceData Source:=Sheets("Data").Range("K9:K16,N9:" & MyCell & 16), PlotBy:= _ 
xlRows 
End Sub

This works, however, I am trying to include the following as the source data:

=Data!$K$9:$K$16,Data!$N9:$whatever$16 this will always be row 16.

Basically it is two ranges that need to be selected and the last part of the last range is dependant upon if there are values in Row 9, but it will always include up until row 16.

[Bob Phillips]

It doesn't look as though you have tried to implement the principle to me!

Basically it is two ranges that need to be selected and the last part of the last range is dependant upon if there are values in Row 9, but it will always include up until row 16.

What does this mean? Which part is the variable bit to test for? Of course, with your attempt it will probably be clearer.