Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Macro in Excel
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 6
Default Macro in Excel
I have a macro in Microsoft Excel listed below. I need for it to repeat
until xxx is entered. Any help will be appreciated.



Sub OptionExplicit()
'
' OptionExplicit Macro
' Macro recorded 3/10/2008 by Student Financial Aid
'
Dim vName As String
Dim vAddress As String
Dim vCityStateZip As String
Dim vPhoneNumber As String
Dim vEmailAddress As String
Dim vRealEstateAgentAndCompany As String
Dim vxxx As String

vName = InputBox("Please type in your name?")
If vName = "xxx" Then
Exit Sub
Else
vAddress = InputBox("Please enter your address?")
vCityStateZip = InputBox("Please enter City, State and Zip?")
vPhoneNumber = InputBox("Please enter your Phone Number?")
vEmailAddress = InputBox("Please enter your Email Address?")
vRealEstateAgentAndCompany = InputBox("Please enter your Real Estate Agent
and Company?")
Worksheets("Sheet1").Range("B1") = vName
Worksheets("Sheet1").Range("B2") = vAddress
Worksheets("Sheet1").Range("B3") = vCityStateZip
Worksheets("Sheet1").Range("B4") = vPhoneNumber
Worksheets("Sheet1").Range("B5") = vEmailAddress
Worksheets("Sheet1").Range("B6") = vRealEstateAgentAndCompany

'
Range("B1").Select
Range("B2").Select
Range("B3").Select
Range("B4").Select
Range("B5").Select
Range("B6").Select


vName = InputBox("Please type in your name?")
vAddress = InputBox("Please enter your address?")
vCityStateZip = InputBox("Please enter City, State and Zip?")
vPhoneNumber = InputBox("Please enter your Phone Number?")
vEmailAddress = InputBox("Please enter your Email Address?")
vRealEstateAgentAndCompany = InputBox("Please enter your Real Estate Agent
and Company?")
Worksheets("Sheet1").Range("B11") = vName
Worksheets("Sheet1").Range("B12") = vAddress
Worksheets("Sheet1").Range("B13") = vCityStateZip
Worksheets("Sheet1").Range("B14") = vPhoneNumber
Worksheets("Sheet1").Range("B15") = vEmailAddress
Worksheets("Sheet1").Range("B16") = vRealEstateAgentAndCompany

'
Range("B11").Select
Range("B12").Select
Range("B13").Select
Range("B14").Select
Range("B15").Select
Range("B16").Select



End If
Application.Goto Reference:="OptionExplicit"
End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,124
Default Macro in Excel
Try this instead. I don't think I would use the name for the sub that you
used. Dim as desired.

Sub loopnames()
'dims here
i = 1
Do 'Until vName = "xxx"
vName = InputBox("Please type in your name?")
If vName = "xxx" Then Exit Sub
'MsgBox vName
vAddress = InputBox("Please enter your address?")
vCityStateZip = InputBox("Please enter City, State and Zip?")
vPhoneNumber = InputBox("Please enter your Phone Number?")
vEmailAddress = InputBox("Please enter your Email Address?")
vRealEstateAgentAndCompany = InputBox("Please enter your Real Estate Agent")


With Worksheets("Sheet1")
.Range("B" & i) = vName
.Range("B" & i + 1) = vAddress
.Range("B" & i + 2) = vCityStateZip
.Range("B" & i + 3) = vPhoneNumber
.Range("B" & i + 4) = vEmailAddress
.Range("B" & i + 5) = vRealEstateAgentAndCompany
End With

i = 1 + 10
Loop
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Cedric" wrote in message
...
I have a macro in Microsoft Excel listed below. I need for it to repeat
until xxx is entered. Any help will be appreciated.



Sub OptionExplicit()
'
' OptionExplicit Macro
' Macro recorded 3/10/2008 by Student Financial Aid
'
Dim vName As String
Dim vAddress As String
Dim vCityStateZip As String
Dim vPhoneNumber As String
Dim vEmailAddress As String
Dim vRealEstateAgentAndCompany As String
Dim vxxx As String

vName = InputBox("Please type in your name?")
If vName = "xxx" Then
Exit Sub
Else
vAddress = InputBox("Please enter your address?")
vCityStateZip = InputBox("Please enter City, State and Zip?")
vPhoneNumber = InputBox("Please enter your Phone Number?")
vEmailAddress = InputBox("Please enter your Email Address?")
vRealEstateAgentAndCompany = InputBox("Please enter your Real Estate
Agent
and Company?")
Worksheets("Sheet1").Range("B1") = vName
Worksheets("Sheet1").Range("B2") = vAddress
Worksheets("Sheet1").Range("B3") = vCityStateZip
Worksheets("Sheet1").Range("B4") = vPhoneNumber
Worksheets("Sheet1").Range("B5") = vEmailAddress
Worksheets("Sheet1").Range("B6") = vRealEstateAgentAndCompany

'
Range("B1").Select
Range("B2").Select
Range("B3").Select
Range("B4").Select
Range("B5").Select
Range("B6").Select


vName = InputBox("Please type in your name?")
vAddress = InputBox("Please enter your address?")
vCityStateZip = InputBox("Please enter City, State and Zip?")
vPhoneNumber = InputBox("Please enter your Phone Number?")
vEmailAddress = InputBox("Please enter your Email Address?")
vRealEstateAgentAndCompany = InputBox("Please enter your Real Estate
Agent
and Company?")
Worksheets("Sheet1").Range("B11") = vName
Worksheets("Sheet1").Range("B12") = vAddress
Worksheets("Sheet1").Range("B13") = vCityStateZip
Worksheets("Sheet1").Range("B14") = vPhoneNumber
Worksheets("Sheet1").Range("B15") = vEmailAddress
Worksheets("Sheet1").Range("B16") = vRealEstateAgentAndCompany

'
Range("B11").Select
Range("B12").Select
Range("B13").Select
Range("B14").Select
Range("B15").Select
Range("B16").Select



End If
Application.Goto Reference:="OptionExplicit"
End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 3,942
Default Macro in Excel
hi
to continue our conversations on 3/13...
if you put this code in a loop it wil be overwriting the same locations over
and over.
in your responce of 3/14, you stated that you would change the the locations.
you wont be able to do that while the code is running since the cell address
are hard coded into the code. so i took so liberties as coded it so that they
stack in column B.
Sub enterstuff()
Dim vName As String
Dim vAddress As String
Dim vCityStateZip As String
Dim vPhoneNumber As String
Dim vEmailAddress As String
Dim vRealEstateAgentAndCompany As String
Dim vxxx As String

Do While vName < "xxx"
vName = InputBox("Please type in your name?")
If vName = "xxx" Then
Exit Sub
End If
vAddress = InputBox("Please enter your address?")
vCityStateZip = InputBox("Please enter City, State and Zip?")
vPhoneNumber = InputBox("Please enter your Phone Number?")
vEmailAddress = InputBox("Please enter your Email Address?")
vRealEstateAgentAndCompany = InputBox("Please enter your Real Estate Agent
and Company?")
Worksheets("Sheet1").Range("B1").Range("B6500") _
.End(xlUp).Offset(1, 0) = vName
Worksheets("Sheet1").Range("B2").Range("B6500") _
.End(xlUp).Offset(1, 0) = vAddress
Worksheets("Sheet1").Range("B3").Range("B6500") _
.End(xlUp).Offset(1, 0) = vCityStateZip
Worksheets("Sheet1").Range("B4").Range("B6500") _
.End(xlUp).Offset(1, 0) = vPhoneNumber
Worksheets("Sheet1").Range("B5").Range("B6500") _
.End(xlUp).Offset(1, 0) = vEmailAddress
Worksheets("Sheet1").Range("B6").Range("B6500") _
.End(xlUp).Offset(1, 0) = vRealEstateAgentAndCompany
Loop
End Sub

regards
FSt1

"Cedric" wrote:

I have a macro in Microsoft Excel listed below. I need for it to repeat
until xxx is entered. Any help will be appreciated.



Sub OptionExplicit()
'
' OptionExplicit Macro
' Macro recorded 3/10/2008 by Student Financial Aid
'
Dim vName As String
Dim vAddress As String
Dim vCityStateZip As String
Dim vPhoneNumber As String
Dim vEmailAddress As String
Dim vRealEstateAgentAndCompany As String
Dim vxxx As String

vName = InputBox("Please type in your name?")
If vName = "xxx" Then
Exit Sub
Else
vAddress = InputBox("Please enter your address?")
vCityStateZip = InputBox("Please enter City, State and Zip?")
vPhoneNumber = InputBox("Please enter your Phone Number?")
vEmailAddress = InputBox("Please enter your Email Address?")
vRealEstateAgentAndCompany = InputBox("Please enter your Real Estate Agent
and Company?")
Worksheets("Sheet1").Range("B1") = vName
Worksheets("Sheet1").Range("B2") = vAddress
Worksheets("Sheet1").Range("B3") = vCityStateZip
Worksheets("Sheet1").Range("B4") = vPhoneNumber
Worksheets("Sheet1").Range("B5") = vEmailAddress
Worksheets("Sheet1").Range("B6") = vRealEstateAgentAndCompany

'
Range("B1").Select
Range("B2").Select
Range("B3").Select
Range("B4").Select
Range("B5").Select
Range("B6").Select


vName = InputBox("Please type in your name?")
vAddress = InputBox("Please enter your address?")
vCityStateZip = InputBox("Please enter City, State and Zip?")
vPhoneNumber = InputBox("Please enter your Phone Number?")
vEmailAddress = InputBox("Please enter your Email Address?")
vRealEstateAgentAndCompany = InputBox("Please enter your Real Estate Agent
and Company?")
Worksheets("Sheet1").Range("B11") = vName
Worksheets("Sheet1").Range("B12") = vAddress
Worksheets("Sheet1").Range("B13") = vCityStateZip
Worksheets("Sheet1").Range("B14") = vPhoneNumber
Worksheets("Sheet1").Range("B15") = vEmailAddress
Worksheets("Sheet1").Range("B16") = vRealEstateAgentAndCompany

'
Range("B11").Select
Range("B12").Select
Range("B13").Select
Range("B14").Select
Range("B15").Select
Range("B16").Select



End If
Application.Goto Reference:="OptionExplicit"
End Sub
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 6
Default Macro in Excel
FST1, thanks for the help. It did work. One think, How can I add a line
after I enter the vRealEstateAgentAndCompany before the next enter name.



"FSt1" wrote:

hi
to continue our conversations on 3/13...
if you put this code in a loop it wil be overwriting the same locations over
and over.
in your responce of 3/14, you stated that you would change the the locations.
you wont be able to do that while the code is running since the cell address
are hard coded into the code. so i took so liberties as coded it so that they
stack in column B.
Sub enterstuff()
Dim vName As String
Dim vAddress As String
Dim vCityStateZip As String
Dim vPhoneNumber As String
Dim vEmailAddress As String
Dim vRealEstateAgentAndCompany As String
Dim vxxx As String

Do While vName < "xxx"
vName = InputBox("Please type in your name?")
If vName = "xxx" Then
Exit Sub
End If
vAddress = InputBox("Please enter your address?")
vCityStateZip = InputBox("Please enter City, State and Zip?")
vPhoneNumber = InputBox("Please enter your Phone Number?")
vEmailAddress = InputBox("Please enter your Email Address?")
vRealEstateAgentAndCompany = InputBox("Please enter your Real Estate Agent
and Company?")
Worksheets("Sheet1").Range("B1").Range("B6500") _
.End(xlUp).Offset(1, 0) = vName
Worksheets("Sheet1").Range("B2").Range("B6500") _
.End(xlUp).Offset(1, 0) = vAddress
Worksheets("Sheet1").Range("B3").Range("B6500") _
.End(xlUp).Offset(1, 0) = vCityStateZip
Worksheets("Sheet1").Range("B4").Range("B6500") _
.End(xlUp).Offset(1, 0) = vPhoneNumber
Worksheets("Sheet1").Range("B5").Range("B6500") _
.End(xlUp).Offset(1, 0) = vEmailAddress
Worksheets("Sheet1").Range("B6").Range("B6500") _
.End(xlUp).Offset(1, 0) = vRealEstateAgentAndCompany
Loop
End Sub

regards
FSt1

"Cedric" wrote:

I have a macro in Microsoft Excel listed below. I need for it to repeat
until xxx is entered. Any help will be appreciated.



Sub OptionExplicit()
'
' OptionExplicit Macro
' Macro recorded 3/10/2008 by Student Financial Aid
'
Dim vName As String
Dim vAddress As String
Dim vCityStateZip As String
Dim vPhoneNumber As String
Dim vEmailAddress As String
Dim vRealEstateAgentAndCompany As String
Dim vxxx As String

vName = InputBox("Please type in your name?")
If vName = "xxx" Then
Exit Sub
Else
vAddress = InputBox("Please enter your address?")
vCityStateZip = InputBox("Please enter City, State and Zip?")
vPhoneNumber = InputBox("Please enter your Phone Number?")
vEmailAddress = InputBox("Please enter your Email Address?")
vRealEstateAgentAndCompany = InputBox("Please enter your Real Estate Agent
and Company?")
Worksheets("Sheet1").Range("B1") = vName
Worksheets("Sheet1").Range("B2") = vAddress
Worksheets("Sheet1").Range("B3") = vCityStateZip
Worksheets("Sheet1").Range("B4") = vPhoneNumber
Worksheets("Sheet1").Range("B5") = vEmailAddress
Worksheets("Sheet1").Range("B6") = vRealEstateAgentAndCompany

'
Range("B1").Select
Range("B2").Select
Range("B3").Select
Range("B4").Select
Range("B5").Select
Range("B6").Select


vName = InputBox("Please type in your name?")
vAddress = InputBox("Please enter your address?")
vCityStateZip = InputBox("Please enter City, State and Zip?")
vPhoneNumber = InputBox("Please enter your Phone Number?")
vEmailAddress = InputBox("Please enter your Email Address?")
vRealEstateAgentAndCompany = InputBox("Please enter your Real Estate Agent
and Company?")
Worksheets("Sheet1").Range("B11") = vName
Worksheets("Sheet1").Range("B12") = vAddress
Worksheets("Sheet1").Range("B13") = vCityStateZip
Worksheets("Sheet1").Range("B14") = vPhoneNumber
Worksheets("Sheet1").Range("B15") = vEmailAddress
Worksheets("Sheet1").Range("B16") = vRealEstateAgentAndCompany

'
Range("B11").Select
Range("B12").Select
Range("B13").Select
Range("B14").Select
Range("B15").Select
Range("B16").Select



End If
Application.Goto Reference:="OptionExplicit"
End Sub
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 7
Default Macro in Excel
Worksheets("Sheet1").Range("B1") = vName
Worksheets("Sheet1").Range("B2") = vAddress
Worksheets("Sheet1").Range("B3") = vCityStateZip
'etc...
Worksheets("Sheet1").Range("B14") = vPhoneNumber
Worksheets("Sheet1").Range("B15") = vEmailAddress
Worksheets("Sheet1").Range("B16") = vRealEstateAgentAndCompany

I see you have an answer.
Don't know if this short & general idea would be of interest...
You could probably do away with the variables names,
and just use M(1) = InputBox("??")...etc

Sub Demo()
Dim M(1 To 3)
Const vName As Long = 1
Const vAddress As Long = 2
Const vCityStateZip As Long = 3

M(vName) = InputBox("Please type in your name?")
M(vAddress) = InputBox("Please enter your address?")
M(vCityStateZip) = InputBox("Please enter City, State and Zip?")

With WorksheetFunction
Range("B1").Resize(3) = .Transpose(M)
End With
End Sub

__
HTH
Dana DeLouis


Cedric wrote:
I have a macro in Microsoft Excel listed below. I need for it to repeat
until xxx is entered. Any help will be appreciated.



Sub OptionExplicit()
'
' OptionExplicit Macro
' Macro recorded 3/10/2008 by Student Financial Aid
'
Dim vName As String
Dim vAddress As String
Dim vCityStateZip As String
Dim vPhoneNumber As String
Dim vEmailAddress As String
Dim vRealEstateAgentAndCompany As String
Dim vxxx As String

vName = InputBox("Please type in your name?")
If vName = "xxx" Then
Exit Sub
Else
vAddress = InputBox("Please enter your address?")
vCityStateZip = InputBox("Please enter City, State and Zip?")
vPhoneNumber = InputBox("Please enter your Phone Number?")
vEmailAddress = InputBox("Please enter your Email Address?")
vRealEstateAgentAndCompany = InputBox("Please enter your Real Estate Agent
and Company?")
Worksheets("Sheet1").Range("B1") = vName
Worksheets("Sheet1").Range("B2") = vAddress
Worksheets("Sheet1").Range("B3") = vCityStateZip
Worksheets("Sheet1").Range("B4") = vPhoneNumber
Worksheets("Sheet1").Range("B5") = vEmailAddress
Worksheets("Sheet1").Range("B6") = vRealEstateAgentAndCompany

'
Range("B1").Select
Range("B2").Select
Range("B3").Select
Range("B4").Select
Range("B5").Select
Range("B6").Select


vName = InputBox("Please type in your name?")
vAddress = InputBox("Please enter your address?")
vCityStateZip = InputBox("Please enter City, State and Zip?")
vPhoneNumber = InputBox("Please enter your Phone Number?")
vEmailAddress = InputBox("Please enter your Email Address?")
vRealEstateAgentAndCompany = InputBox("Please enter your Real Estate Agent
and Company?")
Worksheets("Sheet1").Range("B11") = vName
Worksheets("Sheet1").Range("B12") = vAddress
Worksheets("Sheet1").Range("B13") = vCityStateZip
Worksheets("Sheet1").Range("B14") = vPhoneNumber
Worksheets("Sheet1").Range("B15") = vEmailAddress
Worksheets("Sheet1").Range("B16") = vRealEstateAgentAndCompany

'
Range("B11").Select
Range("B12").Select
Range("B13").Select
Range("B14").Select
Range("B15").Select
Range("B16").Select



End If
Application.Goto Reference:="OptionExplicit"
End Sub
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort Gavin Excel Worksheet Functions 0 May 17th 07 01:20 PM
My excel macro recorder no longer shows up when recording macro jack Excel Discussion (Misc queries) 1 February 5th 07 09:31 PM
My excel macro recorder no longer shows up when recording macro jack Excel Discussion (Misc queries) 3 February 5th 07 08:22 PM
passing arguments from an excel macro to a word macro KWE39 Excel Discussion (Misc queries) 1 July 7th 05 03:56 PM
Macro - Open Word with Excel macro Bill Excel Discussion (Misc queries) 3 May 23rd 05 11:21 PM


All times are GMT +1. The time now is 07:53 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement