#### tratra

##### New Member
i have a questions like this, under column A, i have the following numbers
and I need to depends a period duration (cell B1, e.g.=75), then show the numbers in column C
This case, if i refer to 0,18,0
The column C (row 1-75), should shows 0
e.g.2 if i refer to 1000,6,400, and use period duration (60), then column C (row 1-60) should shows 1000,1000,1000,1000,1000,1000,0,0,0,0,0....0
e.g 3. if i refer the empty cell in column A, it shows 0 for the period duration.
I have many combinations of the following cells.
Column A
0,18, 0
0,18,0
1000,20,500
500,11,1000
....
....

1000,6,400

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
HI
I am afraid, I could not understand your problem. Do you want as many rows to be labelled in c as the value between two commas in col A? 0,18,0 means 18 rows to be labelled 0 in C
Ravi

hi

yeah, this is what i mean

for 1000,5,300, if payment period =10,
then under cloumn C become
1000
1000
1000
1000
1000
300
300
300
300
300

Hi
paste the following codes in the macro window ( Alt F11)

Code:
``````Sub jjj()
x = Cells(Rows.Count, 1).End(xlUp).Row
f = 1
For a = 1 To x
b = Cells(a, 1)
c = InStr(b, ",")
d = InStr(c + 1, b, ",")
h = Mid(b, c + 1, d - c - 1)
For e = 1 To h
Cells(f, 2) = Left(b, c - 1)
f = f + 1
Next e
For g = 1 To h
Cells(f, 2) = Right(b, Len(b) - d)
f = f + 1
Next g
Next a
End Sub``````
if col A contains the strings, on running the macro it will populate col B with first number and last number x times middle number
Ravi

if column A contains the strings and column B countains the period durations.

Code:
``````Public Sub OK()
Dim ar() As Variant
Dim i, z, x, t, Lrow, LNum, Rnum, Mnum As Integer
On Error Resume Next
Lrow = Range("A65536").End(xlUp).Row
For i = 1 To Lrow
If InStr(1, Cells(i, 1), ",") <= 0 Then
Cells(1, i + 2) = "not valid"
ElseIf Len(Cells(i, 1)) - Len(Application.Substitute(Cells(i, 1), ",", "")) <> 2 Then
Cells(1, i + 2) = "not valid"
Else
ReDim ar(1 To 2)
For t = 1 To Len(Cells(i, 1))
If Mid(Cells(i, 1), t, 1) = "," Then
x = x + 1
ar(x) = t
End If
Next
LNum = Left(Cells(i, 1), ar(1) - 1) + 0
Rnum = Right(Cells(i, 1), Len(Cells(i, 1)) - ar(2)) + 0
Mnum = Mid(Cells(i, 1), ar(1) + 1, (ar(2) - (ar(1) + 1))) + 0
If Not IsNumeric(Cells(i, 2)) Or Cells(i, 2) < 0 Then
Cells(1, i + 2) = "not valid"
Else
For z = 1 To Cells(i, 2)
If Cells(z, i + 2).Row <= Mnum Then
Cells(z, i + 2) = LNum
ElseIf Cells(z, i + 2).Row > (Cells(i, 2) - Mnum) Then
Cells(z, i + 2) = Rnum
Else: Cells(z, i + 2) = 0
End If
Next
End If
End If
x = 0
Next
End Sub``````

Replies
3
Views
180
Replies
5
Views
137
Replies
1
Views
149
Replies
31
Views
676
Replies
6
Views
316

1,221,185
Messages
6,158,409
Members
451,490
Latest member
desktopace

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back