Marco help - fill down data based a value

[Garethc01]

Hi Excel Guru's

I'm hoping one of you can help me with a macro in Excel 2007.

I have a row of data as follows on 'sheet1' with the word 'Dog' starting in cell A1.

	A	B	C
1	Dog	Cat	Bird
2	1	2	3

<tbody>
</tbody>

What I am after is a macro that will on 'sheet2' list the data as shown below. I want the word to appear the number of times based on the value below it.

	A
1	Dog
2	Cat
3	Cat
4	Bird
5	Bird
6	Bird
7	Bird

<tbody>
</tbody>

Any assistance would be greatly appreciated.
Gareth

 

[dermie_72]

you could try:

Sub Garethc01()
'for http://www.mrexcel.com/forum/excel-questions/741644-marco-help-fill-down-data-based-value.html
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To LC
    LR = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
    For X = 1 To Cells(2, i).Value
        Sheets("Sheet2").Cells(LR, 1).Value = Sheets("Sheet1").Cells(1, i).Value
        LR = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
    Next X
Next i
End Sub

 

[JoeMo]

Another take:

Sub Gareth()
Dim R As Range, vA As Variant, Ct As Long, lC As Long
lC = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
Set R = Sheets("Sheet1").Range(Cells(1, 1), Cells(2, lC))
vA = R.Value
Application.ScreenUpdating = False
With Sheets("Sheet2")
    For i = 1 To UBound(vA, 2)
        .Range("A1").Offset(Ct, 0).Resize(vA(2, i), 1).Value = vA(1, i)
        Ct = Ct + vA(2, i)
    Next i
End With
Application.ScreenUpdating = True
End Sub

 

[Garethc01]

Wow - thanks for the fast replies.

Dermie - yours works great. However I can only run it from 'sheet1' but I was hoping to have it run from 'sheet2' which is the destination. Is this possible??

Thank you so much for your help.

you could try:

Sub Garethc01()
'for http://www.mrexcel.com/forum/excel-questions/741644-marco-help-fill-down-data-based-value.html
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To LC
    LR = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
    For X = 1 To Cells(2, i).Value
        Sheets("Sheet2").Cells(LR, 1).Value = Sheets("Sheet1").Cells(1, i).Value
        LR = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
    Next X
Next i
End Sub

 

[JoeMo]

The code I posted can be run from any sheet in the workbook.

 

[dermie_72]

Wow - thanks for the fast replies.

Dermie - yours works great. However I can only run it from 'sheet1' but I was hoping to have it run from 'sheet2' which is the destination. Is this possible??

Thank you so much for your help.

Yes def possible, however as Jow said, his one works from any sheet and should be faster than mine too. Nice work Joe.

If you want to adjust mine to work from any sheet, you just need to put Sheets("Sheet1"). in front of any Cells that do not have any text prior to it.
so LC = Cells(1, Columns.Count).End(xlToLeft).Column becomes LC = sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column ect.

 

[Garethc01]

Hi Joe,

I tried running yours again but unfortunately i get an Run-Time Error 1004 - Application defined or object-defined error. When I click on 'Debug' it highlights the following row Set R = Sheets("Sheet1").Range(Cells(1, 1), Cells(2, lC))

Any idea why?

Thanks Dermie I will try that too.

Appreciate the help

 

[JoeMo]

Hi Joe,

I tried running yours again but unfortunately i get an Run-Time Error 1004 - Application defined or object-defined error. When I click on 'Debug' it highlights the following row Set R = Sheets("Sheet1").Range(Cells(1, 1), Cells(2, lC))

Any idea why?

Thanks Dermie I will try that too.

Appreciate the help

Do you have a sheet named "Sheet1" in your workbook?

 

[Garethc01]

Yep the first sheet where the original data is, is called 'Sheet1'

 

[JoeMo]

Yep the first sheet where the original data is, is called 'Sheet1'

Runs fine for me. Did you copy the code I posted directly from your browser or did you type it in? If the latter, in this piece of the offending line:
Cells(2, lC)
the last argument before the closing ")" is lower case L ("l") (NOT a one (1) or an I) followed by C.