Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste link and more
Hi there. I have this piece of code working but still not doing what I need to.
I have column A with certain values highlighted blue. Once I find the color cell, I go thru the columns for that row looking for all the blue colored cells. Then I'll copy the value to another column (E) and add some information. So far so good... Sub Select_Results() Dim te As Long 'total elements Dim LastEl As Long 'Last Element Row Dim lastC As String 'Last column letter Dim i As Long Dim oRng As Range Dim DestCol As Long 'Destination column Dim DestRow As Long 'Destination row Dim oCol As Long 'zero column myfilename = Range("H3").Value Sheets("Results " & myfilename & " data").Select te = Range("F6").Value LastEl = (te + 15) lastC = Range("I100").Value DestCol = Columns("E").Column oCol = Columns("E").Column DestRow = LastEl + 6 oRow = LastEl + 6 With Worksheets("Results " & myfilename & " data").Range("B16").Select For i = 1 To LastEl On Error Resume Next Set oRng = Cells.Find(What:=ActiveCell.Offset(0, 0).Value, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows) If Not oRng Is Nothing Then If ActiveCell.Offset(0, -1).Interior.ColorIndex = 8 Then Cells(DestRow, DestCol).Value = ActiveCell.Offset(0, -1).Value For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC & i + 15) If c.Interior.ColorIndex = 8 Then 'ActiveSheet.Paste Link:=True Cells(DestRow, DestCol + 1).Value = c.Value Cells(DestRow, DestCol + 2).Value = "mg/L" Cells(DestRow, DestCol + 3).Value = "1" Cells(DestRow, DestCol + 5).Value = "EPA 200.8" Cells(DestRow, DestCol + 6).Value = "Date" DestCol = DestCol + 8 End If Next If DestRow < "" Then DestRow = DestRow + 1 Else DestRow = DestRow End If End If End If DestCol = oCol ActiveCell.Offset(1, 0).Select Next i End With Range("H2").Select End Sub Here is the challenge: Once I find the c.values, I need to Paste link those values to the new locations (column e +1, +8, etc) , set the interior color index to 8, and format the numbers to 3 significant digits I need to repeat the first value found (column A) for each c.value found: if there is a second value for Germanium, I start in DesCol + 8 the resuls look like this From Col A: c.value: Germanium < 5 mg/L 1 EPA 200.8 Date Lead 499.91361 mg/L 1 EPA 200.8 Date mg/L, 1, EPA 200.8 and date I need to find this information matching the values from column A (Germanium, Lead) in another sheet (3). Is all this possible? Any help I would really really appreciate. Oh Boy is going to be a long night.... Thanks, -- gaba :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
'paste special', 'paste link' formatting transfer | Excel Discussion (Misc queries) | |||
PASTE LINK option not available when I select PASTE SPECIAL to link an image in Excel to a Word document. | Links and Linking in Excel | |||
Formatting not retained using paste all and paste link | Excel Worksheet Functions | |||
Paste Link enters a 0 into the cell where I paste. How do I elemin | Excel Discussion (Misc queries) | |||
Paste Link is greyed out during Paste Special | Excel Programming |