For each c in selection - cell address of each cell

[Yamezz]

My code below finds the correct date in column A of the first cell in my non-contiguous selected range, but not for each subsequent cell. I presume the cause is my use of ActiveCell. What is the correct syntax to loop through each cell in the selection?
Thanks.

For Each c In Selection
AnalysisDate = Range("A" & ActiveCell.Row).Value
MsgBox AnalysisDate
Next c

 

[JoeMo]

Not knowing what your selection looks like, I can only guess, maybe this:

AnalysisDate = Range("A" & c.Row).Value

 

[Damon Ostrander]

Hi Yamezz,

I noticed you never referred to c inside the loop. Did you intend to get the date that resides in each cell of the selection? If so it should be just:

For Each c In Selection
AnalysisDate = c.Value
MsgBox AnalysisDate
Next c

Damon

 

[Yamezz]

Thanks guys. I still get 'random' stuff appearing in my variables. Damon, the date is not inside each cell selected, it's in column A.
Here's my input, code and output. I must still be referring to something wrong. If we start with a simple one, maybe I can work out the others on my own. In the source workbook the Vintage is just referred to by the last 2 digits, so I have code there to add 2000 to it, so "14" becomes "2014" etc. the code does everything I want for the first instance, then errors creep in as you can see from the Vintage output.

Thanks for taking a look at this. I'm tearing my hair out.

Sub Winescan()

Dim c As Range, AnRowNo As Long, AnValue As Double, AnalysisDate As Date, Vintage As Long, Client, Blend, Batch, SubBatch, Analyte As String

For Each c In Selection

    AnValue = c.Value
    
    If c.EntireColumn.Cells(1, 1).Value = "Total Acid" Then
        Analyte = "TA"
        ElseIf c.EntireColumn.Cells(1, 1).Value = "GlucFruc" Then
        Analyte = "GF"
        ElseIf c.EntireColumn.Cells(1, 1).Value = "Ethanol" Then
        Analyte = "Alc"
        ElseIf c.EntireColumn.Cells(1, 1).Value = "Malic Acid" Then
        Analyte = "Malo"
        Else: Analyte = c.EntireColumn.Cells(1, 1).Value
    End If
        
    AnalysisDate = Range("A" & c.Row).Value 'load value of column A in row of cell in variable AnalysisDate
    Vintage = Range("D" & c.Row).Value
    Vintage = Vintage + 2000
    Client = Range("E" & c.Row).Value
    Blend = Range("F" & c.Row).Value
    Batch = Range("G" & c.Row).Value
    SubBatch = Range("H" & c.Row).Value
    Workbooks("Cellar.xlsm").Sheets("Analysis").Activate
    Range("A65536").End(xlUp).Offset(1, 0).Select 'select next available cell in column A
    AnRowNo = ActiveCell.Row
    Workbooks("Cellar.xlsm").Sheets("Analysis").Range("A" & AnRowNo) = Day(AnalysisDate)
    Workbooks("Cellar.xlsm").Sheets("Analysis").Range("B" & AnRowNo) = Format(AnalysisDate, "mmm")
    Workbooks("Cellar.xlsm").Sheets("Analysis").Range("C" & AnRowNo) = Format(AnalysisDate, "yy")
    Workbooks("Cellar.xlsm").Sheets("Analysis").Range("D" & AnRowNo) = Vintage
    Workbooks("Cellar.xlsm").Sheets("Analysis").Range("E" & AnRowNo) = Client
    Workbooks("Cellar.xlsm").Sheets("Analysis").Range("F" & AnRowNo) = Blend
    Workbooks("Cellar.xlsm").Sheets("Analysis").Range("G" & AnRowNo) = Batch
    Workbooks("Cellar.xlsm").Sheets("Analysis").Range("H" & AnRowNo) = Analyte
    Workbooks("Cellar.xlsm").Sheets("Analysis").Range("I" & AnRowNo) = AnValue
    Workbooks("Cellar.xlsm").Sheets("Analysis").Range("L" & AnRowNo) = SubBatch
    
    Range("M" & AnRowNo - 1, "M" & AnRowNo).FillDown

Next c

End Sub

 

[Rick Rothstein]

Thanks guys. I still get 'random' stuff appearing in my variables.

So far you have only shown us non-working code for some mysteriously created non-contiguous selection and an incorrect out for it, but we really do not yet have a good understanding of what you are attempting to do. For the 30 rows of sample data you posted in Message #4, why don't you tell us the conditions you want to restrict the code to and then show us what the correct output should be... perhaps from that, we might be able to figure out what you are trying to ultimately do and provide you with a path to it.

 

[Yamezz]

Sorry Rick. The input is the 'output' of a piece of lab equipment. The results of interest are manually selected. The code (which works for the first instance) copies the values across to another workbook. I don't want all the data, just the ones selected, but for the ones selected I need to copy the date of the analysis, the details of the sample and the chemical/test the value actually relates to. So for the first cell, the code looks at row 1 of the column the cell is in [c.EntireColumn.Cells(1, 1).Value] to see that the analysis is a pH test, then looks at columns A in the cell's row for the date, then columns D though H for the details of the sample. So the output should be:

I am most baffled by the presence of DO and Cold Stab in the failed output. Not only do these values not apply to the Notes column, these values do not even exist in the input workbook. They are present in the output workbook though, further up in the Analysis column.

 

[Rick Rothstein]

Sorry Rick. The input is the 'output' of a piece of lab equipment. The results of interest are manually selected.

I am still a little unclear on some things, but perhaps the following will give you an idea. The following single code line will copy the entire row of data for every selected cell on the active sheet (assuming it is not Sheet2) over to Sheet2...

Intersect(ActiveSheet.UsedRange, Selection.EntireRow).Copy Sheets("Sheet2").Range("A1")

After you do that, you could then delete the columns on Sheet2 that you do not want (iterate the columns backwards from the highest numbered column to the lowest).

 

[Yamezz]

Thank you, but I'm not sure that will fit with the layout I have in the Destination worksheet.

I'll try to better explain what the code should be doing. The source data is the output of a lab machine. It gives readings for all of the parameters at once, regardless of whether they are needed or not. My manual selection of the data is highlighting the specific values I'm interested in transferring to the destination worksheet, but as I've only selected the values, not which parameter or which sample the value belongs to, I'm using the code to find that information out and transfer it to the destination worksheet. So the first cell I selected in the example is I20, which has a value of 3.65. The code now needs to look at the top of the column (in this case I1) to determine the parameter that has been measured as 3.65 (in this case "pH"). It the looks at the first column of the row belonging to the value (A20) to get the date the sample was analysed. From columns D through H it gets the sample's details (in this case D20:H20). The whole lot is copied to the destination worksheet (while adding 2000 to the Vintage, so it becomes the year 20XX). The process is then supposed to repeat for the next cell in the selection (J22). The parameter comes from the top of the cell's column (in this case J1, which is "Total Acid"); the date from column A in the cell's row (A22) and the details from D22:H40 and so on.

Something is going wrong when moving to the next c in selection.

 

[Yamezz]

Turns out it's another boneheaded error by myself. A simple return to activate the source workbook at the end of the loop fixed things.