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
Mike
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")
Do
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
Else
Set Ax = Ax.Offset(1, 0)
End If
Loop
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
----------------------
