Urgent excel help!!!! Please help

[tratra]

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

Please help me cuz I have no idea how to start. Thank you.

 

[ravishankar]

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

 

[tratra]

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

 

[ravishankar]

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

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

 

[facethegod]

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

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