VB code to Run formula untill blank cell

[Rajesh Kr Joshi]

Hi,

I would lke to drag a formula in row D27 towards right. It should stop once there is a blank cell.

Thanks
Rajesh

[Rick Rothstein]

I am not sure how your subject title relates to your actual request (your request deals with formulas, not VB code), but see if this answers your question...

It doesn't matter whether you drag down or to the right, the standard way to stop outputting visible results is to test the critical formula's cell for being empty and then output the empty string if it is. For example, let's say your formula in A2 was this...

="Value: "&A1

Obviously, as you drag it across, you will output just "Value: " when you run out of data in Row 1. If you modify the formula to this (testing the critical cell for being empty), you will only output something if there is a value in the corresponding Row 1 cell...

=IF(A1="","","Value: "&A1)

The general principle is this... see if a cell is blank and, if it is, output the empty string instead of the calculation.

[swinnersmusic]

Hi,

I would lke to drag a formula in row D27 towards right. It should stop once there is a blank cell.

Thanks
Rajesh

Hello Rajesh:
Step one:.....add a new module in the VBA environment
Step two.....paste in the subroutine below which will be effectively a macro called "fillacross"


Sub fillacross()
Dim myrow, mycol As Integer
mycol = 4 'the D Column
myrow = 27 'the 27th row

Do Until Cells(myrow, mycol + 1).Value = "" 'looping aross the columns until blank cell
'The next code is to copy across the column on the same row
Cells(myrow, mycol + 1).Value = Cells(myrow, mycol).Value
mycol = mycol + 1
Loop

End Sub
Step three put some different data in cells D27, E27, F27, G27 .....up to wherever you like but not too far across ...see below
Step four run the macro called "Fillacross"

Note:
This works...I have tested it.
BUT
You need an error trapping routine if it all goes wrong and reaches the column limitation of 256 Columns.
I have a routine for this if you would find it useful
This macro takes the value of the Cell D27 and overwrites all data in the 27th row from the D column onwards with this until a blank cell is reached

[Rajesh Kr Joshi]

Hi,

Thanks, This code is copying the value of D27, not the formula. I wants to copy the formula. And also is there a way to include the formula i wants to copy, in the VB code itself.

Thanks
Rajesh

[swinnersmusic]

Rajest, try changing the code from:

Do Until Cells(myrow, mycol + 1).value
to

Do Until Cells(myrow, mycol + 1).Formula
Alan

[swinnersmusic]

Rajest:
also experiment with recording a macro.
here is one that has captured entering a formula and filling it across:

Sub enterformula()
'
' enterformula Macro
'

'
Range("D6").Select
ActiveCell.FormulaR1C1 = "4"
Range("E6").Select
ActiveCell.FormulaR1C1 = "=100*RC[-1]"
Range("E6").Select
Selection.AutoFill Destination:=Range("E6:L6"), Type:=xlFillDefault
Range("E6:L6").Select
Range("F9").Select
End Sub

regards
Alan

[Rick Rothstein]

Okay, now I think I see what you want. If I am right, then you can do it using this single line of code...


Range(Range("D27"), Range("D27").End(xlToRight)).Formula = Range("D27").Formula

More generally, where the cell reference could change, this macro...


Sub ExtendFormulaToRight()
Dim StartCell As Range
Set StartCell = Range("D27")
Range(StartCell, StartCell.End(xlToRight)).Formula = StartCell.Formula
End Sub

Where I assume you might change the starting cell for your extending the formula to the right... just change the Set code line as needed.

[Rajan_Verma]

You can also try Autofill




Sub FillDown()
Dim rngRange As Range
Set rngRange = Range("A1:A5")
rngRange.Cells(1, 1).AutoFill Destination:=rngRange, Type:=xlFillValues
End Sub




Thanks
Rajan verma

[littleiitin]

I think this will serve your purpose.



Sub FillFormula()

Dim rngFillRange As Range

'Assuming Data is in column A and formula you want in column B
'Making Dynamic range for filling the data
'Let say Your first Formula exists in B1 then

Set rngFillRange = Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
rngFillRange.FillDown

End Sub





HTH
--------------------------