Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Split the Address into Row/Column 1

Status
Not open for further replies.
kjv1611

kjv1611

New member
Jul 9, 2003
10,758
US
I was attempting to use a string to store the value of ActiveCell.Address, and that worked great. What I want to do now is to take that value, and split it to where I can use the new data to form a new range. For example, this is kind of what I want to do:
Code: 
     Columns.SpecialCells(xlCellTypeLastCell).Select
     CurrentRange = ActiveCell.Address
     MyArray = Split(CurrentRange, "$")
     CurrentColumn = MyArray(1)
     CurrentRow = MyArray(2)
     CurrentColumn = CurrentColumn - 1
     Range(CurrentColumnCurrentRow).Select
     [GREEN]'Not sure of the right format to use here,
     'since the format is Range(a1) where a is the column, 
     'and 1 is the row. Maybe someone could help me there as well?



Stephen [infinity]
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV
 
Sort by date Sort by votes
Oops, I messed up the format of the code i used (as far as the web page part - this is the same thing, but corrected my typo (left out code):
Code: 
     Columns.SpecialCells(xlCellTypeLastCell).Select
     CurrentRange = ActiveCell.Address
     MyArray = Split(CurrentRange, "$")
     CurrentColumn = MyArray(1)
     CurrentRow = MyArray(2)
     CurrentColumn = CurrentColumn - 1
     Range(CurrentColumnCurrentRow).Select
     [GREEN]'Not sure of the right format to use here,
     'since the format is Range(a1) where a is the column, 
     'and 1 is the row. Maybe someone could help me there as well?
[/GREEN]


Stephen [infinity]
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV
 
Upvote 0 Downvote
Stephen,

You're making to too hard
Code: 
With Columns.SpecialCells(xlCellTypeLastCell)
  LastCol = .Column
  LastRow = .Row
End with
Range(LastRow, LastCol).Select
I personally try to AVOID selecting cells as much as possible..

faq707-4105 How Can I Make My Code Run Faster?

:)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Skip??

[blue]Cells(LastRow, LastCol).Select[/blue]

Otherwise, I agree [wink]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
 
Upvote 0 Downvote
Thanks for the info there! [SMILE] I've got a long way to go yet. Could either of you recommend a good Excel book for VBA/macros? I didn't know if a Sybex book similar to the Access 2002 Desktop Developer's Handbook would be good for it, or what? I have been using 3 books by Sybex for Access 2002 - Desktop Developer Handbook, Enterprise Developer Handbook, and Access 2002 VBA Handbook. If you know of a really good book that would provide much in the form of quick references, I'd appreciate that. I've just been kind of going on the fly so far with Excel VBA, and then comming here if could not find it via the Help built into Excel or by guessing.

Stephen [infinity]
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV
 
Upvote 0 Downvote
Okay, new question/situation on this one: When using the code:

Code: 
    With Columns.SpecialCells(xlCellTypeLastCell)
        LastCol = .Column
        LastRow = .Row
    End With
    Cells(LastRow, LastCol).Select

I get the following error message on the last line:
[BLUE]Run-time error '1004':
Application-defined or object-defined error[/BLUE]

Is there a particular reference I need to be using to make this work?

Stephen [infinity]
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV
 
Upvote 0 Downvote
Not sure. It runs for me. But try this...
Code: 
With ActiveSheet
    With .Columns.SpecialCells(xlCellTypeLastCell)
        LastCol = .Column
        LastRow = .Row
    End With
    .Cells(LastRow, LastCol).Select
End With
:)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
maybe you are missing a "." on the "with columns". It should be "with .columns"? (or was it just a typo on the forums?

(same for the last line (Cells(LastRow, LastCol).Select)

Regards

Frederico Fonseca
SysSoft Integrated Ltd
 
Upvote 0 Downvote
kjv1611 said:
Could either of you recommend a good Excel book for VBA/macros?
Click to expand...

Stephen
I can't personally vouch for them, but the books that get recommended more than anything else I've seen on this site are those of John Walkenbach.

Check out his web site at
;-)

If a man says something and there are no women there to hear him, is he still wrong? [ponder]
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244
 
Upvote 0 Downvote
Walkenbach!!!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Thanks for the reference on the book. I tried adding "With ActiveSheet" and End With for that, but no go yet, still same error in same location. Tried adding a '.' in front of Cells(LastRow, LastCol).Select, still got same error. Could it be the format of the Cells(LastRow, LastCol)? B/c when I try to type Range(a cell address), the pop-up deal shows Range(Cell1, [Cell2]) as range.. will try to mess with that a little, and see what I get. Any info/ideas in the mean time, I'll be much appreciative.

Alsok, thanks for the info on the excel book author - I'll take a look and check around a little more, and maybe try one of them to see.

Stephen [infinity]
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV
 
Upvote 0 Downvote
Are you coding this in a MODULE?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Yes. I just "recoreded" a macro, and told Excel to store in the Personal Macro Workbook, then located the macro in one of the Modules under the Personal Macro Workbook, and began to fiddle with some extra code.

Stephen [infinity]
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV
 
Upvote 0 Downvote
I dunno! It works as posted. I just copied/pasted and ran.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Figures... LOL [SMILE]
I'll try it again, and see if there is just some stupid something I'm doing wrong.

Stephen [infinity]
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV
 
Upvote 0 Downvote
Still Trying, but it seems I always get the error on the Range or Cells(LastRow, LastCol).Select - I even tried joining them back together with this code, then calling the new variable:
Code: 
    MyArray(1) = LastCol
    MyArray(2) = LastRow
    LastAddress = Join(MyArray, "$")
    Cells(LastAddress).Select

The only thing I can figure is that for some reason it is pulling the numeric value as apposed to the letter when pulling .Column, and that the Range() and Cells() arguments require a format such as $A$1 instead of $1$1. Any clues as to how to change this to see if that's it?

Stephen [infinity]
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV
 
Upvote 0 Downvote
The syntax of Cells(row, col) is that row is LONG and col is INTEGER (ie BOTH numeric) or col can be alpha.

So Cells(1, 1) & Cells(1, "A") are equivalent statements.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Maybe the issue is that the code is in the Personal.xls and doesn't recognise a sheetname or similar if it has been hard coded ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Geoff,

You may have hit on it although ActiveSheet implies the Workbook in which it resides...
Code: 
With Workbooks("YourWorkbookName").Wosrsheets("YourSheetName")
    With .Columns.SpecialCells(xlCellTypeLastCell)
        LastCol = .Column
        LastRow = .Row
    End With
    .Cells(LastRow, LastCol).Select
End With


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Skip - true - activesheet should be fine but kjv1611 has yet to post any code which includes a specified sheet of ANY type - active or not !! ;-)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Combine two working macros into one for MS Word 1
Replies
7
Views
1K
strongm
strongm
IcarusHallsorts
  • Locked
  • Question
I have a column of data in Column A
Replies
3
Views
513
Andrzejek
Andrzejek
kris gersbach
  • Locked
  • Question
I’m importing a csv into a vfp curs
Replies
1
Views
331
Andrzejek
Andrzejek
jtm2020hyo
  • Locked
  • Question
VBA script to split 0D, 1D, and 2D arrays 1
Replies
10
Views
751
jtm2020hyo
jtm2020hyo
dgillz
  • Locked
  • Question
Back coloring an entire row if a duplicate is found in a dynamic range 1
Replies
18
Views
878
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top