Hi guys,
I'm really new at VBA and using a loop script to copy income data (from four manually selected cells) into four cells (B2:E2) that are part of a calculation of which the final output is located in cell B6. This value is then pasted into the first blank cell at the bottom of a list with Range("B21"). After this the next row of income data is selected and the script loops.
This is the script:
Sub loop_all()
For x = 1 To 177 Step 1
Application.CutCopyMode = False
Selection.copy
Range("B1").Select
ActiveSheet.paste
Range("B6").Select
Selection.copy
ActiveSheet.Range("b21").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
ActiveCell.Offset(1, 5).Select
Range(ActiveCell, ActiveCell.Offset(0, 3)).Select
Next
End Sub
It works fine, except for one problem. As it runs through the list of income data it also runs it for rows with missing input, resulting in output #VALUE! in B6 wich is then pasted into the last cell of the list.
I would like it to 'paste a blank cell' or if not possible value 0. The thing is that the script must skip this cell in the next run (otherwise output belonging to another entity ends up in the cell that should have been skipped).
I hope this is at least somewhat clear. Any help would be great, thanks!
huibster
I'm really new at VBA and using a loop script to copy income data (from four manually selected cells) into four cells (B2:E2) that are part of a calculation of which the final output is located in cell B6. This value is then pasted into the first blank cell at the bottom of a list with Range("B21"). After this the next row of income data is selected and the script loops.
This is the script:
Sub loop_all()
For x = 1 To 177 Step 1
Application.CutCopyMode = False
Selection.copy
Range("B1").Select
ActiveSheet.paste
Range("B6").Select
Selection.copy
ActiveSheet.Range("b21").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
ActiveCell.Offset(1, 5).Select
Range(ActiveCell, ActiveCell.Offset(0, 3)).Select
Next
End Sub
It works fine, except for one problem. As it runs through the list of income data it also runs it for rows with missing input, resulting in output #VALUE! in B6 wich is then pasted into the last cell of the list.
I would like it to 'paste a blank cell' or if not possible value 0. The thing is that the script must skip this cell in the next run (otherwise output belonging to another entity ends up in the cell that should have been skipped).
I hope this is at least somewhat clear. Any help would be great, thanks!
huibster