I am trying to do the following: Search for the cell containing "Hours".
Once I find it, drop down 2 more rows and read 48 columns of data, then drop
down another row and read another 48 columns of data. I think I am mixing
Cell and Range stuff together but I am not getting any where. I get datatype
mismatch errors on the For loop

Thanks


'Assumes data starts in column C
Set rRng = ActiveSheet.UsedRange.Columns("C:C")

For i = 1 To rRng.Rows.Count
If ActiveSheet.Cells(i, 3).Value = "Hours" Then
Set rngTemp = ActiveSheet.Cells(i, 3)

Exit For
End If
Next

If Not rngTemp Is Nothing Then
tmpStr = "C" & rngTemp.Row + 2 & ":AY" & (rngTemp.Row + 2)
Set rRng = wsh.Range(tmpStr)
i = 1
For Each c In wsh.Range(tmpStr).Cells
sMonth = "Month" & i & "="
If i < 48 Then
sSQL = sSQL & sMonth & c.Value & ", "
Else
sSQL = sSQL & sMonth & c.Value & ", "
End If
i = i + 1
Next
MsgBox sSQL
End If

You had a few little probelms. I modified the code to make it easier to
understand.

Sub findhours()

With ActiveSheet
Set HourCell = .Columns("C").Find(what:="Hours", LookIn:=xlValues, _
lookat:=xlWhole)
If Not HourCell Is Nothing Then
tmpStr = "C" & HourCell.Row + 2 & ":AY" & (HourCell.Row + 2)
Set rRng = .Range(tmpStr)

i = 1
sSQL = ""
For Each c In rRng
sMonth = "Month" & i & "="
If i < 48 Then
sSQL = sSQL & sMonth & c.Value & ", "
Else
sSQL = sSQL & sMonth & c.Value & ", "
End If
i = i + 1
Next
MsgBox sSQL
End If


End Sub


"chemicals" wrote:

I am trying to do the following: Search for the cell containing "Hours".
Once I find it, drop down 2 more rows and read 48 columns of data, then drop
down another row and read another 48 columns of data. I think I am mixing
Cell and Range stuff together but I am not getting any where. I get datatype
mismatch errors on the For loop

Thanks


'Assumes data starts in column C
Set rRng = ActiveSheet.UsedRange.Columns("C:C")

For i = 1 To rRng.Rows.Count
If ActiveSheet.Cells(i, 3).Value = "Hours" Then
Set rngTemp = ActiveSheet.Cells(i, 3)

Exit For
End If
Next

If Not rngTemp Is Nothing Then
tmpStr = "C" & rngTemp.Row + 2 & ":AY" & (rngTemp.Row + 2)
Set rRng = wsh.Range(tmpStr)
i = 1
For Each c In wsh.Range(tmpStr).Cells
sMonth = "Month" & i & "="
If i < 48 Then
sSQL = sSQL & sMonth & c.Value & ", "
Else
sSQL = sSQL & sMonth & c.Value & ", "
End If
i = i + 1
Next
MsgBox sSQL
End If

Thanks Joel it works great...!

"Joel" wrote:

You had a few little probelms. I modified the code to make it easier to
understand.

Sub findhours()

With ActiveSheet
Set HourCell = .Columns("C").Find(what:="Hours", LookIn:=xlValues, _
lookat:=xlWhole)
If Not HourCell Is Nothing Then
tmpStr = "C" & HourCell.Row + 2 & ":AY" & (HourCell.Row + 2)
Set rRng = .Range(tmpStr)

i = 1
sSQL = ""
For Each c In rRng
sMonth = "Month" & i & "="
If i < 48 Then
sSQL = sSQL & sMonth & c.Value & ", "
Else
sSQL = sSQL & sMonth & c.Value & ", "
End If
i = i + 1
Next
MsgBox sSQL
End If


End Sub


"chemicals" wrote:

I am trying to do the following: Search for the cell containing "Hours".
Once I find it, drop down 2 more rows and read 48 columns of data, then drop
down another row and read another 48 columns of data. I think I am mixing
Cell and Range stuff together but I am not getting any where. I get datatype
mismatch errors on the For loop

Thanks


'Assumes data starts in column C
Set rRng = ActiveSheet.UsedRange.Columns("C:C")

For i = 1 To rRng.Rows.Count
If ActiveSheet.Cells(i, 3).Value = "Hours" Then
Set rngTemp = ActiveSheet.Cells(i, 3)

Exit For
End If
Next

If Not rngTemp Is Nothing Then
tmpStr = "C" & rngTemp.Row + 2 & ":AY" & (rngTemp.Row + 2)
Set rRng = wsh.Range(tmpStr)
i = 1
For Each c In wsh.Range(tmpStr).Cells
sMonth = "Month" & i & "="
If i < 48 Then
sSQL = sSQL & sMonth & c.Value & ", "
Else
sSQL = sSQL & sMonth & c.Value & ", "
End If
i = i + 1
Next
MsgBox sSQL
End If