Friday, September 23, 2016

How Can I Fix - copy range to row below based on cell value from formula?

copy range to row below based on cell value from formula

need VB to copy a range of cells in row (columns A, C, L ,to the next blank row (Columns A, C, E) on basis of a cell value from formula in source row (column L).
this need to happen automatically on cell value changing in (L)

Keys to the Problem copy range to row below based on cell value from formula

Download SmartPCFixer for Free Now

That gives a much better understanding of the process.
In this case, a manual entry in column E can be used as the trigger to evaluate the value in column L and move the information into a new row if necessary.
Note that the manual entry in column E is
the trigger, not the change in column L.

It would be better to write the formula in column L when the value in column E is entered.
For the purpose of this exercise, I will assume
8 hours in a workday and that the formula in column L is simply =MAX(E1-8,0).

Right-click the worksheet's name tab and when the VBE opens, paste the following into the pane titled something like
Book1 - Sheet1 (Code), 


Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Columns("E").SpecialCells(xlConstants, xlNumbers)) _
      Is Nothing Then
        Application.EnableEvents = False
        Dim e As Range, dWrkHrs As Double
        dWrkHrs = 8
        For Each e In Intersect(Target, Me.Columns("E"))
            e.Offset(0, 7).FormulaR1C1 = "=IFERROR(MAX(RC[-7]-" & dWrkHrs & ", 0),"""")"
            If Application.Sum(e.Offset(0, 7).Value) > 0 Then
                Me.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = e.Offset(0, -4).Value
                Me.Cells(Rows.Count, "A").End(xlUp).Offset(0, 2) = e.Offset(0, -2).Value
                Me.Cells(Rows.Count, "A").End(xlUp).Offset(0, 4) = e.Offset(0, 7).Value
                Me.Cells(Rows.Count, "A").End(xlUp).Offset(0, 11).FormulaR1C1 = _
                  "=IFERROR(MAX(RC[-7]-" & dWrkHrs & ", 0),"""")"
            End If
            Do While Me.Cells(Rows.Count, "L").End(xlUp).Value > 0
                Me.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = _
                  Me.Cells(Rows.Count, "A").End(xlUp).Value
                Me.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = _
                  Me.Cells(Rows.Count, "C").End(xlUp).Value
                Me.Cells(Rows.Count, "E").End(xlUp).Offset(1, 0) = _
                  Me.Cells(Rows.Count, "L").End(xlUp).Value
                Me.Cells(Rows.Count, "L").End(xlUp).Offset(1, 0).FormulaR1C1 = _
                  "=IFERROR(MAX(RC[-7]-" & dWrkHrs & ", 0),"""")"
            Loop
        Next e
    End If
FallThrough:
    Application.EnableEvents = True
End Sub


Note that the variable dWrkHrs is assigned a value of 8.
Tap
Alt+Q to return to your worksheet.
When you type a value into column E that is great than the value assigned to
dWrkHrs, a new row will be created and populated with the values from the entry row,

I'm not sure what you wanted to do with the original value from column.
If you want it reset to a maximum of dWrkHrs, then post back and some modifications may be made.

Make Sure that your Hardware Meets the System Requirements

Microsoft Windows Requirements :

  • 1 gigahertz (GHz) or faster 32-bit (x86) or 64-bit (x64) processor
  • 1 gigabyte (GB) RAM (32-bit) or 2 GB RAM (64-bit)
  • 16 GB available hard disk space (32-bit) or 20 GB (64-bit)

This error can be due to some configuration requirements for you system. If your computer hardware is not up to par, then make sure you update where necessary before continuing.

Another Safe way to Repair the Problem: copy range to row below based on cell value from formula:

How to Fix copy range to row below based on cell value from formula with SmartPCFixer?

1. Click the button to download Error Fixer . Install it on your computer.  Open it, and it will perform a scan for your computer. The errors will be shown in the scan result.

2. After the scan is finished, you can see the errors and problems need to be repaired. Click Fix All.

3. When the Fixing part is finished, your computer has been speeded up and the errors have been fixed


Related: How Can You Update & Download NVidia 6100, 6800 GS/XT Display Driver v.260.89 WHQL,Where to Download NVidia GeForce 8800 GT WHQL-certified driver v.196.21,How to Update & Download NVidia GeForce Go 7400 Display Driver v.260.19.12,[Solved] Download NVidia GeForce GTX 460M Driver v.331.82,Download NVidia Quadro Plex Model IV VGA Driver v.304.43 Certified,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.8.17.12.6686,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.11.6.0.1030,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.9.3.0.1019,Method to Update & Download ASUS K41VD Intel INF Update Driver v.9.1.1.1015,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver
Read More: Fast Solution to Error: Constantly losing Internet connection W/ 8.1,Troubleshooting:Creating a table style NOT based on Table Normal - Unable to change cell width or height when I try to create one based on Normal Error,CPU Intermittently Pegs at 100% for 30-60 minutes. Tech Support,Troubleshooting:corrupted outlook express,Troubleshooter of Error: Create formatted worksheets based on no. of rows in sheet 1,Closing Internet Explorer causes error,Compare 2 Tables and Calculate the PROFIT Faster,Computer goes to Windows Boot Manager after Start,Connect to one of my network computer remotely when don't allow exception box Marked,computer locking up randomly

No comments:

Post a Comment