+ Reply to Thread
Results 1 to 10 of 10

Code with last column and last row to recognize multiple commands through the macro code

  1. #1
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Code with last column and last row to recognize multiple commands through the macro code

    Hi.
    The code I have below is relevant to this spreadsheet Copy of Invoice test.xlsm
    In the code below I marked the script that has issues in red. The code takes the last row and the last column and works from there. This works fine with single commands, however, when I take a cell (based off the last row and the last column) and try to give that cell multiple commands excel sends back an error. When I use this multiple command (see part of code that is marked in red) for a range that is not based off the last column it works fine.
    Thanks in advance .


    Please Login or Register  to view this content.

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Code with last column and last row to recognize multiple commands through the macro co

    Hi kosherboy,

    From your red text, first line, try changing to:

    With Range(Cells(lr + 1, lc - 4).Address)

    that may fix it for you.

    cheers,

    Arkadi

  3. #3
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: Code with last column and last row to recognize multiple commands through the macro co

    Nice! That worked perfectly
    Can you explain to me why this changes everything?

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Code with last column and last row to recognize multiple commands through the macro co

    Range(cells(x,y)) would not work because it is not a function, and doesn't calculate the value of the Cells(x,y) position.
    Range is an object, and you can assign it the address of the cell, but need to feed it the actual address. Sine .address is a propery of the cell, you CAN say that Range should be assigned the value of the .address of the cell (i.e. Cells(x,y).address)
    Last edited by Arkadi; 02-13-2014 at 03:26 PM. Reason: trying to be more concise

  5. #5
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: Code with last column and last row to recognize multiple commands through the macro co

    Ok, so why does a code like the one below work without inserting the word address?
    The code below is based off only the last row and not a last column.
    Please Login or Register  to view this content.

  6. #6
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Code with last column and last row to recognize multiple commands through the macro co

    In your example you are still supplying a range, albeit with LR+1 as a variable, but you are giving the B as text and adding the number. In that case you are using a variable to build the Range, but not calculating the Range, if you get my meaning. inside the brackets is not a cell(), but an actual Range value, because inside the brackets still will be read by the code as B and a number. Example if LR = 12 then the inside of the brackets is B12.

    The real parallel example would be replacing the ("B" & LR+1) with 'cells(LR+1 ,2)' and you will see that DOES need the .address to work

    Happy to keep discussing, but don't forget to mark as solved
    Last edited by Arkadi; 02-13-2014 at 03:35 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: Code with last column and last row to recognize multiple commands through the macro co

    Makes sense. Thanks.

  8. #8
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Code with last column and last row to recognize multiple commands through the macro co

    My pleasure
    Glad I'm finally learning enough to help others out too

  9. #9
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: Code with last column and last row to recognize multiple commands through the macro co

    Welcome to the forum
    This thread is marked solved and I added a rep to you.

  10. #10
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Code with last column and last row to recognize multiple commands through the macro co

    Thanks Lol my first rep point...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Code do not recognize retrieved value
    By score in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-26-2013, 09:27 PM
  2. VBA code to recognize when the excel was updated
    By ezthelm in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-19-2011, 03:37 AM
  3. Can I code Mainframe commands in Excel macro?
    By mf_coe in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-16-2009, 04:23 AM
  4. Code to recognize where selection change active cell originates
    By jman0707 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-14-2008, 12:27 PM
  5. describing VBA commands/code
    By zealot_02 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-26-2008, 04:49 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1