Loop through columns

[wtom0412]

Hi all again,

Toadstool has been helping me with this issue and came up with a really cool Formula based solution, but while I was waiting for his replies, I looked at the same problem using VBA and need a bit of help if possible?

Here is the code I have...

Sub Bottle1()
Dim x As Integer

Set wbCopyFrom = ActiveWorkbook
    
      Application.ScreenUpdating = False
      
      Sheet2.Activate
      
      ' Set numrows = number of rows of data.
      NumRows = Range("C1", Range("C1").End(xlDown)).Rows.Count
      
      ' Select Starting cell.
      Range("C1").Select
      
      ' Establish "For" loop to loop "numrows" number of times.
      For x = 1 To NumRows
      
         If ActiveCell = Sheet3.Range("C2") Then ' This is the search criteria in Sheet 3
         
            If ActiveCell.Offset(0, 1) = "1" Then 'The number 1 is the second search criteria and if found will copy the offset text and paste it to Sheet3
            ActiveCell.Offset(0, 2).Copy
            
            Sheet3.Activate
            Range("C8").Select
            
            NextFree = Range("C8:C" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row 'This looks for the next blank cell
            Range("C" & NextFree).Select

            ActiveCell.PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            
            Sheet2.Activate
            
            End If
     
         End If
         
         ' Selects cell down 1 row from active cell and continue the loop.
         ActiveCell.Offset(1, 0).Select
      Next
      
      
      Application.ScreenUpdating = True
      
        
End Sub

This code works well for one column, but I need the above to look through each column (from Column C to Column LA) in Sheet 2 using two search criteria (a value in Row 2 Sheet 3, and a value in Column D in Sheet 2). Once both criteria have been met, it copies a value in Column E Sheet 2 and pastes it to a specific place (see below) on sheet 3.

For example, if we are looping through Column C on Worksheet 2 then the pasted information goes into Column C in Sheet 3, looping through Column D on sheet 2, then paste in Column D on sheet 3 and so on until Column LA.

After it has done all Columns using "1", I need it to repeat the process again and use "2" as the second criteria. I have four second criteria 1, 2, 3 and 4.

I assume that I would write a Macro for each of the second criteria (1,2,3 and 4) and Call each from the macro before it?

Search Criteria "1" would start to paste info from row 8 of the relevant column
Search Criteria "2" would start to paste info from row 16 of the relevant column
Search Criteria "3" would start to paste info from row 19 of the relevant column
Search Criteria "4" would start to paste info from row 12 of the relevant column

I am sure there will be questions, as I am sure I haven't explained myself very well, but any help would be greatly appreciated.

Cheers, TT

 

[DanteAmor]

You could explain it with examples.
Use xl2bb tool to put the data sample.
I would like to see 3 ranges of data:
1. Range of sheet2, to see your original data.
2. Range of sheet3, before the process. I want to see the criteria you have in row 2.
3. Range of sheet3 with the result you want. Obviously showing the data you took from sheet 2.
And it would be great if you could explain a couple of those results.

 

[wtom0412]

You could explain it with examples.
Use xl2bb tool to put the data sample.
I would like to see 3 ranges of data:
1. Range of sheet2, to see your original data.
2. Range of sheet3, before the process. I want to see the criteria you have in row 2.
3. Range of sheet3 with the result you want. Obviously showing the data you took from sheet 2.
And it would be great if you could explain a couple of those results.

Hi DanteAmoor,

Firstly, I am so sorry it took me this long to reply, I had to go out of town on business unexpectedly.

Thank you so much for looking at and considering my issue, but on further reflection, and a lot of amazing advice from ToadStool, I got it to work with the Formula approach.

Cheers, WT