Software

Ask something
Discussion Locked

Problem with Programming a Command button

0 helpful
3 replies

by stewart54

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

The code shown below is for a Button a user will click after a "data input" session in order to post the record.

The code works as written for testing purposes to a specific target but I need to have the copied range pasted to the next empty row of a record stack that was already manually-started at A501.

Here is the Code:

Private Sub CommandButton3_Click()
Worksheets("les").Activate
Range("Ac3:aC24").Select
Range("Ac3:aC24").Copy
Range("a5000").PasteSpecial xlPasteValuesAndNumberFormats, SkipBlanks:=False, Transpose:=True

Worksheets("les").Activate
Range("af1").Select
Range("ac4").Select
End Sub

The first group copies and pastes the record, and the second grouip returns the input screen for further input.

How can the code be written so that the next "empty" row is found before the range of copied cells is allowed to be pasted?

The idea for this is to use a conditional statement to test for cell content beginning at A501; if not empty, it advances to A502 then A503 and-so-on until the next empty cell is found. Once found, the copied range is pasted as the next completed record.

I am familiar in doing these manually, but am not in knowing how to write program instruction for automated functions like this one.

Thank you for your pending response

3 replies

by yogi_john

This will get you to the first blank cell.
Range("A500").Select
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Activate
Loop

Once in that cell, try
ActiveCell.PasteSpecial....

John

by stewart54

I just wanted to add;
In my submission entitled "Update on Progression: Making headway", it was attempted in using a Call funtion to use a Sub-routine to acomplish this.

In knowing that when the routine completed, the Call from would be resumed with the next step in its progression with the paste.

What you provided however, negates the necessity of a "Call" and was more logical to use your provided method.

Thank you again; it works well...

This discussion is closed to new replies.