Repetitive Data Entering

[coake]

Hey Folks,
I'm hoping for some advise on how to take the value in 1 cell of 1 row and convert it to a different sheet over 4 rows adding some constant text before and after the cell value.

For every row in Sheet1, column H (only if it contains data) I need to use the cell value and add a static value to it.

Sheet 1
Column H
This is a widget

Sheet 2
Column b
8.This is a widget L
8.This is a widget E
8.This is a widget M
8.This is a widget 3P

Here is what I'm doing on sheet 2, but sheet 1 can contain up to 500 rows...
=IF(Worklist!H17="","","8."&Worklist!H17&"-"&Worklist!K17&" L")
=IF(Worklist!H17="","","8."&Worklist!H17&"-"&Worklist!K17&" E")
=IF(Worklist!H17="","","8."&Worklist!H17&"-"&Worklist!K17&" M")
=IF(Worklist!H17="","","8."&Worklist!H17&"-"&Worklist!K17&" 3P")

Regards.

 

[RedBeard]

The values you say are static seem to be changing though...

 

[coake]

I suppose so
What I mean is sheet 1 has 1 row of data, I need to put it on sheet 2 over 4 rows always adding 8.[Sheet1 Value] & always L, E, M, 3P and always in that order.
It would also be handy to say I'm using Office365. In the past I would just complete the 4 rows of formulas and ctrl + drag and the formula would keep the same pattern - 365 doesn't seem to do this

These 12 rows (in sheet 2) would represent 3 rows from sheet 1
[TABLE="width: 472"]
<colgroup><col></colgroup><tbody>[TR]
[TD]=IF(Worklist!H130="","","8."&Worklist!H130&"-"&Worklist!K130&" L")[/TD]
[/TR]
[TR]
[TD]=IF(Worklist!H130="","","8."&Worklist!H130&"-"&Worklist!K130&" E")[/TD]
[/TR]
[TR]
[TD]=IF(Worklist!H130="","","8."&Worklist!H130&"-"&Worklist!K130&" M")[/TD]
[/TR]
[TR]
[TD]=IF(Worklist!H130="","","8."&Worklist!H130&"-"&Worklist!K130&" 3P")[/TD]
[/TR]
[TR]
[TD]=IF(Worklist!H131="","","8."&Worklist!H131&"-"&Worklist!K131&" L")[/TD]
[/TR]
[TR]
[TD]=IF(Worklist!H131="","","8."&Worklist!H131&"-"&Worklist!K131&" E")[/TD]
[/TR]
[TR]
[TD]=IF(Worklist!H131="","","8."&Worklist!H131&"-"&Worklist!K131&" M")[/TD]
[/TR]
[TR]
[TD]=IF(Worklist!H131="","","8."&Worklist!H131&"-"&Worklist!K131&" 3P")[/TD]
[/TR]
[TR]
[TD]=IF(Worklist!H132="","","8."&Worklist!H132&"-"&Worklist!K132&" L")[/TD]
[/TR]
[TR]
[TD]=IF(Worklist!H132="","","8."&Worklist!H132&"-"&Worklist!K132&" E")[/TD]
[/TR]
[TR]
[TD]=IF(Worklist!H132="","","8."&Worklist!H132&"-"&Worklist!K132&" M")[/TD]
[/TR]
[TR]
[TD]=IF(Worklist!H132="","","8."&Worklist!H132&"-"&Worklist!K132&" 3P")[/TD]
[/TR]
</tbody>[/TABLE]

 

[coake]

Ugly
My apologies, but I do not see an edit post, or I would make that a little easier on the eyes .

 

[RedBeard]

Right.

Bear in mind this is done on little sleep and with a screaming toddler so take it for what it is.

Private Sub derp()

Dim i As Long
Dim j As Long
Dim x As Long
Dim n As Long
Dim cnt As Variant
Dim rng As Range
Dim lastrow As Long
Dim countarray As Variant

lastrow = Sheet1.Cells(Rows.Count, "H").End(xlUp).Row

ReDim countarray(lastrow)

Set rng = Sheet1.Range("H1:H" & lastrow)
    
    For Each cnt In rng
    
        If Not cnt = vbNullString Then
            
            countarray(j) = cnt
            
            j = j + 1
            
        End If
    
    Next cnt
    
ReDim Preserve countarray(j)

x = 1

j = j - 1

For i = 0 To j

    n = 0

        Do Until n = 4
    
            Select Case n
            
                Case 0
                      
                    Sheet2.Range("B" & x).Value2 = ("8." & countarray(i) & "L")
                    
                Case 1
                
                    Sheet2.Range("B" & x).Value2 = ("8." & countarray(i) & "E")
                    
                Case 2
                
                    Sheet2.Range("B" & x).Value2 = ("8." & countarray(i) & "M")
                    
                Case 3
                
                    Sheet2.Range("B" & x).Value2 = ("8." & countarray(i) & "3P")
            
            End Select
    
        x = x + 1
        n = n + 1

        Loop

Next i

End Sub