Hi,

I'm trying create a macro that produces an InputBox for each row with a name
in Column A (Names, and # of names will change from project to project). The
user then inputs a number into the InputBox, which is placed into the
corresponding row of Column C.

Note that the text within the InputBox states the Name in Column A and the
corresponding number in Column B (with that information, the user will know
what number to input into the box to be placed into Column C).

I've got a good chunk of the macro figured out, the problem is that I can't
figure out how to increase the Range row number by 1 for each of Columns A
and B (i.e., the text to be included in the InputBox prompt). Note, there is
a header for each column in Row 1. Here's the code I have so far:

Sub input_test()

Dim NextName_input As String
Dim NextSample_input As Long
Dim NextRow_output As Long
Dim row As Long
Dim last_row As Long
Dim pop_input As Long

With Sheets("Response_Rates")
NextName_input = Range("Response_Rates!A2") ' Need to increase by 1 somehow
NextSample_input = Range("Response_Rates!B2") 'Need to increase by 1 as well

NextRow_output = Range("Response_Rates!C65536").End(xlUp).row + 1

last_row = Range("Response_Rates!A" & Rows.Count).End(xlUp).row - 1

For row = 1 To last_row

pop_input = InputBox("Enter population for " & NextName_input & " unit.
NB: The sample size is " & NextSample_input, "Data for Response Rates")

Cells(NextRow_output, 3) = pop_input
NextRow_output = Range("Response_Rates!C65536").End(xlUp).row + 1
Next row

End With

End Sub

Thanks for any help.

Sub input_test()

Dim NextName_input As String
Dim NextSample_input As Long
Dim NextRow_output As Long
Dim row As Long
Dim last_row As Long
Dim pop_input As Long

With Sheets("Response_Rates")
RowCount = 2
NextName_input = .Range("A" & (RowCount + 1)) ' Need to increase by 1
somehow
NextSample_input = .Range("B" & (RowCount + 1)) 'Need to increase by 1 as
well

NextRow_output = .Range("C" & Rows.Count).End(xlUp).row + 1

last_row = .Range("A" & Rows.Count).End(xlUp).row - 1

For RowCount = 1 To last_row

pop_input = InputBox("Enter population for " & NextName_input & "
unit." & _
vbCrLf & "NB: The sample size is " & NextSample_input, "Data for
Response Rates")

.Range("C" & RowCount) = pop_input
Next row

End With

End Sub




"intoit" wrote:

Hi,

I'm trying create a macro that produces an InputBox for each row with a name
in Column A (Names, and # of names will change from project to project). The
user then inputs a number into the InputBox, which is placed into the
corresponding row of Column C.

Note that the text within the InputBox states the Name in Column A and the
corresponding number in Column B (with that information, the user will know
what number to input into the box to be placed into Column C).

I've got a good chunk of the macro figured out, the problem is that I can't
figure out how to increase the Range row number by 1 for each of Columns A
and B (i.e., the text to be included in the InputBox prompt). Note, there is
a header for each column in Row 1. Here's the code I have so far:

Sub input_test()

Dim NextName_input As String
Dim NextSample_input As Long
Dim NextRow_output As Long
Dim row As Long
Dim last_row As Long
Dim pop_input As Long

With Sheets("Response_Rates")
NextName_input = Range("Response_Rates!A2") ' Need to increase by 1 somehow
NextSample_input = Range("Response_Rates!B2") 'Need to increase by 1 as well

NextRow_output = Range("Response_Rates!C65536").End(xlUp).row + 1

last_row = Range("Response_Rates!A" & Rows.Count).End(xlUp).row - 1

For row = 1 To last_row

pop_input = InputBox("Enter population for " & NextName_input & " unit.
NB: The sample size is " & NextSample_input, "Data for Response Rates")

Cells(NextRow_output, 3) = pop_input
NextRow_output = Range("Response_Rates!C65536").End(xlUp).row + 1
Next row

End With

End Sub

Thanks for any help.

Thanks for the suggestion, Joel. Your macro just needed to be tweaked a bit:

change:
..Range("C" & RowCount) = pop_input

to

..Range("C" & RowCount + 1) = pop_input

(i.e., to skip the header in Column C)

"Joel" wrote:

Sub input_test()

Dim NextName_input As String
Dim NextSample_input As Long
Dim NextRow_output As Long
Dim row As Long
Dim last_row As Long
Dim pop_input As Long

With Sheets("Response_Rates")
RowCount = 2
NextName_input = .Range("A" & (RowCount + 1)) ' Need to increase by 1
somehow
NextSample_input = .Range("B" & (RowCount + 1)) 'Need to increase by 1 as
well

NextRow_output = .Range("C" & Rows.Count).End(xlUp).row + 1

last_row = .Range("A" & Rows.Count).End(xlUp).row - 1

For RowCount = 1 To last_row

pop_input = InputBox("Enter population for " & NextName_input & "
unit." & _
vbCrLf & "NB: The sample size is " & NextSample_input, "Data for
Response Rates")

.Range("C" & RowCount) = pop_input
Next row

End With

End Sub




"intoit" wrote:

Hi,

I'm trying create a macro that produces an InputBox for each row with a name
in Column A (Names, and # of names will change from project to project). The
user then inputs a number into the InputBox, which is placed into the
corresponding row of Column C.

Note that the text within the InputBox states the Name in Column A and the
corresponding number in Column B (with that information, the user will know
what number to input into the box to be placed into Column C).

I've got a good chunk of the macro figured out, the problem is that I can't
figure out how to increase the Range row number by 1 for each of Columns A
and B (i.e., the text to be included in the InputBox prompt). Note, there is
a header for each column in Row 1. Here's the code I have so far:

Sub input_test()

Dim NextName_input As String
Dim NextSample_input As Long
Dim NextRow_output As Long
Dim row As Long
Dim last_row As Long
Dim pop_input As Long

With Sheets("Response_Rates")
NextName_input = Range("Response_Rates!A2") ' Need to increase by 1 somehow
NextSample_input = Range("Response_Rates!B2") 'Need to increase by 1 as well

NextRow_output = Range("Response_Rates!C65536").End(xlUp).row + 1

last_row = Range("Response_Rates!A" & Rows.Count).End(xlUp).row - 1

For row = 1 To last_row

pop_input = InputBox("Enter population for " & NextName_input & " unit.
NB: The sample size is " & NextSample_input, "Data for Response Rates")

Cells(NextRow_output, 3) = pop_input
NextRow_output = Range("Response_Rates!C65536").End(xlUp).row + 1
Next row

End With

End Sub

Thanks for any help.