VBA To copy formula in active column & to apply to selected column

[excelsishya]

Hi all,

Can any anybody provide VBA code which copies formula in active column applies to right column till data is there.

eg in column b say row 8 ,10 are formula , when i select column b it should copy formula and apply to col c ,d,e as next 3 column(col no right to it may vary) right to it as data.

Sheet1

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Calibri,Arial; font-size: 11pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td style="text-align: right;">4</td> <td style="text-align: right;">4</td> <td style="text-align: right;">55</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td style="text-align: right;">2</td> <td style="text-align: right;">4</td> <td style="text-align: right;">55</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="text-align: right;">3</td> <td style="text-align: right;">4</td> <td style="text-align: right;">55</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="text-align: right;">4</td> <td style="text-align: right;">4</td> <td style="text-align: right;">55</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="text-align: right;">3</td> <td style="text-align: right;">4</td> <td style="text-align: right;">55</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="text-align: right;">5</td> <td style="text-align: right;">4</td> <td style="text-align: right;">55</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="text-align: right;">3</td> <td style="text-align: right;">4</td> <td style="text-align: right;">55</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td style="text-align: right; font-weight: bold;">24</td> <td style="text-align: right; font-weight: bold;">28</td> <td style="text-align: right; font-weight: bold;">385</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td style="text-align: right;">4</td> <td style="text-align: right;">4</td> <td style="text-align: right;">55</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td style="text-align: right; font-weight: bold;">0.166667</td> <td style="text-align: right; font-weight: bold;">0.142857</td> <td style="text-align: right; font-weight: bold;">0.142857</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B8</td> <td>=SUM(B1:B7)</td></tr> <tr> <td>C8</td> <td>=SUM(C1:C7)</td></tr> <tr> <td>D8</td> <td>=SUM(D1:D7)</td></tr> <tr> <td>B10</td> <td>=B9/B8</td></tr> <tr> <td>C10</td> <td>=C9/C8</td></tr> <tr> <td>D10</td> <td>=D9/D8</td></tr></tbody></table></td></tr></tbody></table> after running code c8,10 d8,10 having constants value should have formula.

i am using excel 2007

i have formula method for it (go to+filter ) but looking for VBA code.

Thanks in advance.

 

[AlphaFrog]

Sub Copy_Formulas_Right()
    
    Dim Lostcol As Long
    Lastcol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
    Selection.Copy
    Selection.Offset(, 1).Resize(, Lastcol - Selection.Column).PasteSpecial _
        Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=True, Transpose:=False
    Application.CutCopyMode = True
    
End Sub

 

[AlphaFrog]

Corrected code. Disregard the previous.

Sub Copy_Formulas_Right2()
    
    Dim Lostcol As Long, cell As Range
    Lastcol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
    For Each cell In Selection.SpecialCells(xlCellTypeFormulas)
        cell.Resize(, Lastcol - Selection.Column + 1).Formula = cell.Formula
    Next cell
    
End Sub

 

[excelsishya]

Alphafrog thanks for code .it works perfectly.

 

[pong]

Hi i am also new to VBA too
I just have a few questions about it
the first one is
what does the "*" represent here ?
cux i have tried searching string with the find method
but i dont understand the "*" here
and

what is the difference between xlnext and xlprevious
why isnt xlnext in the case ?

 

[AlphaFrog]

The goal with this line of code is to find the last used column on the sheet.

Lastcol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column

The asterisk "*" is a wildcard. It will match anything; text or numbers. So any cell that is not blank will be a match.

The xlPrevious argument directs a search backwards. It would find the first nonblank cell previous to cell A1, or the last used column in this case. Previous to cell A1 wraps around to the last used cell.

Other methods; Finding the Last Cell in a Range