Urgent excel help!!!! Please help

tratra

New Member
Joined
Jun 10, 2006
Messages
17
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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

New Member
Joined
Jun 10, 2006
Messages
17
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

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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
 

facethegod

Well-known Member
Joined
Aug 1, 2006
Messages
767
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
 

Forum statistics

Threads
1,181,363
Messages
5,929,538
Members
436,676
Latest member
Mavri

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top