Counting Variable Length of Rows in a Named Range

[jandre]

I need help.

I have gotten to where I am in my excel programming by perusing th
help file. I have a command button that i want to combine the value o
itself with the value of another cell. Basically i want:

If H10>L10 then --- Row 10 is where my data starts, not Row 1
L10 = L10 + N10
end if

I have this in a For Next loop. I also defined a name of the rang
(H10, defined name is "Work_Total_Exp" and range = " ='Detai
Sheet'!$H$11:$H$236 ") and want the For Next loop to count the numbe
of rows (variable) and run the above IF THEN statement thru the tota
number of rows in Work_Total_Exp. I hope this makes sense...

the problem i'm coming into is the Work_Total_Exp range right now ha
225 rows, yet my code returns the value of 14. basically its countin
from Row 1 down to Row 14. i cant figure out why its starting at Row
and stopping at Row 14


Code
-------------------
Sub Button1_Click() 'Updates Work Complete from Previous Application

Set Work_Total_Exp = ActiveWorkbook.Names
Set wks = Worksheets("Detail Sheet")
Dim row, totalrows As Integer
Dim colExpTotal, colRenTotal, colExpWorkPrev, colRenWorkPrev, colExpWorkCur, colRenWorkCur As Integer
Dim colExpPercentPrev, colRenPercentPrev, colExpPercentCur, colRenPercentCur As Integer
totalrows = Work_Total_Exp.Count
colExpTotal = 8 'column H - hidden
colExpPercentPrev = 9 'column I - hidden
colExpWorkPrev = 12 'column L
colExpWorkCur = 14 'column N
colExpPercentCur = 20 'column T
'BELOW ADDS THE EXPANSION WORK COMPLETED FROM PREVIOUS APPLICATION
For row = 1 To totalrows
If wks.Cells(row, colExpTotal).Value > wks.Cells(row, colExpWorkPrev).Value Then
wks.Cells(row, colExpWorkPrev) = wks.Cells(row, colExpWorkPrev) + wks.Cells(row, colExpWorkCur)
wks.Cells(row, colExpPercentPrev).Value = wks.Cells(row, colExpPercentCur)
End If
Next
End Su
-------------------


please help

thanks!

-Jami

 

[Bernie Deitrick]

Jamie,

Delete this line:
Set Work_Total_Exp = ActiveWorkbook.Names

and change the line
totalrows = Work_Total_Exp.Count

to
totalrows =
Range("Work_Total_Exp").Cells(Range("Work_Total_Exp").Cells.Count).Row

and then change
For row = 1 To totalrows

to
For row = 10 To totalrows

Those are the most obvious errors: I haven't checked further into your
logic.

HTH,
Bernie
MS Excel MVP

I need help.

I have gotten to where I am in my excel programming by perusing the
help file. I have a command button that i want to combine the value of
itself with the value of another cell. Basically i want:

If H10>L10 then --- Row 10 is where my data starts, not Row 1
L10 = L10 + N10
end if

I have this in a For Next loop. I also defined a name of the range
(H10, defined name is "Work_Total_Exp" and range = " ='Detail
Sheet'!$H$11:$H$236 ") and want the For Next loop to count the number
of rows (variable) and run the above IF THEN statement thru the total
number of rows in Work_Total_Exp. I hope this makes sense...

the problem i'm coming into is the Work_Total_Exp range right now has
225 rows, yet my code returns the value of 14. basically its counting
from Row 1 down to Row 14. i cant figure out why its starting at Row 1
and stopping at Row 14


Code:
--------------------
Sub Button1_Click() 'Updates Work Complete from Previous Application

Set Work_Total_Exp = ActiveWorkbook.Names
Set wks = Worksheets("Detail Sheet")
Dim row, totalrows As Integer
Dim colExpTotal, colRenTotal, colExpWorkPrev, colRenWorkPrev,

colExpWorkCur, colRenWorkCur As Integer