Macro in Excel

[Cedric]

I have a macro in Microsoft Excel listed below. I need for it to repeat

 

[Don Guillett]

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
(e-mail address removed)

 

[FSt1]

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]

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.

 

[Dana DeLouis]

Worksheets("Sheet1").Range("B1") = vName
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