Hi,

I have this code:

Sub LastRow()

Dim a As Integer

For a = 12 To 100
''' If Range("A" & a) = "" Then
''' LastR = a - 1
''' Exit For

''' Else
If Left(Range("A" & a), 5) = "Total" Then
LastR = a - 1
Exit For
Else

End If
Next a

End Sub

at the moment this code finds the last row, what I would like the code
to do as well if there are empty rows above "Total" then minus them
until the code finds the row with data in it.

Thanks

Hi Neil -

Sub LastRow()
Dim a As Integer

For a = 12 To 100
If Range("A" & a) = "" Or _
Left(Range("A" & a), 5) = "Total" Then
LastR = a - 1
Exit For
End If
Next a

MsgBox "Last Row = " & LastR
End Sub

----
Jay


" wrote:

Hi,

I have this code:

Sub LastRow()

Dim a As Integer

For a = 12 To 100
''' If Range("A" & a) = "" Then
''' LastR = a - 1
''' Exit For

''' Else
If Left(Range("A" & a), 5) = "Total" Then
LastR = a - 1
Exit For
Else

End If
Next a

End Sub

at the moment this code finds the last row, what I would like the code
to do as well if there are empty rows above "Total" then minus them
until the code finds the row with data in it.

Thanks

On 5 Dec, 11:06, Jay wrote:
Hi Neil -

Sub LastRow()
Dim a As Integer

For a = 12 To 100
If Range("A" & a) = "" Or _
Left(Range("A" & a), 5) = "Total" Then
LastR = a - 1
Exit For
End If
Next a

MsgBox "Last Row = " & LastR
End Sub

----
Jay



" wrote:
Hi,

I have this code:

Sub LastRow()

Dim a As Integer

For a = 12 To 100
''' If Range("A" & a) = "" Then
''' LastR = a - 1
''' Exit For

''' Else
If Left(Range("A" & a), 5) = "Total" Then
LastR = a - 1
Exit For
Else

End If
Next a

End Sub

at the moment this code finds the last row, what I would like the code
to do as well if there are empty rows above "Total" then minus them
until the code finds the row with data in it.

Thanks- Hide quoted text -

- Show quoted text -

Hi Jay,

Thanks for the response it works perfectly but it looks as though it
does not work with the rest of the coding I have and trips up and
gives me an error message 9 subscript out of range when it goes
through my array code - any ideas how I can make this code work??

Dim TaskData() As Variant

Call LastRow

TaskData() = Sheets("New Time Sheet").Range("A12:L" & LastR).Value

'TaskData() = Sheets("New Time Sheet").Range("A12:L12").Value

Dim Tempdata() As Variant

c = 0
For a = 1 To (LastR - 11) Step 1
'For a = 1 To 21 Step 1
If TaskData(a, 11) = "" Then

Else
c = c + 1
ReDim Preserve Tempdata(12, c)
For d = 1 To 12 Step 1
Tempdata(d, c) = TaskData(a, d)
Next d
End If
Next a

'-----invert array-----

ReDim TaskData(c, 12)
For a = 1 To c Step 1
For b = 1 To 12 Step 1
TaskData(a, b) = Tempdata(b, a)
Next b
Next a

Thanks Val

Hi Val -

Your code seems to run fine; I can't reproduce the error. On what line does
the error occur and what are the values of a, b, c, and d at the time?

Jay

" wrote:

On 5 Dec, 11:06, Jay wrote:
Hi Neil -

Sub LastRow()
Dim a As Integer

For a = 12 To 100
If Range("A" & a) = "" Or _
Left(Range("A" & a), 5) = "Total" Then
LastR = a - 1
Exit For
End If
Next a

MsgBox "Last Row = " & LastR
End Sub

----
Jay



" wrote:
Hi,

I have this code:

Sub LastRow()

Dim a As Integer

For a = 12 To 100
''' If Range("A" & a) = "" Then
''' LastR = a - 1
''' Exit For

''' Else
If Left(Range("A" & a), 5) = "Total" Then
LastR = a - 1
Exit For
Else

End If
Next a

End Sub

at the moment this code finds the last row, what I would like the code
to do as well if there are empty rows above "Total" then minus them
until the code finds the row with data in it.

Thanks- Hide quoted text -

- Show quoted text -

Hi Jay,

Thanks for the response it works perfectly but it looks as though it
does not work with the rest of the coding I have and trips up and
gives me an error message 9 subscript out of range when it goes
through my array code - any ideas how I can make this code work??

