MrExcel Publishing
Your One Stop for Excel Tips & Solutions

If loops including Cell numbers

Posted by Mike W on September 27, 2001 5:35 AM

I'm trying to set up a spreadsheet so that when you press a button on it, it will prune certain data and copy that data to another file.
This part I have managed ok thanks to other posts in this forum.

However, what I want to do is make it so that every time the button is pressed, it is ADDED to the data in the second file, but doesn't overwrite it.

To me it seems the simplest way of doing it is to check the target cell and see if it is blank. If it's blank it'll paste the data, if not, it'll check the next cell down.

That's where the problem begins. How do I integrate cell numbers into an IF loop in VB?

Essentially what I want is:

If Ax="(blank)" then paste
Else x=x+1
then repeat the loop.

The trouble I'm having is integrating the Cell numbers into this.

Any ideas?

Posted by Mark O'Brien on September 27, 2001 6:23 AM


You could use a Do Loop like this, here's an example. I've just opened a new book and have assumed "A1" is the first cell to check.

Public Sub FindTargetCell()
'Declare Variable
Dim Ax As Range

'Initialise Variable
Set Ax = Sheets("Sheet1").Range("A1")

If Ax.Value = "" Then
'++++Insert Your Code Here.++++
'I have put in a message box as an example
MsgBox "The first blank cell is: " & Ax.Address
Exit Do
Set Ax = Ax.Offset(1, 0)
End If

End Sub

Posted by Juan Pablo on September 27, 2001 6:28 AM

Try this... i see you're checking for column A, if not, change the Range.

Sheets("Sheet2").Paste Destination:=Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1)

That will paste whatever you have copied in the first available row of sheet2 (Again, checking Column A)

Juan Pablo