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

miss c

New Member
Joined
Nov 19, 2018
Messages
4
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 :

VBA Code:
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 !
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
If I understand correctly, perhaps:

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

VBA Code:
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
 
Solution

miss c

New Member
Joined
Nov 19, 2018
Messages
4
Yesss, this is exactly what I was looking for! Now that I see it, it makes sense. Thanks a lot!
 

Forum statistics

Threads
1,147,482
Messages
5,741,409
Members
423,658
Latest member
Kumaradas

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