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

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

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

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

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