Excel VBA - easy Loop

Question

I would like to write a script that looks like a loop to me. My data is under column A.

I want to put script say in column B. eg B2 = A2
Under B3 I put in "xxxx" and under B4 I put in "yyyy" and then repeat step 1 & 2 until end of data.

So far I have the below. How can I loop it as I have to like type it up another many hundred times.... Thanks.

Sub DATA()

Dim DL As Long
Dim Script1 As String
Dim Script2 As String

DL = Range("A" & Rows.Count).End(xlUp).Row
Script1 = "KEY END"
Script2 = "KEY WAIT"

Range("B2").Value = Range("A2")
Range("B3").Value = Script1
Range("B4").Value = Script2

Range("B5").Value = Range("A3")
Range("B6").Value = Script1
Range("B7").Value = Script2

Range("B8").Value = Range("A4")
Range("B9").Value = Script1
Range("B10").Value = Script2   

End Sub

You already know you need a loop. Do a loop.

ApplePie
– ApplePie

2013-08-30 15:56:50 +00:00
Commented Aug 30, 2013 at 15:56 — ApplePie
– ApplePie, Commented Aug 30, 2013 at 15:56

Kazimierz Jawor · Accepted Answer · 2013-08-30 16:02:33Z

2

Trying to convert your code into 'loop logic' you get something like this:

'beginning of your code here
Dim i As Long
for i=2 to DL

    Range("B" & i*3-4).Value = Range("A" & i)
    Range("B" & i*3-3).Value = Script1
    Range("B" & i*3-2).Value = Script2

next i
'the end of your sub here

answered Aug 30, 2013 at 16:02

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tigeravatar · Accepted Answer · 2013-08-30 16:23:20Z

0

Sub tgr()

    Dim arrData() As Variant
    Dim varAcell As Variant
    Dim strScript1 As String
    Dim strScript2 As String
    Dim DataIndex As Long
    Dim i As Long

    strScript1 = "KEY END"
    strScript2 = "KEY WAIT"

    With Range("A2", Cells(Rows.Count, "A").End(xlUp))
        If .Row < 2 Then Exit Sub   'No data
        ReDim arrData(1 To .Rows.Count * 3)
        For Each varAcell In .Value
            For i = 1 To 3
                DataIndex = DataIndex + 1
                arrData(DataIndex) = Choose(i, varAcell, strScript1, strScript2)
            Next i
        Next varAcell
    End With

    Range("B2").Resize(UBound(arrData)).Value = Application.Transpose(arrData)

    Erase arrData

End Sub

[EDIT]

For @KazJaw:

VBA Erase Statement

edited Aug 30, 2013 at 16:23

answered Aug 30, 2013 at 16:13

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

3 Comments

Kazimierz Jawor Over a year ago

Erase??... not VBA!!

tigeravatar Over a year ago

@KazJaw I have edited my answer to include the VBA Erase Statement from the Excel VBA Help. I'm not sure what you mean by "not VBA"

Kazimierz Jawor Over a year ago

@tigeravatar, thanks for the info. the funny thing is that I have never used it and was not aware that erase statement exists. And I don't think I lost anything without knowing it, do I?.

Collectives™ on Stack Overflow

Excel VBA - easy Loop

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related