Paste range of values in the first 5 consecutive blank cells

[theonealex]

I am having trouble figuring out how to locate the first 5 consecutive blank cells in a specific row and then paste a range in those 5 blank cells.

Some details about the data I'm working with (Example data below):

• User selects a product name from a drop down list (Sheet1, cell B1)
• User enters 5 pieces of information about that product (Sheet1, cells D2:H2)
• User clicks a button to submit the 5 pieces of information

Ideally the button copies the Sheet1 D2:H2 range, uses the selected product name to look up the corresponding row in Sheet2 for that product, then locates the first 5 blank cells in Sheet2 after column F to paste the range in (so previous information submitted for a product does not get overwritten).

Private Sub SubmitComment_Click()

Dim myValue As Range 'value selected from a drop down list
Dim findRow As Range
Dim targetRow As Long
Dim myComment As Range

Set myComment = Sheets("Sheet1").Range("D2:H2")
Set myValue = Sheets("Sheet1").Range("B1") 'location of drop down list
Set findRow = Sheets("Sheet2").Range("A:A").Find(What:=myValue, LookIn:=xlValues) 'searches column B in Sheet2 for drop down list value
targetRow = findRow.Row 'integer value of row matching the selected product

Sheets("Sheet2").Select

'// code to locate first 5 consecutive blank cells after column F
'// code to select the first cell in this range of 5 consecutive blank cells
'// code to paste myComment

End Sub

Any insight and help would be greatly appreciated!


Example workbook:

Sheet 1

	A	B	C	D	E	F	G	H
1	Product:	(drop down list of products)		Info 1	Info 2	Info 3	Info 4	Info 5
2				[user entered data]<user entered="" data=""></user>	[user entered data]<user entered="" data=""></user>	[user entered data]<user entered="" data=""></user>	[user entered data]<user entered="" data=""></user>	[user entered data]<user entered="" data=""></user>

<tbody>
</tbody>

[[["Submit Comment" button]]]

Sheet 2

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

Comment 1

Comment 1

Comment 1

Comment 1

Comment 1

Comment 2

Comment 2

Comment 2

Comment 2

Comment 2

Comment 3

Comment 3

Comment 3

Comment 3

Comment 3

2

Product List

Data

Data

Data

Data

Data

Info 1

Info 2

Info 3

Info 4

Info 5

Info 1

Info 2

Info 3

Info 4

Info 5

Info 1

Info 2

Info 3

Info 4

Info 5

3

Product 1

aaa

aaa

aaa

aaa

aaa

4

Product 2

5

Product 3

aaa

aaa

aaa

6

Product 4

7

Product 5

aaa

aaa

aaa

aaa

aaa

aaa

aaa

aaa

8

Product 6

<tbody>
</tbody>

 

[tonyyy]

Re: How to paste range of values in the first 5 consecutive blank cells?

theonealex,

Welcome to the Board.

You might consider the following...

Private Sub SubmitComment_Click()
Dim myValue As Range 'value selected from a drop down list
Dim findRow As Range
Dim targetRow As Long
Dim myComment As Range
Dim LastColumn As Long
Dim ws As Worksheet

Set ws = Sheets("Sheet2")
Set myComment = Sheets("Sheet1").Range("D2:H2")
Set myValue = Sheets("Sheet1").Range("B1") 'location of drop down list
Set findRow = Sheets("Sheet2").Range("A:A").Find(What:=myValue, LookIn:=xlValues) 'searches column B in Sheet2 for drop down list value
targetRow = findRow.Row 'integer value of row matching the selected product
LastColumn = ws.Cells(targetRow, Columns.Count).End(xlToLeft).Column

If LastColumn > 6 Then
    ws.Range(ws.Cells(targetRow, LastColumn + 1), ws.Cells(targetRow, LastColumn + 6)).Value = myComment.Value
Else
    ws.Range(ws.Cells(targetRow, 7), ws.Cells(targetRow, 11)).Value = myComment.Value
End If
Sheets("Sheet2").Select
End Sub

Please note the code is untested, and assumes your original code ran error-free.

Cheers,

tonyyy