Vba code correction with different range

[ANTONIO1981]

hi

This code looks into data stored in columns A to N

SITE COST Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 Nov-18 Dec-18

To create a table that goes from A to D

Public Sub Conver_variable_cost_into_final_table()
Dim sWS     As Worksheet, _
    dWS     As Worksheet
    
Dim i       As Long, _
    j       As Long, _
    rowx    As Long
    
Dim LR      As Long


Dim Site    As String, _
    Cost    As String, _
    Dte     As Date, _
    Amt     As Double


With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With


Set sWS = Sheets("VARIABLE_COST")
Set dWS = Sheets("VARIABLE_COST_TABLE")


dWS.Range("A1").Value = "Site"
dWS.Range("B1").Value = "Month"
dWS.Range("C1").Value = "Cost"
dWS.Range("D1").Value = "Amount"


LR = sWS.Range("A" & Rows.Count).End(xlUp).Row
rowx = 2


For i = 2 To LR
    'Collect Site and Cost names
    Site = sWS.Range("A" & i).Value
    Cost = sWS.Range("B" & i).Value
    For j = 1 To 12
        'Collect Date and Amount
        Dte = sWS.Cells(1, j + 2).Value
        Amt = sWS.Cells(i, j + 2).Value
        
        'Store Values in new table
        dWS.Range("A" & rowx).Value = Site
        dWS.Range("B" & rowx).Value = Dte
        dWS.Range("C" & rowx).Value = Cost
        dWS.Range("D" & rowx).Value = Amt
        rowx = rowx + 1
    Next j
Next i


With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With


End Sub



[CODE]



how the code will look like if instead of columns A to N 
[U][B]i have now the data stored in AD to AQ[/B][/U]

the table that creates still goes from A to D

i tried to change the names in the columns but is not right

thanks in advance

Anthony

 

[Fluff]

Hia
Try

For i = 2 To LR
    'Collect Site and Cost names
    Site = sWS.Range("A" & i).Value
    Cost = sWS.Range("B" & i).Value
    [COLOR=#0000ff]For j = 30 To 43[/COLOR]
        'Collect Date and Amount
        Dte = sWS.[COLOR=#0000ff]Cells(1, j)[/COLOR].Value
        Amt = sWS.[COLOR=#0000ff]Cells(i, j)[/COLOR].Value
        
        'Store Values in new table
        dWS.Range("A" & rowx).Value = Site
        dWS.Range("B" & rowx).Value = Dte
        dWS.Range("C" & rowx).Value = Cost
        dWS.Range("D" & rowx).Value = Amt
        rowx = rowx + 1
    Next j
Next i

The parts of your code that I've changed are in blue

 

[ANTONIO1981]

code:

Public Sub Conver_variable_cost_into_final_table()
Dim sWS As Worksheet, _
dWS As Worksheet


Dim i As Long, _
j As Long, _
rowx As Long


Dim LR As Long




Dim Site As String, _
Cost As String, _
Dte As Date, _
Amt As Double




With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With




Set sWS = Sheets("Sheet1")
Set dWS = Sheets("Sheet2")




dWS.Range("A1").Value = "Site"
dWS.Range("B1").Value = "Month"
dWS.Range("C1").Value = "Cost"
dWS.Range("D1").Value = "Amount"




LR = sWS.Range("AD" & Rows.Count).End(xlUp).Row
rowx = 2




For i = 2 To LR
'Collect Site and Cost names
Site = sWS.Range("AD" & i).Value
Cost = sWS.Range("AE" & i).Value
For j = 30 To 43
'Collect Date and Amount
Dte = sWS.Cells(1, j).Value
Amt = sWS.Cells(i, j).Value


'Store Values in new table
dWS.Range("A" & rowx).Value = Site
dWS.Range("B" & rowx).Value = Dte
dWS.Range("C" & rowx).Value = Cost
dWS.Range("D" & rowx).Value = Amt
rowx = rowx + 1
Next j
Next i




With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With




End Sub








i tried this but gives an error


can you help?

thanks in advance

 

[Fluff]

oops
should be

For j = 32 To 43

 

[ANTONIO1981]

many thanks! works ok!!

 

[Fluff]

Glad to help & thanks for the feedback