Dynamic LastCell
 

Hello NG,

Can someone please help me with a dynamic last cell problem?
Everyday I have a list of distributors that I have to send pricing info to,
nationwide we have over 35,000 account.
However, if I sort my list by state it come out different everyday.
When I finish sending then pricing info I have to fill in a column with my
name
This column id always D and always starts at D2, D1 is "Contacted By"
I've tried using the macro recorder and it wants to put in actual cell
address, but that change every time.

Sub LastCell()
Dim FirstCell As Variant
Dim LastCell As Variant

Range("A2").Select
FirstCell = ActiveCell.Offset(0, 3).Address
Selection.End(xlDown).Select
LastCell = ActiveCell.Offset(0, 3).Address

ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:= _
"=ohio.xls!" & FirstCell & ":" & LastCell ' Problem is here

End Sub
What I would like to do is fill in column D, from cell "D2" to the last
cell in column with my name - James
Can anyone please help me?
TIA
James

Dynamic LastCell
 

Sub LastCell()
Dim FirstCell As String
Dim LastCell As String

FirstCell = Range("A2").Offset(0, 3).Address
LastCell = Range("A2").Offset(0, 3).Address

ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:= _
"=ohio.xls!" & FirstCell & ":" & LastCell ' Problem is here

Names("MyRange").Value = "James"

End Sub


--
Regards,
Tom Ogilvy


"James Agostinho" wrote in message
nk.net...
Hello NG,

Can someone please help me with a dynamic last cell problem?
Everyday I have a list of distributors that I have to send pricing info
to,
nationwide we have over 35,000 account.
However, if I sort my list by state it come out different everyday.
When I finish sending then pricing info I have to fill in a column with my
name
This column id always D and always starts at D2, D1 is "Contacted By"
I've tried using the macro recorder and it wants to put in actual cell
address, but that change every time.

Sub LastCell()
Dim FirstCell As Variant
Dim LastCell As Variant

Range("A2").Select
FirstCell = ActiveCell.Offset(0, 3).Address
Selection.End(xlDown).Select
LastCell = ActiveCell.Offset(0, 3).Address

ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:= _
"=ohio.xls!" & FirstCell & ":" & LastCell ' Problem is here

End Sub
What I would like to do is fill in column D, from cell "D2" to the last
cell in column with my name - James
Can anyone please help me?
TIA
James

Dynamic LastCell
 

Let me try that again - several errors/omissions:

Sub LastCell()
Dim FirstCell As String
Dim LastCell As String

FirstCell = Range("A2").Offset(0, 3).Address
LastCell = Range("A2").End(xlDown).Offset(0, 3).Address

ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:= _
"='" & ActiveSheet.Name & "'!" & FirstCell & ":" & LastCell '
Problem is here

Range("MyRange").Value = "James"

End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Sub LastCell()
Dim FirstCell As String
Dim LastCell As String

FirstCell = Range("A2").Offset(0, 3).Address
LastCell = Range("A2").Offset(0, 3).Address

ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:= _
"=ohio.xls!" & FirstCell & ":" & LastCell ' Problem is here

Names("MyRange").Value = "James"

End Sub


--
Regards,
Tom Ogilvy


"James Agostinho" wrote in message
nk.net...
Hello NG,

Can someone please help me with a dynamic last cell problem?
Everyday I have a list of distributors that I have to send pricing info
to,
nationwide we have over 35,000 account.
However, if I sort my list by state it come out different everyday.
When I finish sending then pricing info I have to fill in a column with
my
name
This column id always D and always starts at D2, D1 is "Contacted By"
I've tried using the macro recorder and it wants to put in actual cell
address, but that change every time.

Sub LastCell()
Dim FirstCell As Variant
Dim LastCell As Variant

Range("A2").Select
FirstCell = ActiveCell.Offset(0, 3).Address
Selection.End(xlDown).Select
LastCell = ActiveCell.Offset(0, 3).Address

ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:= _
"=ohio.xls!" & FirstCell & ":" & LastCell ' Problem is here

