Hi,

I have a column of values of finance data. ColumnB with 10 values

Col B
$10
$20
$30
$40
$50
$60
$70
$80
$90
$100

User is prompted to select a row of these 10 rows (using Inputbox option).
Suppose user selects row#5 with $50 value.

How should I copy the values UPTO row#5 of Column B and paste in Column C
without hardcoding?

How should I copy the values AFTER row#5 UPTO row# 10 of Column B and paste
in Column D, STARTING FROM ROW 6 without hardcoding?

Required output

ColB ColC ColD
$10 $10
$20 $20
$30 $30
$40 $40
$50 $50 $50
$60 $60
$70 $70
$80 $80
$90 $90
$100 $100

Not sure what you mean by hardcoding, but this is where I would start.
Tell us what you don't like about it.

Allllen

Sub TryThis()

Dim i As String, readrow As Integer

i = InputBox("Enter the row number")
If i = "" Then Exit Sub

On Error Resume Next
If i < 2 Or i 10 Then MsgBox ("Out of range"): Exit Sub

For readrow = 2 To i
Cells(readrow, 3).Value = Cells(readrow, 2).Value
Next readrow

For readrow = i To 11
Cells(readrow, 4).Value = Cells(readrow, 2).Value
Next readrow
On Error GoTo 0

End Sub
--
Allllen


"Dimri" wrote:

Hi,

I have a column of values of finance data. ColumnB with 10 values

Col B
$10
$20
$30
$40
$50
$60
$70
$80
$90
$100

User is prompted to select a row of these 10 rows (using Inputbox option).
Suppose user selects row#5 with $50 value.

How should I copy the values UPTO row#5 of Column B and paste in Column C
without hardcoding?

How should I copy the values AFTER row#5 UPTO row# 10 of Column B and paste
in Column D, STARTING FROM ROW 6 without hardcoding?

Required output

ColB ColC ColD
$10 $10
$20 $20
$30 $30
$40 $40
$50 $50 $50
$60 $60
$70 $70
$80 $80
$90 $90
$100 $100

sorry, should be

If i < 2 Or i 11 Then MsgBox ("Out of range"): Exit Sub

you have 11 rows not 10
--
Allllen


"Allllen" wrote:

Not sure what you mean by hardcoding, but this is where I would start.
Tell us what you don't like about it.

Allllen

Sub TryThis()

Dim i As String, readrow As Integer

i = InputBox("Enter the row number")
If i = "" Then Exit Sub

On Error Resume Next
If i < 2 Or i 10 Then MsgBox ("Out of range"): Exit Sub

For readrow = 2 To i
Cells(readrow, 3).Value = Cells(readrow, 2).Value
Next readrow

For readrow = i To 11
Cells(readrow, 4).Value = Cells(readrow, 2).Value
Next readrow
On Error GoTo 0

End Sub
--
Allllen


"Dimri" wrote:

Hi,

I have a column of values of finance data. ColumnB with 10 values

Col B
$10
$20
$30
$40
$50
$60
$70
$80
$90
$100

User is prompted to select a row of these 10 rows (using Inputbox option).
Suppose user selects row#5 with $50 value.

How should I copy the values UPTO row#5 of Column B and paste in Column C
without hardcoding?

How should I copy the values AFTER row#5 UPTO row# 10 of Column B and paste
in Column D, STARTING FROM ROW 6 without hardcoding?

Required output

ColB ColC ColD
$10 $10
$20 $20
$30 $30
$40 $40
$50 $50 $50
$60 $60
$70 $70
$80 $80
$90 $90
$100 $100