Dim TaskData() As Variant

Call LastRow

TaskData() = Sheets("New Time Sheet").Range("A12:L" & LastR).Value

'TaskData() = Sheets("New Time Sheet").Range("A12:L12").Value

Dim Tempdata() As Variant

c = 0
For a = 1 To (LastR - 11) Step 1
'For a = 1 To 21 Step 1
If TaskData(a, 11) = "" Then

Else
c = c + 1
ReDim Preserve Tempdata(12, c)
For d = 1 To 12 Step 1
Tempdata(d, c) = TaskData(a, d)
Next d
End If
Next a

'-----invert array-----

ReDim TaskData(c, 12)
For a = 1 To c Step 1
For b = 1 To 12 Step 1
TaskData(a, b) = Tempdata(b, a)
Next b
Next a

Thanks Val

On 5 Dec, 12:13, Jay wrote:
Hi Val -

Your code seems to run fine; I can't reproduce the error. On what line does
the error occur and what are the values of a, b, c, and d at the time?

Jay



" wrote:
On 5 Dec, 11:06, Jay wrote:
Hi Neil -

Sub LastRow()
Dim a As Integer

For a = 12 To 100
If Range("A" & a) = "" Or _
Left(Range("A" & a), 5) = "Total" Then
LastR = a - 1
Exit For
End If
Next a

MsgBox "Last Row = " & LastR
End Sub

----
Jay

" wrote:
Hi,

I have this code:

Sub LastRow()

Dim a As Integer

For a = 12 To 100
''' If Range("A" & a) = "" Then
''' LastR = a - 1
''' Exit For

''' Else
If Left(Range("A" & a), 5) = "Total" Then
LastR = a - 1
Exit For
Else

End If
Next a

End Sub

at the moment this code finds the last row, what I would like the code
to do as well if there are empty rows above "Total" then minus them
until the code finds the row with data in it.

Thanks- Hide quoted text -

- Show quoted text -

Hi Jay,

Thanks for the response it works perfectly but it looks as though it
does not work with the rest of the coding I have and trips up and
gives me an error message 9 subscript out of range when it goes
through my array code - any ideas how I can make this code work??

Dim TaskData() As Variant

Call LastRow

TaskData() = Sheets("New Time Sheet").Range("A12:L" & LastR).Value

'TaskData() = Sheets("New Time Sheet").Range("A12:L12").Value

Dim Tempdata() As Variant

c = 0
For a = 1 To (LastR - 11) Step 1
'For a = 1 To 21 Step 1
If TaskData(a, 11) = "" Then

Else
c = c + 1
ReDim Preserve Tempdata(12, c)
For d = 1 To 12 Step 1
Tempdata(d, c) = TaskData(a, d)
Next d
End If
Next a

'-----invert array-----

ReDim TaskData(c, 12)
For a = 1 To c Step 1
For b = 1 To 12 Step 1
TaskData(a, b) = Tempdata(b, a)
Next b
Next a

Thanks Val- Hide quoted text -

- Show quoted text -

Jay,

Many thanks for replying it seems that the Array problem is fine after
I stepped through the entire Project. The problem that I am
encountering is when there is no blank rows between "Total" and the
data and this is where it is tripping up:

a should be = 25
b should be = 24

But it is saying that b is = 25

here is the rest of the code

Dim b As Integer

For a = 12 To 100
If Range("A" & a) = "" Then
b = a - 1
Exit For
Else
End If
Next a

'Stop

If b = 12 Then b = 13

For a = 1 To (b - 11) Step 1

'For a = 1 To (b - 11) Step 1

