For each c, find column

[jrepko11]

Hello,

I am going through a range of cells down column A, then going across the row of that cell to determine the value inputed by user. If c.value > 0, I will copy and paste specific cells to another sheet. See code portion below:

Dim TargetCell As Range
Set TargetCell = Range("A6")

Dim LastCell As Range
Set LastCell = Range("A65536").End(xlUp).Offset(-1, 0)

Dim NameCell As Range
Set NameCell = Range("C2")

Dim SrvGrpCell As Range
Set SrvGrpCell = Range("L2")

For Each cat In Range(TargetCell, LastCell)
For Each c In Range(cat.Offset(0, 1), cat.Offset(0, 7))

If c.Value > 0 Then

'This just creates a separate worksheet if it's not already created. Don't need help here

If WorksheetExists("Data") = False Then
'Create data sheet with headers
ActiveWorkbook.Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "Data"
Sheets("Data").Select
ActiveCell.FormulaR1C1 = "Name"
Range("B1").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "Category"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Hours"
Range("C2").Select
Else
End If

NameCell.Copy
Worksheets("Data").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
SrvGrpCell.Copy
Worksheets("Data").Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
cat.Copy
Worksheets("Data").Range("C65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
Next c
Next cat
.....

Column.c.Copy?

Here is where I have an issue. The three individual cells that are being copied above (NamceCell, SrvGrpCell, and cat) are all defined and copying fine. I now want to copy the cell that is in row 5 of the same column (which changes with each c). Is there any way to copy "Column.c" or something of the sort?

Any help is appreciated, thanks!!!!

 

[jrepko11]

Can anyone help? My other thought was to have an "offset of an offset"?

ex:

c.Offset(.End(xlUp).Offset(-5, 0), 0).Copy

I need the cell in the 5th row of the sheet, in column c (which changes with each loop)

Anything is appreciated, thanks!

 

[Worf]

Hi

Are you aware of the syntax shown below? Can be helpful.

Sub Info()

Sheets("Sheet1").Cells(5, 3).Value = 10
' attributes 10 to cell C5 => fifth row, third column
' alter the row and column index as you like

End Sub