Blank Cell
 

Please help

I have data in a worksheet called "dm1" in cell "a72", cell "aa73"
Now when i click a button i want contents of "a72" to go to a worksheet
named "Input Screen" and find a blank cell between "d7:d36" and place the
data here.
Now "aa73" goes to "Input Screen" and find a blank cell between "e7:e36" and
place the data here.

Thanks in advance

Blank Cell
 

Hi, you could try something like (eg for first Write):
'-----------------------------------------------------
Sub test()
Dim rg As Range, Cell As Range

Set Cell = ThisWorkbook.Worksheets("dm1").Range("a72")
Set rg = ThisWorkbook.Worksheets("Input Screen").Range("d7:d36")

Set rg = rg.Find(Chr(0), , xlValues, xlWhole) 'search blank cell
If rg Is Nothing Then 'If no blank cell found
MsgBox "No blank cell found"
Else 'else write the cell
rg.Cells(1).Value = Cell.Value
End If

End Sub
'------------------------------------------------------------
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"cityfc" wrote:

Please help

I have data in a worksheet called "dm1" in cell "a72", cell "aa73"
Now when i click a button i want contents of "a72" to go to a worksheet
named "Input Screen" and find a blank cell between "d7:d36" and place the
data here.
Now "aa73" goes to "Input Screen" and find a blank cell between "e7:e36" and
place the data here.

Thanks in advance

Blank Cell
 

What a star
thank you so much it works a treat

cityfc



"sebastienm" wrote:

Hi, you could try something like (eg for first Write):
'-----------------------------------------------------
Sub test()
Dim rg As Range, Cell As Range

Set Cell = ThisWorkbook.Worksheets("dm1").Range("a72")
Set rg = ThisWorkbook.Worksheets("Input Screen").Range("d7:d36")

Set rg = rg.Find(Chr(0), , xlValues, xlWhole) 'search blank cell
If rg Is Nothing Then 'If no blank cell found
MsgBox "No blank cell found"
Else 'else write the cell
rg.Cells(1).Value = Cell.Value
End If

End Sub
'------------------------------------------------------------
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"cityfc" wrote:

Please help

I have data in a worksheet called "dm1" in cell "a72", cell "aa73"
Now when i click a button i want contents of "a72" to go to a worksheet
named "Input Screen" and find a blank cell between "d7:d36" and place the
data here.
Now "aa73" goes to "Input Screen" and find a blank cell between "e7:e36" and
place the data here.

Thanks in advance