Addressing problem in loop

[kraadde]

Sub Macro2()
'
' Macro2 Macro
'
Dim j As Long
Dim id As Long
j = 1
For id = 2 To 5
'
Cells(j, 1).Value = "=+Liste_2016_WEB_RevD.csv!R2C1&"",%pr.dob%,%%%""&Liste_2016_WEB_RevD.csv!R2C16&"" 00:00:00%%%,%1%"""
Cells(j + 1, 1).Value = "=+Liste_2016_WEB_RevD.csv!R2C1&"",%pr.modnr%,%%%""&Liste_2016_WEB_RevD.csv!R2C13&""%%%,%9%"""
Cells(j + 2, 1).Value = "=+Liste_2016_WEB_RevD.csv!R2C1&"",%pr.nr%,%%%""&Liste_2016_WEB_RevD.csv!R2C9&""%%%,%5%"""
Cells(j + 3, 1).Value = "=+Liste_2016_WEB_RevD.csv!R2C1&"",%pr.ort%,%%%""&Liste_2016_WEB_RevD.csv!R2C11&""%%%,%7%"""
Cells(j + 4, 1).Value = "=+Liste_2016_WEB_RevD.csv!R2C1&"",%pr.plz%,%%%""&Liste_2016_WEB_RevD.csv!R2C10&""%%%,%6%"""
Cells(j + 5, 1).Value = "=+Liste_2016_WEB_RevD.csv!R2C1&"",%pr.str%,%%%""&Liste_2016_WEB_RevD.csv!R2C8&""%%%,%4%"""
Cells(j + 6, 1).Value = "=+Liste_2016_WEB_RevD.csv!R2C1&"",%pr.telnr%,%%%""&Liste_2016_WEB_RevD.csv!R2C12&""%%%,%8%"""
Cells(j + 7, 1).Value = "=+Liste_2016_WEB_RevD.csv!R2C1&"",%pr.username%,%%%""&Liste_2016_WEB_RevD.csv!R2C5&""%%%,%2%"""
Cells(j + 8, 1).Value = "=+Liste_2016_WEB_RevD.csv!R2C1&"",%pr.uservor%,%%%""&Liste_2016_WEB_RevD.csv!R2C6&""%%%,%3%"""
j = j + 9
Next id
End Sub


Above code works and indeed copies cell content from the "List..." worksheet to the active one.
What I need however, is to get the same, but instead of the cells addressed R2C1 resp. R2C.., to replace the row address R2 to the loop counter id.
How to do that?
Thanks for the help

[mikerickson]

First, I would change the loop to



For j = 1 To 28 step 9
'
Cells(j, 1).Value = "=+Liste_2016_WEB_RevD.csv!R2C1&"",%pr.dob%,%%%""&Liste_2016_WEB_RevD.csv!R2C16&"" 00:00:00%%%,%1%"""
' ect
' no j=j+9 line
Next id


And it sounds like you want to use the relative reference RC1, i.e. the first column of the row in question.

[kraadde]

First, I would change the loop to



For j = 1 To 28 step 9
'
Cells(j, 1).Value = "=+Liste_2016_WEB_RevD.csv!R2C1&"",%pr.dob%,%%%""&Liste_2016_WEB_RevD.csv!R2C16&"" 00:00:00%%%,%1%"""
' ect
' no j=j+9 line
Next id


And it sounds like you want to use the relative reference RC1, i.e. the first column of the row in question.

[kraadde]

Thanks sofar.

However my problemt will not be solved-

What I want to achieve is that after the set of 9 copied lines, a further set of lines shall be copied, where the reference shall not be R1C1, but R2C1 and so on--
Hope I made myself clear...
regards Adriano

[snb]

Did you consider


Sub M_snb()
sheets.add ,sheets(sheets.count),,"C:\Liste_2016_WEB_RevD.csv"
end sub

[Leith Ross]

Hello kraadde,

This amended version uses "id" in place of 2,3,4, and 5 along with "R" in the formulae.



