Create range for every 2nd cell and then use as value into sting

[miss c]

Hi,

I am a total newbie at VBA so I apologize in advance if the code looks suspicious. I am trying to copy data from one sheet to another and string two cells value into one.

This is the code I started experimenting with :

Sub EpTitle()

Dim OrgnlNm As String
Dim DubNm  As String
Dim i As Integer
OrgnlNm = Worksheets("Production").Range("C6").Value 'Item number 1 - original language
DubNm = Worksheets("Production").Range("C7").Value 'Item number 1 - dubbed language

 Application.ScreenUpdating = False
    
    For i = 1 To Worksheets("Production").Range("A5").Value ' Worksheets("Production").Range("A5") is a variable numeral value ranging from 1 to 52
    Worksheets("List").Range("B" & i + 6).Value = "VO : " & OrgnlNm & " - VF : " & DubNm 'combining Original name and Dubbed name in one cell starting at B7
    
    Next i

  Application.ScreenUpdating = True
 
End Sub

This is joining the Original Name and the Dubbed Name of the first item and copying it in a column x number of times depending on a numeral value.


row 6 # (A) Items (B)
row 7 Item 1: VO : Original Name Item 1 - VF : Dubbed Name Item 1
row 8 Item 2: VO : Original Name Item 1 - VF : Dubbed Name Item 1
row 9 Item 3: VO : Original Name Item 1 - VF : Dubbed Name Item 1


However, what I need is to update the code to reflect the whole range of Item Names.

row 6 # (A) Items (B)
row 7 Item 1: VO : Original Name Item 1 - VF : Dubbed Name Item 1
row 8 Item 2: VO : Original Name Item 2 - VF : Dubbed Name Item 2
row 9 Item 3: VO : Original Name Item 3 - VF : Dubbed Name Item 3


The Original Name starting point is Worksheets("Production").Range("C6"), then every 2nd cell down (variable number of time, max row 108)
The Dubbed Name starting point is Worksheets("Production").Range("C7"), then every 2nd cell down (variable number of time, max row 109)


I looked at the ‘step’ function but I have no clue how to integrate that into my current code or even if this is the way to go. Can anyone help me with this ?


Thanks !

 

[mrshl9898]

If I understand correctly, perhaps:

(1*2) + 4 = 6
(2*2) + 4 = 8 etc..

Sub EpTitle()

Dim i As Integer
Dim x As Integer

 Application.ScreenUpdating = False
 
    For i = 1 To Worksheets("Production").Range("A5").Value ' Worksheets("Production").Range("A5") is a variable numeral value ranging from 1 to 52
    x = i * 2
    Worksheets("List").Range("B" & i + 6).Value = "VO : " & Worksheets("Production").Range("C" & x + 4) & " - VF : " & Worksheets("Production").Range("C" & x + 5) 'combining Original name and Dubbed name in one cell starting at B7
 
    Next i

  Application.ScreenUpdating = True
 
End Sub

 

[miss c]

Yesss, this is exactly what I was looking for! Now that I see it, it makes sense. Thanks a lot!