adapt VBA code to stop and alert if any cells are blank

[yachrishere]

Hi all, thanks for taking a look.
I have a VBA code that when activated it copies 6 cells from sheet1 to the next available row in sheet2 - A2:F:2.

I need help with alerting and stopping the routine if any one of the cells in sheet1 are empty so the user has to input into all cells before the routine will execute.

does it need a check cells for content
if error highlight empty cell.

cheers all.

[JOHN H. DAVIS]

Can you show us the code you have?

[techflutter]

Use a do until is empty loop.

Put an if statement inside to check if cell is empty.

The use a msgbox and require input from user.

Write that value to the cell.

Check our site for code on counters and total counters as you will need them
James

[yachrishere]

Hi John.

here is the code and when I get out of the file I'll add the file so you can comment on the format if you wish.

Private Sub CommandButton1_Click(#
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, r2 As Range
Dim v1 As Variant, v2 As Variant

Set sh1 = Worksheets#"Sheet1"#
Set sh2 = Worksheets#"Sheet2"#

' specify the cells that will contain information in sheet1
v1 = Array#"F5", "F8", "F12", "C19", "E19", "G19", "I19", "C21"#
' in the same order specify what columns that info would be
' placed in in sheet2
v2 = Array#"A", "B", "C", "D", "E", "F", "G", "Q"#
' find the next open row in sheet2 using column A
rw = sh2#Cells#Rows#Count, "A"##End#xlUp##Offset#1, 0##Row
' Now copy and clear the data
For i = LBound#v1# To UBound#v1#
Set r1 = sh1#Range#v1#i##
Set r2 = sh2.Cells#rw, v2#i##
r1.Copy r2
' clears the value after it is transferred
r1.ClearContents
Next i
End Sub

[yachrishere]

Another question is - as you may see I have used drop down boxes to populate the fields, F5, F8, F12, and also the start and finish times.

is this an effective way to do this?

[JOHN H. DAVIS]

Please comply with Forum Rule No. 3.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

[yachrishere]

Please Login or Register  to view this content.

[yachrishere]

Thanks John for keeping me informed.