I have a user form where I want to put the data into a spreadsheet and keep
adding to it. Basicly, to quantiitize a report for sales people. I need to
add the new data to the next blank row.
I have it now adding to a single row, but I need it to find the next blank
row and write the new data there. Either that, or there is a much easier way
that I'm not doing it! Thanks,

Private Sub OK_Click()

Dim MyRange As String

WE1 = "A5"
MyRange = WE1.Value
Range(MyRange).Value = WeekEnding.Text

OC1 = "B5"
MyRange = OC1.Value
Range(MyRange).Value = OutgoingCalls.Text

IS1 = "C5"
MyRange = IS1.Value
Range(MyRange).Value = IncomingCalls.Text

OE1 = "D5"
MyRange = OE1.Value
Range(MyRange).Value = OutgoingEmail.Text

IE1 = "E5"
MyRange = IE1.Value
Range(MyRange).Value = IncomingEmail.Text

Con1 = "G5"
MyRange = Con1.Value
Range(MyRange).Value = Conversation.Text

Mes1 = "H5"
MyRange = Mes1.Value
Range(MyRange).Value = Message.Text

Ded1 = "I5"
MyRange = Ded1.Value
Range(MyRange).Value = DeadCall.Text

PC1 = "K5"
MyRange = PC1.Value
Range(MyRange).Value = ProductionCompany.Text

CAT1 = "L5"
MyRange = CAT1.Value
Range(MyRange).Value = Catering.Text

VEN1 = "M5"
MyRange = VEN1.Value
Range(MyRange).Value = Venue.Text

WP1 = "N5"
MyRange = WP1.Value
Range(MyRange).Value = WeddingPlanner.Text

ENT1 = "O5"
MyRange = ENT1.Value
Range(MyRange).Value = Entertainment.Text

CORP1 = "P5"
MyRange = CORP1.Value
Range(MyRange).Value = Corporations.Text

ORG1 = "Q5"
MyRange = ORG1.Value
Range(MyRange).Value = Organizations.Text

RFP1 = "s5"
MyRange = RFP1.Value
Range(MyRange).Value = RFP.Text

EB1 = "T5"
MyRange = EB1.Value
Range(MyRange).Value = EventsBooked.Text

Unload Me
End Sub

Try somthing like this...

Dim MyRange As range

set myrange = cells(rows.count, "A").end(xlup)

with myrange
.value = WeekEnding.Text
.offset(0,1).Value = OutgoingCalls.Text
.offset(0,2).Value = IncomingCalls.Text
.offset(0,3).Value = OutgoingEmail.Text
.offset(0,4).Value = IncomingEmail.Text
.offset(0,6).Value = Conversation.Text
.offset(0,7).Value = Message.Text
.offset(0,8).Value = DeadCall.Text
.offset(0,10).Value = ProductionCompany.Text
'fill in the rest
end with

--
HTH...

Jim Thomlinson


"Uru Nime" wrote:

I have a user form where I want to put the data into a spreadsheet and keep
adding to it. Basicly, to quantiitize a report for sales people. I need to
add the new data to the next blank row.
I have it now adding to a single row, but I need it to find the next blank
row and write the new data there. Either that, or there is a much easier way
that I'm not doing it! Thanks,

Private Sub OK_Click()

Dim MyRange As String

WE1 = "A5"
MyRange = WE1.Value
Range(MyRange).Value = WeekEnding.Text

OC1 = "B5"
MyRange = OC1.Value
Range(MyRange).Value = OutgoingCalls.Text

IS1 = "C5"
MyRange = IS1.Value
Range(MyRange).Value = IncomingCalls.Text

OE1 = "D5"
MyRange = OE1.Value
Range(MyRange).Value = OutgoingEmail.Text

IE1 = "E5"
MyRange = IE1.Value
Range(MyRange).Value = IncomingEmail.Text

Con1 = "G5"
MyRange = Con1.Value
Range(MyRange).Value = Conversation.Text

Mes1 = "H5"
MyRange = Mes1.Value
Range(MyRange).Value = Message.Text

Ded1 = "I5"
MyRange = Ded1.Value
Range(MyRange).Value = DeadCall.Text

PC1 = "K5"
MyRange = PC1.Value
Range(MyRange).Value = ProductionCompany.Text

CAT1 = "L5"
MyRange = CAT1.Value
Range(MyRange).Value = Catering.Text

VEN1 = "M5"
MyRange = VEN1.Value
Range(MyRange).Value = Venue.Text

WP1 = "N5"
MyRange = WP1.Value
Range(MyRange).Value = WeddingPlanner.Text

ENT1 = "O5"
MyRange = ENT1.Value
Range(MyRange).Value = Entertainment.Text

CORP1 = "P5"
MyRange = CORP1.Value
Range(MyRange).Value = Corporations.Text

ORG1 = "Q5"
MyRange = ORG1.Value
Range(MyRange).Value = Organizations.Text

RFP1 = "s5"
MyRange = RFP1.Value
Range(MyRange).Value = RFP.Text

EB1 = "T5"
MyRange = EB1.Value
Range(MyRange).Value = EventsBooked.Text

Unload Me
End Sub