PDA

View Full Version : [SOLVED] Putting data where you want it!!



BexleyManor
06-24-2005, 06:08 AM
Sub Enter_Agreement_details()
Dim Iput As String, Data As Variant, i As Integer
Sheet3.Select
Range("A1").End(xlDown).Offset(1, 0).Select
Data = Array("Agreement Number", "Parent Company", "Title", "First Name", "Surname", "Street", "Town", "City", "County", "Post Code", "Phone", "Fax", "E-Mail", "Application Received", "UNA To Site", "UNA Received From Site", "UNA To DEFRA", "DEFRA Approved")
For i = LBound(Data) To UBound(Data)
Iput = InputBox(".:: p l e a s e e n t e r f o l l o w i n g d a t a ::. " & Chr(10) & Chr(10) & Data(i), Data(i))
Next i



Ok, what I would like the above to do is take the data it receives from the inputbox and place it on the worksheet starting at the point selected by the code Range("A1").End(xlDown).Offset(1, 0).Select so for arguments sake say it selects A10 then the following data should then go in B10, C10, D10 etc until the array has completed asking for data.

Hope this Makes sense?

Cheers folks!!

mvidas
06-24-2005, 07:05 AM
Hi,
I changed your sub a little bit, should do what you want!


Sub Enter_Agreement_details()
Dim Iput As String, Data As Variant, i As Long, iUB As Long, Answers() As String
Data = Array("Agreement Number", "Parent Company", "Title", "First Name", _"Surname", "Street", "Town", "City", "County", "Post Code", "Phone", "Fax",
"E-Mail", "Application Received", "UNA To Site", "UNA Received From Site", _
"UNA To DEFRA", "DEFRA Approved")
iUB = UBound(Data)
ReDim Answers(iUB)
For i = 0 To iUB
Iput = InputBox(".:: p l e a s e e n t e r f o l l o w i n g d a t a ::. " _
& Chr(10) & Chr(10) & Data(i), Data(i))
Answers(i) = Iput
Next i
With Sheet3.Range("A1").End(xlDown)
Range(.Offset(1, 0), .Offset(1, iUB)) = Answers
End With
End Sub

Matt

Norie
06-24-2005, 07:08 AM
Does this work?


Option Base 1
Sub Enter_Agreement_details()
Dim Iput As String, arrData As Variant, i As Integer
Dim NextRow As Long

NextRow = Sheet3.Range("A65536").End(xlUp).Row + 1

arrData = Array("Agreement Number", "Parent Company", _
"Title", "First Name", "Surname", "Street", _
"Town", "City", "County", "Post Code", "Phone", _
"Fax", "E-Mail", "Application Received", "UNA To Site", _
"UNA Received From Site", "UNA To DEFRA", "DEFRA Approved")

For i = LBound(arrData) To UBound(arrData)

Iput = InputBox(".:: p l e a s e e n t e r f o l l o w i n g d a t a ::. " & Chr(10) & Chr(10) & arrData(i), arrData(i))
Sheet3.Cells(NextRow, i) = Iput
Next i

End Sub

BexleyManor
06-24-2005, 07:31 AM
Ok, both work splendidly, thank you kindly!!

Top work, saved me pulling anymore hair out!!