End Sub
What I would like to do is fill in column D, from cell "D2" to the last
cell in column with my name - James
Can anyone please help me?
TIA
James

Dynamic LastCell
 

Hi James,

Besides Tom's fix of your code, here are three other ways to do that.

Sub CellNamer()
Dim i As Integer
i = Range("A65000").End(xlUp).Row
Range("B2").Select
For i = 1 To i - 1
ActiveCell.Value = "James"
ActiveCell.Offset(1, 0).Select
Next
End Sub

Sub RangeNamer()
Dim LastRow As Long
Dim Drange As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set Drange = Range("B2:B" & LastRow)
Drange.Value = "James"
End Sub

Sub TheResizeNamer()
Dim i As Integer
i = Cells(Rows.Count, "A").End(xlUp).Row - 1
Range("B2").Resize(i, 1).Value = "James"
End Sub

HTH
Regards,
Howard


"James Agostinho" wrote in message
nk.net...
Hello NG,

Can someone please help me with a dynamic last cell problem?
Everyday I have a list of distributors that I have to send pricing info
to,
nationwide we have over 35,000 account.
However, if I sort my list by state it come out different everyday.
When I finish sending then pricing info I have to fill in a column with my
name
This column id always D and always starts at D2, D1 is "Contacted By"
I've tried using the macro recorder and it wants to put in actual cell
address, but that change every time.

Sub LastCell()
Dim FirstCell As Variant
Dim LastCell As Variant

Range("A2").Select
FirstCell = ActiveCell.Offset(0, 3).Address
Selection.End(xlDown).Select
LastCell = ActiveCell.Offset(0, 3).Address

ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:= _
"=ohio.xls!" & FirstCell & ":" & LastCell ' Problem is here

End Sub
What I would like to do is fill in column D, from cell "D2" to the last
cell in column with my name - James
Can anyone please help me?
TIA
James

Dynamic LastCell
 

Gentlemen,
Thank you for the help, I knew there must be way, I played with this for
several hours and I couldn't get the syntax just right.
Anyway thanks
James
"L. Howard Kittle" wrote in message
news:Dr8_b.99725$jk2.468474@attbi_s53...
Hi James,

Besides Tom's fix of your code, here are three other ways to do that.

Sub CellNamer()
Dim i As Integer
i = Range("A65000").End(xlUp).Row
Range("B2").Select
For i = 1 To i - 1
ActiveCell.Value = "James"
ActiveCell.Offset(1, 0).Select
Next
End Sub

Sub RangeNamer()
Dim LastRow As Long
Dim Drange As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set Drange = Range("B2:B" & LastRow)
Drange.Value = "James"
End Sub

Sub TheResizeNamer()
Dim i As Integer
i = Cells(Rows.Count, "A").End(xlUp).Row - 1
Range("B2").Resize(i, 1).Value = "James"
End Sub

HTH
Regards,
Howard


"James Agostinho" wrote in message
nk.net...
Hello NG,

Can someone please help me with a dynamic last cell problem?
Everyday I have a list of distributors that I have to send pricing info
to,
nationwide we have over 35,000 account.
However, if I sort my list by state it come out different everyday.
When I finish sending then pricing info I have to fill in a column with
my
name
This column id always D and always starts at D2, D1 is "Contacted By"
I've tried using the macro recorder and it wants to put in actual cell
address, but that change every time.

Sub LastCell()
Dim FirstCell As Variant
Dim LastCell As Variant

Range("A2").Select
FirstCell = ActiveCell.Offset(0, 3).Address
Selection.End(xlDown).Select
LastCell = ActiveCell.Offset(0, 3).Address

ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:= _
"=ohio.xls!" & FirstCell & ":" & LastCell ' Problem is here

End Sub
What I would like to do is fill in column D, from cell "D2" to the last
cell in column with my name - James
Can anyone please help me?
TIA
James