Macro in Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cedric - 19 Mar 2008 19:38 GMT

> 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 to this Message

Don Guillett - 19 Mar 2008 20:32 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>> I have a macro in Microsoft Excel listed below. I need for it to repeat
>> until xxx is entered. Any help will be appreciated.
[quoted text clipped - 64 lines]
>> Application.Goto Reference:="OptionExplicit"
>> End Sub

Reply to this Message

FSt1 - 19 Mar 2008 20:41 GMT

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

> > I have a macro in Microsoft Excel listed below. I need for it to repeat
> > until xxx is entered. Any help will be appreciated.
[quoted text clipped - 65 lines]
> > Application.Goto Reference:="OptionExplicit"
> > End Sub

Reply to this Message

Cedric - 20 Mar 2008 02:19 GMT

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.

> hi
> to continue our conversations on 3/13...
[quoted text clipped - 111 lines]
> > > Application.Goto Reference:="OptionExplicit"
> > > End Sub

Reply to this Message

Dana DeLouis - 20 Mar 2008 04:25 GMT

>> Worksheets("Sheet1").Range("B1") = vName
>> Worksheets("Sheet1").Range("B2") = vAddress
[quoted text clipped - 3 lines]
>> 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

>> I have a macro in Microsoft Excel listed below. I need for it to repeat
>> until xxx is entered. Any help will be appreciated.
[quoted text clipped - 65 lines]
>> Application.Goto Reference:="OptionExplicit"
>> End Sub

Reply to this Message