Pasting a blank cell & skip it the next run

huibster

New Member
Joined
Jul 22, 2011
Messages
4
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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:
Code:
'this code is inefficient
Selection.copy
Range("B1").Select
ActiveSheet.paste
could be rpelaced with:
Code:
'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:
Code:
'Most efficient
Range("B1").value = Activecell.Value

To check for blank cells add a if command
Code:
If Activecell <> vbNullstring then
....
end if
 
Upvote 0
Hey Sijpie,

Thanks a lot :)! In your most efficient solution:

Code:
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:

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

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

Thanks again.
Huib
 
Upvote 0
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?
 
Upvote 0
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 ;)
 
Upvote 0
Try something like this.
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top