VBA find formula & copy output to adjacent cell

[jerrykel]

Hello,
I am in need of a bit of VBA code that will copy formula output to adjacent cell. I think this is a two part problem. First, find the cells with a formula in them and then copy output to the cell on the right.

Criteria:
1. Set number of rows and columns. normally G23:BZ26.
2. Only data in range is this formula that adds other cells and gives a sum.
3. Most cells are blank and the formula cells always have a blank cell to the right. Formula cells comprise all four rows of a column.
4. I need to copy the output not the formula to the adjacent cell to the right.
5. The columns with formulas get deleted prior to publication of the spreadsheet. Put I need to output to remain
6. The columns with formulas are not at a set interval.

Thanks for any help you can give.
Jerry

 

[VoG]

Give this a try

Sub test()
With ActiveSheet.Range("G23:BZ26").SpecialCells(xlCellTypeFormulas)
    .Offset(, 1).Value = .Value
    .Value = .Value
End With
End Sub

 

[jerrykel]

Thanks for the reply VoG.
The script finds the output to the first formula cell then copies it to every cell that had a formula and the adjacent cell. This is a good start because I can now find a formula and copy to the next cell. This script also replaces the formula with the output.

Removing the final .value = .value statement means that all the formula cells stay as they are supposed to. However, only the first formula cell is copied to all the adjacent cells of formulas in the spreadsheet.

So still looking for a solution.

 

[tusharm]

Very lightly tested:

Option Explicit

Sub test()
    Dim aCell As Range
    For Each aCell In ActiveSheet.Range("a1:e10").SpecialCells(xlCellTypeFormulas)
        aCell.Offset(, 1).Value = aCell.Value
        Next aCell
    End Sub

Thanks for the reply VoG.
The script finds the output to the first formula cell then copies it to every cell that had a formula and the adjacent cell. This is a good start because I can now find a formula and copy to the next cell. This script also replaces the formula with the output.

Removing the final .value = .value statement means that all the formula cells stay as they are supposed to. However, only the first formula cell is copied to all the adjacent cells of formulas in the spreadsheet.

So still looking for a solution.

 

[jerrykel]

Thanks Tushar.
This worked. My only concern is that it locks up to spreadsheet for a couple of minutes while the code runs.

 

[tusharm]

You could try, though I don't know how much it will help to adjust screenupdating and calculation as in the untested:

Option Explicit

Sub test()
    With Application
    Dim OldCalc As Long: OldCalc = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    Dim aCell As Range
    For Each aCell In ActiveSheet.Range("a1:e10").SpecialCells(xlCellTypeFormulas)
        aCell.Offset(, 1).Value = aCell.Value
        Next aCell
    .ScreenUpdating = True
    .Calculation = OldCalc
        End With
    End Sub

Thanks Tushar.
This worked. My only concern is that it locks up to spreadsheet for a couple of minutes while the code runs.

 

[jerrykel]

Fixed the delay in the code. Turns out I had another script set to private sub worksheet_change running in the background on this worksheet. I changed it to a manual script and now both scripts run as they should.

Thanks for the help.

Jerry