Results 1 to 9 of 9

Thread: VB code to Run formula untill blank cell

  1. #1
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14

    VB code to Run formula untill blank cell

    Hi,

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

    Thanks
    Rajesh

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    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.
    Last edited by Rick Rothstein; 05-16-2012 at 12:04 AM.

  3. #3
    Junior Member swinnersmusic's Avatar
    Join Date
    May 2012
    Posts
    6
    Rep Power
    0

    Cool solution to....filling across columns using VBA until blank cell is found

    Quote Originally Posted by Rajesh Kr Joshi View Post
    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"

    Code:
    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

  4. #4
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    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

  5. #5
    Junior Member swinnersmusic's Avatar
    Join Date
    May 2012
    Posts
    6
    Rep Power
    0
    Rajest, try changing the code from:
    Code:
    Do Until Cells(myrow, mycol + 1).value
    to
    Code:
    Do Until Cells(myrow, mycol + 1).Formula
    Alan

  6. #6
    Junior Member swinnersmusic's Avatar
    Join Date
    May 2012
    Posts
    6
    Rep Power
    0
    Rajest:
    also experiment with recording a macro.
    here is one that has captured entering a formula and filling it across:
    Code:
    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

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Okay, now I think I see what you want. If I am right, then you can do it using this single line of code...

    Code:
    Range(Range("D27"), Range("D27").End(xlToRight)).Formula = Range("D27").Formula
    More generally, where the cell reference could change, this macro...

    Code:
    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.
    Last edited by Rick Rothstein; 05-16-2012 at 07:10 PM. Reason: Corrected code to move toward the right to find the blank cell

  8. #8
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    14
    You can also try Autofill

    Code:
    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

  9. #9
    Member littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    14
    I think this will serve your purpose.

    Code:
    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
    --------------------------

Similar Threads

  1. Replies: 13
    Last Post: 06-10-2013, 09:05 AM
  2. Removing unused Cell styles - need an efficient code
    By siddharthsindhwani in forum Excel Help
    Replies: 8
    Last Post: 04-15-2013, 07:12 AM
  3. Autofill the data based on non blank cell in next row?
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 3
    Last Post: 11-29-2012, 04:16 PM
  4. Replies: 2
    Last Post: 09-24-2012, 11:19 PM
  5. Replies: 1
    Last Post: 04-24-2012, 09:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •