Correct syntax to get cell value

[Yamezz]

Hi. I have the following code, but Excel doesn't know what I'm trying to accomplish. Hopefully a human can see what I'm trying to do and give me the correct syntax. Thanks in advance.

For Each c In Range("J2:P" & LastInputRow)
    cAddr = c.Address
    If c.Value > 0 Then
        ShiftHours = c.Value 'value from the current cell
        EmpCode = cell(c.Row, 4).Value 'value from column 4 in the row of the current cell
        EmpArea = cell(c.Row, 8).Value 'value from column 8 in the row of the current cell
        ShiftDate = cell(1, c.Col).Value 'value from row 1 in the column of the current cell

 

[Michael M]

Try

If c.Value > 0 Then
        ShiftHours = c.Value 'value from the current cell
        EmpCode = Cells(c.Row, 4).Value 
        EmpArea = Cells(c.Row, 8).Value 
        ShiftDate = Cells(1, c.Column).Value 
    End If

 

[mohadin]

hi

For Each c In Range("p2:p" & [COLOR=#333333]LastInputRow[/COLOR])    cAddr = c.Address
    If c.Value > 0 Then
        ShiftHours = c.Value 'value from the current cell
        EmpCode = c.Offset(, 4).Value 'value from column 4 in the row of the current cell
        EmpArea = c.Offset(, 8).Value 'value from column 8 in the row of the current cell
        ShiftDate = c.offset(1).Value 'value from row 1 in the column of the current cell
        End If
        Next

 

[Yamezz]

Thank you guys.
I've made progress, but I am getting J1 as the result for the first c.Address, despite beginning my code with

For Each c In Range("J2:P" & LastInputRow)

The first cell I intend to look in is J2. What am I doing wrong?

 

[Michael M]

Works fine for me......I'd suggest looking outside that snippet of code.....that which you didn't supply to us.!

 

[Yamezz]

Works fine for me......I'd suggest looking outside that snippet of code.....that which you didn't supply to us.!

Sorry, I didn't want to bog you down. Here's the full macro.

Sub ImportHours()

Dim LastInputRow As Long, EmpCode As String, ShiftDate As Date, EmpArea As String
Dim ShiftHours As Double, LastMasterRow As Long, Fname As String, InputWorkbook As Workbook

Application.ScreenUpdating = False

Sheets("Master").Activate
Sheets("Master").Unprotect

    If ActiveSheet.FilterMode = True Then
            ActiveSheet.ShowAllData
    End If

LastInputRow = Cells(Rows.Count, "A").End(xlUp).Row

 Fname = Application.GetOpenFilename
   Set InputWorkbook = Workbooks.Open(Fname)

InputWorkbook.Activate
'Begin looking in column J (first day in pay period)
For Each c In Range("J2:P" & LastInputRow)
    cAddr = c.Address
    If c.Value > 0 Then
        ShiftHours = c.Value
        EmpCode = Cells(c.Row, 4).Value
        EmpArea = Cells(c.Row, 8).Value
        ShiftDate = Cells(1, c.Column).Value
        
        LastMasterRow = Workbooks("BN Payroll.xlsm").Worksheets("Master").UsedRange.Rows.Count
        Workbooks("BN Payroll.xlsm").Sheets("Master").Cells(LastMasterRow + 1, 1) = ShiftDate
        Workbooks("BN Payroll.xlsm").Sheets("Master").Cells(LastMasterRow + 1, 2) = EmpCode
        Workbooks("BN Payroll.xlsm").Sheets("Master").Cells(LastMasterRow + 1, 3) = EmpArea
        Workbooks("BN Payroll.xlsm").Sheets("Master").Cells(LastMasterRow + 1, 4) = ShiftHours
        
    End If
Next

Sheets("Master").Protect

End Sub

 

[Michael M]

I would suggest your problem is that the "lastInputRow" in Col "A" is less than Col "P"

LastInputRow = Cells(Rows.Count, "A").End(xlUp).Row

 

[MARK858]

Possibly the

LastInputRow = Cells(Rows.Count, "A").End(xlUp).Row

line needs moving below the

InputWorkbook.Activate

line?

 

[Michael M]

or changed to

LastInputRow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row

 

[Yamezz]

Thanks guys. As usual, MrExcel comes to the rescue of my bone-headedness.