Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scenariao needing help with. convert range to string and back,
Hello,. I am 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting text string back into a formula | Excel Discussion (Misc queries) | |||
convert a string to range? | Excel Worksheet Functions | |||
How do I change a range name back to the underlying data range? | Excel Worksheet Functions | |||
How do I convert webarchive back to excel | Excel Discussion (Misc queries) | |||
VBA - Convert my variable range array to single cell string | Excel Programming |