Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting the next blank cell.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting the next blank cell.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A basic Macro: going to a particular Worksheet and then selecting thefirst blank cell in column A | Excel Programming | |||
Start Cell B1 then find first blank cell, insert subtotal, next non blank, then next blank, sutotal cells in between......... | Excel Programming | |||
Selecting the first blank cell in a column | Excel Programming | |||
Selecting the first non blank cell in Range | Excel Worksheet Functions | |||
selecting the last non blank row. | Excel Programming |