Sub Macro2()
'
' Macro2 Macro
'
Dim j As Long
Dim id As Long
j = 1
For id = 2 To 5
'
Cells(j, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.dob%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C16&"" 00:00:00%%%,%1%"""
Cells(j + 1, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.modnr%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C13&""%%%,%9%"""
Cells(j + 2, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.nr%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C9&""%%%,%5%"""
Cells(j + 3, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.ort%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C11&""%%%,%7%"""
Cells(j + 4, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.plz%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C10&""%%%,%6%"""
Cells(j + 5, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.str%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C8&""%%%,%4%"""
Cells(j + 6, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.telnr%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C12&""%%%,%8%"""
Cells(j + 7, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.username%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C5&""%%%,%2%"""
Cells(j + 8, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.uservor%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C6&""%%%,%3%"""
j = j + 9
Next id
End Sub

[Leith Ross]

Hello kraadde,

This amended version uses "id" in place of 2 with "R" in the formulae.



Sub Macro2()
'
' Macro2 Macro
'
Dim j As Long
Dim id As Long
j = 1
For id = 2 To 5
'
Cells(j, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.dob%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C16&"" 00:00:00%%%,%1%"""
Cells(j + 1, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.modnr%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C13&""%%%,%9%"""
Cells(j + 2, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.nr%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C9&""%%%,%5%"""
Cells(j + 3, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.ort%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C11&""%%%,%7%"""
Cells(j + 4, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.plz%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C10&""%%%,%6%"""
Cells(j + 5, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.str%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C8&""%%%,%4%"""
Cells(j + 6, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.telnr%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C12&""%%%,%8%"""
Cells(j + 7, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.username%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C5&""%%%,%2%"""
Cells(j + 8, 1).Value = "=+Liste_2016_WEB_RevD.csv!R" & id & "C1&"",%pr.uservor%,%%%""&Liste_2016_WEB_RevD.csv!R" & id & "C6&""%%%,%3%"""
j = j + 9
Next id
End Sub

[kraadde]

Is your proposal not just copying fron source to new ws?
What I Need is shown in the pict below:

16590

This means transposing row by row
Regards
Adriano

[SamT]

THis works?

Cells(j, 1).Value = _
& _
"=+Liste_2016_WEB_RevD.csv!R2C1 _ 'Only starts with a quote mark
& _
"",%pr.dob%,%%%"" _ 'Quoted String
& _
Liste_2016_WEB_RevD.csv!R2C16 _ 'Not a String
& _
"" 00:00:00%%%,%1%""" 'Quoted String with extra quote
What am I missing. Are the Ampersands and paired Double Quotes part of the Cells's Formula.

I am trying to figure out how to build a concatenated string from that.


Const Start As String = "=Liste_2016_WEB_RevD.csv!"
Const Liste as String = "Liste_2016_WEB_RevD.csv!"
Const Pc3 As String ="%%%"
Const DQ As String = """
Const pr as String = ",%pr."

Cells(J, 1).Formula = Start & J & "A" & DQ & pr & "dob%," & Pc3 & DQ & Liste & J & "S" & DQ & "00:00:00" & Pc3 & "%1%" & DQ
Then only the red parts below would need to be changes in each line.
Cells(J, 1).Formula = Start & J & "A" & DQ & pr & "dob%," & Pc3 & DQ & Liste & J & "S" & DQ & "00:00:00" & Pc3 & "%1%" & DQ

Then you could use arrays for the red values

Const Col2 = Array("S", "M", "I", "K", "J", "H", "L", "E", "F")
Const TimeVal = Aray("00:00:00", "", "", "", "", "", "", "", "", "")
Const prVal = Array("dob%", etc . . . )
Const LastVal = . . .
And substitue arrVar(J) for the varying values in the formula, resulting in a sinllge line that is looped thru by J

But I am not sure about what appears to be in-cell Ampersands.

[kraadde]

THis works?

Cells(j, 1).Value = _
& _
"=+Liste_2016_WEB_RevD.csv!R2C1 _ 'Only starts with a quote mark
& _
"",%pr.dob%,%%%"" _ 'Quoted String
& _
Liste_2016_WEB_RevD.csv!R2C16 _ 'Not a String
& _
"" 00:00:00%%%,%1%""" 'Quoted String with extra quote
What am I missing. Are the Ampersands and paired Double Quotes part of the Cells's Formula.

I am trying to figure out how to build a concatenated string from that.


Const Start As String = "=Liste_2016_WEB_RevD.csv!"
Const Liste as String = "Liste_2016_WEB_RevD.csv!"
Const Pc3 As String ="%%%"
Const DQ As String = """
Const pr as String = ",%pr."

Cells(J, 1).Formula = Start & J & "A" & DQ & pr & "dob%," & Pc3 & DQ & Liste & J & "S" & DQ & "00:00:00" & Pc3 & "%1%" & DQ
Then only the red parts below would need to be changes in each line.
Cells(J, 1).Formula = Start & J & "A" & DQ & pr & "dob%," & Pc3 & DQ & Liste & J & "S" & DQ & "00:00:00" & Pc3 & "%1%" & DQ

Then you could use arrays for the red values

Const Col2 = Array("S", "M", "I", "K", "J", "H", "L", "E", "F")
Const TimeVal = Aray("00:00:00", "", "", "", "", "", "", "", "", "")
Const prVal = Array("dob%", etc . . . )
Const LastVal = . . .
And substitue arrVar(J) for the varying values in the formula, resulting in a sinllge line that is looped thru by J

But I am not sure about what appears to be in-cell Ampersands.

[kraadde]

Thank you all, the proposed solution of Leith Ross was it!

[snb]

Looks simpler:


Sub M_snb()
sn=split(replace(createobject("scripting.filesystemobject").opentextfile("C:\Liste_2016_WEB_RevD.csv").readall,vbCrLf,","),",")
sheet1.cells(1).resize(Ubound(sn)+1)=application.transpose(sn)
End sub