rs.addnew
rs("WKCOMDATE") = week
rs("PROJECTCODE") = HoldingTableData(a, 1)
rs("WORKCODE") = HoldingTableData(a, 2)
rs("MON") = HoldingTableData(a, 3)
rs("TUE") = HoldingTableData(a, 4)
rs("WED") = HoldingTableData(a, 5)
rs("THU") = HoldingTableData(a, 6)
rs("FRI") = HoldingTableData(a, 7)
rs("SAT") = HoldingTableData(a, 8)
rs("SUN") = HoldingTableData(a, 9)
rs("TOTALHRS") = HoldingTableData(a, 10)
rs("TASKCATEGORY") = HoldingTableData(a, 11)
rs("PARTNUMBER") = HoldingTableData(a, 12)
'''''rs("REPORTINGMONTH") = MonthName(Month(Date))
rs("EMPLOYEESNAME") = who
rs("DATESUBMITTED") = Date
'rs("DEPARTMENT") = dept
'rs("DATESUB") = Now()
Next a
rs.update

rs.Close
cnn1.Close
Set cnn1 = Nothing
Set rs = Nothing

'MsgBox (msg)

End Sub

Val -

Try changing the line:
If Range ("A" & a) = "" Then
To:
If Range("A" & a) = "" Or Left(Range("A" & a), 5) = "Total" Then

That should generate the correct value for 'b' when there are no blank data
rows.

---
Jay




" wrote:

On 5 Dec, 12:13, Jay wrote:
Hi Val -

Your code seems to run fine; I can't reproduce the error. On what line does
the error occur and what are the values of a, b, c, and d at the time?

Jay



" wrote:
On 5 Dec, 11:06, Jay wrote:
Hi Neil -

Sub LastRow()
Dim a As Integer

For a = 12 To 100
If Range("A" & a) = "" Or _
Left(Range("A" & a), 5) = "Total" Then
LastR = a - 1
Exit For
End If
Next a

MsgBox "Last Row = " & LastR
End Sub

----
Jay

" wrote:
Hi,

I have this code:

Sub LastRow()

Dim a As Integer

For a = 12 To 100
''' If Range("A" & a) = "" Then
''' LastR = a - 1
''' Exit For

''' Else
If Left(Range("A" & a), 5) = "Total" Then
LastR = a - 1
Exit For
Else

End If
Next a

End Sub

at the moment this code finds the last row, what I would like the code
to do as well if there are empty rows above "Total" then minus them
until the code finds the row with data in it.

Thanks- Hide quoted text -

- Show quoted text -

Hi Jay,

Thanks for the response it works perfectly but it looks as though it
does not work with the rest of the coding I have and trips up and
gives me an error message 9 subscript out of range when it goes
through my array code - any ideas how I can make this code work??

Dim TaskData() As Variant

Call LastRow

TaskData() = Sheets("New Time Sheet").Range("A12:L" & LastR).Value

'TaskData() = Sheets("New Time Sheet").Range("A12:L12").Value

Dim Tempdata() As Variant

c = 0
For a = 1 To (LastR - 11) Step 1
'For a = 1 To 21 Step 1
If TaskData(a, 11) = "" Then

Else
c = c + 1
ReDim Preserve Tempdata(12, c)
For d = 1 To 12 Step 1
Tempdata(d, c) = TaskData(a, d)
Next d
End If
Next a

'-----invert array-----

ReDim TaskData(c, 12)
For a = 1 To c Step 1
For b = 1 To 12 Step 1
TaskData(a, b) = Tempdata(b, a)
Next b
Next a

Thanks Val- Hide quoted text -

- Show quoted text -

Jay,

Many thanks for replying it seems that the Array problem is fine after
I stepped through the entire Project. The problem that I am
encountering is when there is no blank rows between "Total" and the
data and this is where it is tripping up:

a should be = 25
b should be = 24

But it is saying that b is = 25

here is the rest of the code

Dim b As Integer

For a = 12 To 100
If Range("A" & a) = "" Then
b = a - 1
Exit For
Else
End If
Next a

'Stop

If b = 12 Then b = 13

For a = 1 To (b - 11) Step 1

'For a = 1 To (b - 11) Step 1

rs.addnew
rs("WKCOMDATE") = week
rs("PROJECTCODE") = HoldingTableData(a, 1)
rs("WORKCODE") = HoldingTableData(a, 2)
rs("MON") = HoldingTableData(a, 3)
rs("TUE") = HoldingTableData(a, 4)
rs("WED") = HoldingTableData(a, 5)
rs("THU") = HoldingTableData(a, 6)
rs("FRI") = HoldingTableData(a, 7)
rs("SAT") = HoldingTableData(a, 8)
rs("SUN") = HoldingTableData(a, 9)
rs("TOTALHRS") = HoldingTableData(a, 10)
rs("TASKCATEGORY") = HoldingTableData(a, 11)
rs("PARTNUMBER") = HoldingTableData(a, 12)
'''''rs("REPORTINGMONTH") = MonthName(Month(Date))
rs("EMPLOYEESNAME") = who
rs("DATESUBMITTED") = Date
'rs("DEPARTMENT") = dept
'rs("DATESUB") = Now()
Next a
rs.update

rs.Close
cnn1.Close
Set cnn1 = Nothing
Set rs = Nothing

'MsgBox (msg)

End Sub