Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Convert my variable range array to single cell string
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Convert my variable range array to single cell string
Assume you had a range J9:J11
J9: 1 J10: 2 J11: 3 What would you expect to see in the single cell? -- Regards, Tom Ogilvy Kevin G wrote in message news:LZFRb.19360$P51.3159@clgrps12... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Convert my variable range array to single cell string
1, 2, 3, etc
I have 512 cells each with 3 numerals. Thanks, Kevin Graham "Tom Ogilvy" wrote in message ... Assume you had a range J9:J11 J9: 1 J10: 2 J11: 3 What would you expect to see in the single cell? -- Regards, Tom Ogilvy Kevin G wrote in message news:LZFRb.19360$P51.3159@clgrps12... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Convert my variable range array to single cell string
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 -- Regards, Tom Ogilvy Kevin G wrote in message news:bKGRb.19363$P51.11833@clgrps12... 1, 2, 3, etc I have 512 cells each with 3 numerals. Thanks, Kevin Graham "Tom Ogilvy" wrote in message ... Assume you had a range J9:J11 J9: 1 J10: 2 J11: 3 What would you expect to see in the single cell? -- Regards, Tom Ogilvy Kevin G wrote in message news:LZFRb.19360$P51.3159@clgrps12... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Convert my variable range array to single cell string
Thanks Tom. You sure make it look easy.
How would you return to J9:J520 range? "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy Kevin G wrote in message news:bKGRb.19363$P51.11833@clgrps12... 1, 2, 3, etc I have 512 cells each with 3 numerals. Thanks, Kevin Graham "Tom Ogilvy" wrote in message ... Assume you had a range J9:J11 J9: 1 J10: 2 J11: 3 What would you expect to see in the single cell? -- Regards, Tom Ogilvy Kevin G wrote in message news:LZFRb.19360$P51.3159@clgrps12... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Convert my variable range array to single cell string
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 "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy Kevin G wrote in message news:bKGRb.19363$P51.11833@clgrps12... 1, 2, 3, etc I have 512 cells each with 3 numerals. Thanks, Kevin Graham "Tom Ogilvy" wrote in message ... Assume you had a range J9:J11 J9: 1 J10: 2 J11: 3 What would you expect to see in the single cell? -- Regards, Tom Ogilvy Kevin G wrote in message news:LZFRb.19360$P51.3159@clgrps12... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Convert my variable range array to single cell string
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(FirstFeltSh eet!$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 "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy Kevin G wrote in message news:bKGRb.19363$P51.11833@clgrps12... 1, 2, 3, etc I have 512 cells each with 3 numerals. Thanks, Kevin Graham "Tom Ogilvy" wrote in message ... Assume you had a range J9:J11 J9: 1 J10: 2 J11: 3 What would you expect to see in the single cell? -- Regards, Tom Ogilvy Kevin G wrote in message news:LZFRb.19360$P51.3159@clgrps12... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert YYYY, MM, and DD as a single number string | Excel Discussion (Misc queries) | |||
Finding max array value of variable cell range | Excel Discussion (Misc queries) | |||
Passing string as array variable (Pivot VBA) | Excel Discussion (Misc queries) | |||
how do I make single character as a variable in an array | Excel Worksheet Functions | |||
Problem trying to us a range variable as an array variable | Excel Programming |