variable in macro changes when it is used in worksheet

hgeek23

New Member
Joined
Jul 16, 2010
Messages
14
Hi, im using a code to create dynamic named ranges. the code is as follows:

Code:
Sub tryone()
'
'
' Create Dynamic named ranges
'

'

Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long

Dim myName As String, Start As String

Dim wsName As String
' set the row number where headings are held as a constant
' change this to the row number required if not row 1
Const Rowno = 5

' set the Offset as the number of rows below Rowno, where the
' data begins
Const Offset = 5

' set the starting column for the data, in this case 1
' change if the data does not start in column A
Const Colno = 1

TK = Rowno + Offset

' On Error GoTo CreateNames_Error

Set wb = ActiveWorkbook
Set ws = ActiveSheet

' count the number of columns used in the row designated to
' have the header names

lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(TK, Colno).End(xlDown).Row
Start = Cells(Rowno, Colno).Address


'replace blanks in worksheet names with underscore for the purposes of adding range names
wsName = ws.Name
wsName = Replace(wsName, " ", "_")

wb.Names.Add Name:=wsName & "_lcol", RefersTo:="=COUNT($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:=wsName & "_lrow", RefersToR1C1:="=COUNT(C" & Colno & ")"
wb.Names.Add Name:=wsName & "_myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & wsName & "_lrow," & wsName & "_lcol)"

For i = Colno To lcol
' if a column header contains space or other invalid character etc, replace with underscore
myName = Replace(Cells(Rowno, i).Value, "/", "_")
myName = Replace(myName, " ", "_")
myName = Replace(myName, "&", "_")
myName = Replace(myName, "(", "_")
myName = Replace(myName, ")", "_")
myName = Replace(myName, "?", "_")
myName = Replace(myName, "\", "_")

If myName = "" Then
' if column header is blank, warn the user and stop the macro at that point
' names will only be created for those cells with text in them.
MsgBox "Missing Name in column " & i & vbCrLf _
& "Please Enter a Name and run macro again"
Exit Sub
End If
wb.Names.Add Name:=wsName & "_" & myName, RefersToR1C1:= _
"=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & "," & wsName & "_lrow)"

nexti:
Next i

On Error GoTo 0
MsgBox "All dynamic Named ranges have been created"
Exit Sub

CreateNames_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CreateNames"

End Sub

At the beginning of the code, lrow is a variable that holds the number of rows i need later on in the index function (replicated here):

Code:
wb.Names.Add Name:=wsName & "_" & myName, RefersToR1C1:= _
"=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & "," & wsName & "_lrow)"</pre>

My problem is that lrow in the macro equals 75, but when it is used in the workbook it equals 68 for some reason, so my range is stopping 7 rows short!

Why is this the case? how do i change it?

This is the workbook:
http://www.mediafire.com/file/4e0mg35hzc6cp9r/sample.xlsm
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,109
Messages
5,857,441
Members
431,879
Latest member
KiwDaWabbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top