Pasting a blank cell & skip it the next run

[huibster]

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

 

[sijpie]

All this selecting in your sub is not very efficient, it also makes it very difficult to follow the flow of the subroutine.


For instance your:

'this code is inefficient
Selection.copy
Range("B1").Select
ActiveSheet.paste

could be rpelaced with:

'this is more efficient
Selection.Copy Destination:=Range("B1")

which doesn't change the active cell.
But you can also bypass te clipboard completely and say:

'Most efficient
Range("B1").value = Activecell.Value

To check for blank cells add a if command

If Activecell <> vbNullstring then
....
end if

 

[huibster]

Hey Sijpie,

Thanks a lot ! In your most efficient solution:

Range("B1").value = Activecell.Value

the active cell should actually be 4 adjacent cells (B2:E2). It doesn't seem to take that into account as it only returns #VALUE!. I tried:

Range("B1").value = Range(ActiveCell, ActiveCell.Offset(0, 3))

But the result is the same. What am I doing wrong?

Thanks again.
Huib

 

[sijpie]

You are tying to assign a range to the value in B1.

it is like saying (with a formula in B1) =C2:C5
Excel will then also throw up an error.

What do you want B1 to be
- the sum of the values in the range?
- or something else?

 

[huibster]

The input data in this worksheet are four columns wide by 177 rows long. Before running the script the top row has to be selected (G26:J26). These top four cells should then be copy pasted into B1:B4.

B1:B4 is lets say 'the input range' for a certain calculation that needs to be done 177 times. So every time the script loops a new line of four cells needs to get pasted into B1:B4.

I'm sorry if it's not clear, really appreciate the help

 

[Norie]

Try something like this.

Dim rngSrc As Range
Dim rngDst As Range
 
          Set rngDst = Range("B1")
 
          Set rngSrc = Range("G26")
 
          While rngSrc.Value <>""
 
              rngSrc.Resize(,4).Copy rngDst
 
              Set rngSrc = rngSrc.Offset(1)
          Wend

This should loop starting in row 26 and copy columns G:J of that row to B1:B4.

The loop will stop once there are no more values in column G in the current row in the loop.

You might need to stick a Calculate somewhere in the code if the purpose of it is to, well, calculate.