VBA - Convert my variable range array to single cell string

[Kevin G]

I have a range a cells J9:J520 (this range only used for a chart series)
and I am trying to convert this to a string so it can be pasted into a
single cell. I would like to convert this back to a variable range when
needed to chart again.


Thanks, Kevin

 

[Tom Ogilvy]

Assume you had a range J9:J11

J9: 1
J10: 2
J11: 3

What would you expect to see in the single cell?

 

[Kevin G]

1, 2, 3, etc

I have 512 cells each with 3 numerals.

Thanks, Kevin Graham

 

[Tom Ogilvy]

Dim sStr as String
Dim cell as Range
sStr = ""
for each cell in Range("J9:J520")
sStr = sStr & cell.Value & ", "
Next
sStr = Left(sStr,len(sStr)-2)
ActiveCell.Value = sStr

 

[Kevin G]

Thanks Tom. You sure make it look easy.

How would you return to J9:J520 range?

 

[Rob van Gelder]

Or:

Sub test()
Dim sStr As String
Dim cell As Range
sStr = ""
For Each cell In Range("J9:J520")
sStr = sStr & IIf(sStr = "", "", ", ") & cell.Value
Next
ActiveCell.Value = sStr
End Sub


Rob

 

[Kevin G]

Tom,
Thanks for your code. I am still confused. Could you help me with my
problem? Below will explain why I need to convert a particular range to a
string and then back again.

I have 2 work sheets.
1) FirstFeltData
2) FirstFeltSheet

FirstFeltData is basically a Dbase utilizing columns A to CS.
FirstFeltSheet is my presentation sheet printed for my customer. This
includes charts.

I collect data weekly and input into "FirstFeltData"sheet. By: Copy row 2
and insert pasting formats and formulas and then input my new data.) This
Dbase is sorted by date (Column B). I then have a macro below which will
1st: copy the "FirstFeltData" Header row ("B1:CS1") and paste to column G in
the "FirstFeltSheet" . 2nd: Copy "FirstFeltData" ActiveCell (Date) Range
("A1:CR1") to column G in the "FirstFeltSheet". 3rd: "FirstFeltSheet"
Copy/Paste Header and data info to specific ranges.

The "FirstFeltSheet" includes 6 charts which are linked to data (field
equipment measurements "Scans") in columns J, K, L and O utilizing the
offset method
=OFFSET(FirstFeltSheet!$J$1,8,0,COUNTA(FirstFeltSheet!$J:$J)-1,1). I have to
do it this way since column K & L do not always use 512 cells. Chart 5 and 6
are history charts and I have two other sheets I store previous Column J
scans and the other previous Column O scans.

Chart1 = Range "Scan values" in Column J
Chart2 = Range "Scan values" in Column O
Chart3 = Range "Scan values" in Column K
Chart4 = Range "Scan values" in Column L
Chart5 = Range "Scan values" in Column J + the last three scans (previous
three service visits)
Chart6 = Range "Scan values" in Column O + the last three scans (previous
three service visits)



My dilemma is the field measuring equipment I use gives me 512 data units,
which limits me to storing in columns only. I want to keep these values
stored with the other data "FirstFeltData" I collected for that particular
visit(Day).I think the best way is to convert these ranges to a string and
have them pasted to a cell in the row with matching date in the
"FirstFeltData" sheet. So when I use my macro to copy "FirstFeltData" to
"FirstFeltSheet" it will automatically copy and convert the strings back to
columns J, K, L & O. This will guarantee Data and charts (Scans) will match
per service visit. Seeing my macro will select the row where the active
cell in column B (Date) is, could I have the macro copy/convert not only
the active cell scans but also the last three scans for J + O (strings)
below the active cell to columns in the "FirstFeltSheet"?

Dim FD As Worksheet
Set FD = Sheets("FirstFeltData")
Dim FS As Worksheet
Set FS = Sheets("FirstFeltSheet")
Application.ScreenUpdating = False

'Copy Felt data header row and paste to FirstFeltSheet row G
FD.Range("B1:CS1").Copy
FS.Range("G1").PasteSpecial xlPasteValues, Transpose:=True

'Copy Felt data active cell Date and paste to Felt sheet row H
ActiveCell.Range("A1:CR1").Copy
FS.Range("H1").PasteSpecial xlPasteValues, Transpose:=True
Application.CutCopyMode = False

'Copy Columns G and H to correct columns in Felt sheet
FS.Range("H1").Copy
FS.Range("F1").PasteSpecial xlPasteValues
FS.Range("G2:H30").Copy
FS.Range("A3").PasteSpecial xlPasteValues
FS.Range("G31:H63").Copy
FS.Range("C3").PasteSpecial xlPasteValues
FS.Range("G64:H96").Copy
FS.Range("E3").PasteSpecial xlPasteValues
FS.Columns("G:H").Clear
FS.Select
FS.Range("A1").Select
Application.ScreenUpdating = True

Thanks, Kevin Graham