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.