Referencing differenct ranges with one input box

[Guest]

I have this looping macro set up and would like to get it to go to different
cells on the same row. I'm trying to get it so that I can enter a number for
an individual and then have multiple input boxes pop up to ask different
questions and put those answers to those questions in different cells on the
same row but different column. All with only entering the persons number
once. Here is what I got.

The AGE = "_" & ACCOUNT & "a" is where I thought I could reference a
differnet range but it doesn't work

' Macro recorded 10/17/2005 by Jeremy Barth
'
' Keyboard Shortcut: Ctrl+e

Const TXTTITLE As String = "Turkey Trot Participants"
Const NUMBERMSG As String = "Enter Runner's Number"
Const NAMEMSG As String = "Enter Runner's Name"
Const AGEMSG As String = "Enter Runner's Age"
Const GENDERMSG As String = "Enter (1) for Male or (2) for Female"
Dim ACCOUNT As Variant
Dim Name As Variant
Dim AGE As Variant
Dim GENDER As Variant

Do

ACCOUNT = InputBox(NUMBERMSG, TXTTITLE)
If ACCOUNT = "" Then Exit Sub
ACCOUNT = "_" & ACCOUNT & "n"
AGE = "_" & ACCOUNT & "a"
If ACCOUNT = "_." Then
Application.Goto Reference:="MENU"
Exit Sub
Else

With Range(ACCOUNT)
Application.Goto Reference:=.Cells
Name = InputBox(NAMEMSG, TXTTITLE)
If Name = "" Then Exit Sub
If .HasFormula Then
.Formula = Name
Else
.Formula = Name
End If
End With
AGE = "_" & ACCOUNT & "a"
With Range(ACCOUNT)
ActiveCell.Offset(0, -2).Select
AGE = InputBox(AGEMSG, TXTTITLE)
If AGE = "" Then Exit Sub
If .HasFormula Then
.Formula = AGE
Else
.Formula = AGE
End If
End With
With Range(ACCOUNT)
ActiveCell.Offset(0, -1).Select
GENDER = InputBox(GENDERMSG, TXTTITLE)
If GENDER = "" Then Exit Sub
If .HasFormula Then
.Formula = GENDER
Else
.Formula = GENDER
End If
End With
End If
Loop
End Sub

TIA

 

[Rowan Drummond]

Hi Jeremy

It is pretty difficult to follow what you are trying to achieve with
this code. I had a look at it the other day but gave up after a few
minutes. For example:

ACCOUNT = "_" & ACCOUNT & "n"
AGE = "_" & ACCOUNT & "a"
If ACCOUNT = "_." Then
Application.Goto Reference:="MENU"
Exit Sub
Else

You are appending the character "n" onto the end of the account number
but then checking that the account is not equal to "_." If the user
enters "." as the account then it would be changed to "_.n" and can
never equal "_.".

Also, I could not work out the function of this if statement:
If .HasFormula Then
.Formula = Name
Else
.Formula = Name
End If
Your action for the if and else clauses is exactly the same so it seems
redundant.

Maybe it would be better to start by describing exactly what you are
wanting to do e.g do the account numbers already exist and you are
wanting to add more data to the rows where the account is found or are
you wanting to create new rows with new account numbers. What data are
you wanting to capture in which columns etc.

Regards
Rowan

 

[Guest]

Rowan,

Thanks for the reply.
I know its confusing, I had somebody help me with it using it in a different
application. The funny thing is it does what I want it to do in that
application. I'm trying to creat a data entry macro using one number
reference. I am putting it together for a charity road race that I help run.
The section:
ACCOUNT = "_" & ACCOUNT & "n"
AGE = "_" & ACCOUNT & "a"
If ACCOUNT = "_." Then
Is done so that I can have multiple cells named the same for ex. 1000 and
1000n and 1000a the _ is needed because excel won't allow you to name a cell
a number so you have to have that in the front. You are correct the . is not
need and I have gotten rid of that but this was copied from an older version
and forgot to take it out. I am trying to create this so that it loops so
that I can enter runners in without haveing to look at where they are being
entered just by their reference ex 1000.
I have since got it to work a little different and maybe you would have an
idea on this, if not thanks for your reply. This actually goes to the cell
that I want to enter data into but the data gets entered into the original
cell with the "a" after it.

Const TXTTITLE As String = "Turkey Trot Participants"
Const NUMBERMSG As String = "Enter Runner's Number"
Const NAMEMSG As String = "Enter Runner's Name"
Const AGEMSG As String = "Enter Runner's Age"
Const GENDERMSG As String = "Enter (1) for Male or (2) for Female"
Dim ACCOUNT As Variant
Dim Name As Variant
Dim AGE As Variant
Dim GENDER As Variant

Do

ACCOUNT = InputBox(NUMBERMSG, TXTTITLE)
If ACCOUNT = "" Then Exit Sub
ACCOUNT = "_" & ACCOUNT & "n"
AGE = "_" & ACCOUNT & "a"
If ACCOUNT = "" Then
Application.Goto Reference:="MENU"
Exit Sub
Else

With Range(ACCOUNT)
Application.Goto Reference:=.Cells
Name = InputBox(NAMEMSG, TXTTITLE)
If Name = "" Then Exit Sub
If .HasFormula Then
.Formula = Name
Else
.Formula = Name
End If
End With
With Range(ACCOUNT)
ActiveCell.Offset(0, -2).Select
AGE = InputBox(AGEMSG, TXTTITLE)
If AGE = "" Then Exit Sub
If .HasFormula Then
.Formula = AGE
Else
.Formula = AGE
End If
End With
With Range(ACCOUNT)
ActiveCell.Offset(0, -1).Select
GENDER = InputBox(GENDERMSG, TXTTITLE)
If GENDER = "" Then Exit Sub
If .HasFormula Then
.Formula = GENDER
Else
.Formula = GENDER
End If
End With
End If
Loop
End Sub

 

[Rowan Drummond]

Hi Jeremy

The reason that the data is getting entered into the ACCOUNT field is to
do with your With statements. Bear in mind that you do not have to
select a cell in order to manipulate it so in the code below you are
using two different referencing systems. The With block allows you to
manipulate the range represented by ACCOUNT so when you use a statement
like .Hasformula in the With block this is refering to that range.
Activecell.Offset(0,-1).Select sets the activcell but then you do not
actually change the activecells value at any point.

With Range(ACCOUNT)
ActiveCell.Offset(0, -1).Select
GENDER = InputBox(GENDERMSG, TXTTITLE)
If GENDER = "" Then Exit Sub
If .HasFormula Then
.Formula = GENDER
Else
.Formula = GENDER
End If
End With

So you might rewrite this as:

With Range(ACCOUNT)
GENDER = InputBox(GENDERMSG, TXTTITLE)
If GENDER = "" Then Exit Sub
If .Offset(0,-1).HasFormula Then
.Offset(0,-1).Formula = GENDER
Else
.Offset(0,-1).Formula = GENDER
End If
End With

or even more simply:

With Range(ACCOUNT).Offset(0,-1)
GENDER = InputBox(GENDERMSG, TXTTITLE)
If GENDER = "" Then Exit Sub
If .HasFormula Then
.Formula = GENDER
Else
.Formula = GENDER
End If
End With

Having said that I still don't see the point of the IF statement as the
actions for the If and Else are exactly the same so it is redundant.

Hope this helps
Rowan

 

[Guest]

Thanks Rowan,
I actually had figured it out, somewhat simular to what you had done.
Thanks for the help though.

Jeremy

 

[Rowan Drummond]

You're welcome. Glad you got it working.

Regards
Rowan