+ Reply to Thread
Results 1 to 5 of 5

Define macro parameters using InputBox ?

Hybrid View

MarianneBE Define macro parameters using... 07-04-2014, 09:17 AM
6StringJazzer Re: Define macro parameters... 07-05-2014, 09:51 PM
MarianneBE Re: Define macro parameters... 07-07-2014, 04:55 AM
mikerickson Re: Define macro parameters... 07-05-2014, 10:20 PM
MarianneBE Re: Define macro parameters... 07-07-2014, 05:04 AM
  1. 07-04-2014, 09:17 AM #1
    MarianneBE is offline
    Registered User
    Join Date
    07-04-2014
    Location
    Brussels, Belgium
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Define macro parameters using InputBox ?

    Hi everyone, I just started using macros in Excel few days ago and I am a bit stuck in my code.

    I receive a weekly report (Excel file) from a forwarder and I use it to complete another Excel file ("sea log"). I used to do a serie of VLOOKUP but it really takes long (lots of columns to fill) and started thinking about a macro that would do the job in one click (or two).

    The 'report' file only shows the latest shipments whereas the 'sea log' file is used for analysis and keeps track of shipment from 2012 until now.
    I don't want the macro to run on all rows as it replace my 2012-2013 data with #N/A.

    This is what my code looks like so far :
    Sub SeaLogUpdate()
With Sheets("CTN Data")
    .Range("B6992:B7003").Value = WorksheetFunction.VLookup(.Range("A6992:A7003").Value, Sheets("REPORT").Range("A:D"), 4, False)
    .Range("D6992:D7003").Value = WorksheetFunction.VLookup(.Range("A6992:A7003").Value, Sheets("REPORT").Range("A:G"), 7, False)
    .Range("E6992:E7003").Value = WorksheetFunction.VLookup(.Range("A6992:A7003").Value, Sheets("REPORT").Range("A:N"), 14, False)
    .Range("F6992:F7003").Value = WorksheetFunction.VLookup(.Range("A6992:A7003").Value, Sheets("REPORT").Range("A:R"), 18, False)
    .Range("I6992:I7003").Value = WorksheetFunction.VLookup(.Range("A6992:A7003").Value, Sheets("REPORT").Range("A:V"), 22, False)
    .Range("J6992:J7003").Value = WorksheetFunction.VLookup(.Range("A6992:A7003").Value, Sheets("REPORT").Range("A:W"), 23, False)
    .Range("G6992:G7003").Value = WorksheetFunction.VLookup(.Range("A6992:A7003").Value, Sheets("REPORT").Range("A:L"), 12, False)
    .Range("M6992:M7003").Value = WorksheetFunction.VLookup(.Range("A6992:A7003").Value, Sheets("REPORT").Range("A:AH"), 34, False)
    .Range("N6992:N7003").Value = WorksheetFunction.VLookup(.Range("A6992:A7003").Value, Sheets("REPORT").Range("A:Y"), 25, False)
End With
End Sub
    Rows 6992 to 7003 are the new shipments that I need to update this week, but next week it will be different. I don't feel like changing the figures manually every week...

    Is there a way to have an input box that ask for 'start row' and 'end row' values before running the VLOOKUP macro ?
    Or maybe you know another way to do it ?

    Thanks !
    Reply With QuoteRegister To Reply
  2. 07-05-2014, 09:51 PM #2
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,995

    Re: Define macro parameters using InputBox ?

    Writing this to get row numbers from the user isn't hard, but I can't make sense out of what you're doing here. Does the code you posted actually work?

    The first argument of VLOOKUP must evaluate to a single value. In your case, you are providing a range with 12 values. In each line of code, VLOOKUP will use the value in the upper left corner of the range, which is A6992. What are you really intending to do here?

    You can certainly use InputBox to collect two integers, assign them to variables, and use those variables to build the Range expressions in your code. But let's address the question above first.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  3. 07-07-2014, 04:55 AM #3
    MarianneBE is offline
    Registered User
    Join Date
    07-04-2014
    Location
    Brussels, Belgium
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Re: Define macro parameters using InputBox ?

    Hi 6StringJazzer and thank you for taking the time to address my question. Yes the current code is actually working, don't ask me how or why, I am not sure myself... It fills up 9 columns with the data of the report, but only on the 12 rows selected (6992 to 7003).

    If I put a single value instead of a range in the 1st argument (let's say A6992), I only get the result for said value in all the rows of the defined range (B6992 to B7003) and that is not what I want here as 1 row = 1 bill of lading (with a specific number).

    Let me know if my explanations are clear.
    Reply With QuoteRegister To Reply
  4. 07-05-2014, 10:20 PM #4
    mikerickson is offline
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Define macro parameters using InputBox ?

    Is there some way to distinguish between different week's blocks of data. A row that says "NEW WEEK" or something?

    Also, could I suggest you use With..End With and Offset, so that you only have to put the adjust for new rows in one place.

    With Sheets("CTN Data")
    With Range("B6992:B7003")
        .Value = WorksheetFunction.VLookup(.Offset(0, -1).Value, Sheets("REPORT").Range("A:D"), 4, False)
        .Offset(0, 2).Value = WorksheetFunction.VLookup(.Offset(0, -1).Value, Sheets("REPORT").Range("A:G"), 7, False)
        .Offset(0,3).Value = WorksheetFunction.VLookup(.Offset(0, -1).Value, Sheets("REPORT").Range("A:N"), 14, False)
        .Offset(0, 4).Value = WorksheetFunction.VLookup(.Offset(0, -1).Value, Sheets("REPORT").Range("A:R"), 18, False)
        .Offset(0, 7).Value = WorksheetFunction.VLookup(.Offset(0, -1).Value, Sheets("REPORT").Range("A:V"), 22, False)
        .Offset(0, 8).Value = WorksheetFunction.VLookup(.Offset(0, -1).Value, Sheets("REPORT").Range("A:W"), 23, False)
        .Offset(0, 5).Value = WorksheetFunction.VLookup(.Offset(0, -1).Value, Sheets("REPORT").Range("A:L"), 12, False)
        .Offset(0, 11).Value = WorksheetFunction.VLookup(.Offset(0, -1).Value, Sheets("REPORT").Range("A:AH"), 34, False)
        .Offset(0, 12).Value = WorksheetFunction.VLookup(.Offset(0, -1).Value, Sheets("REPORT").Range("A:Y"), 25, False)
    End With
End With
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.
    Reply With QuoteRegister To Reply
  5. 07-07-2014, 05:04 AM #5
    MarianneBE is offline
    Registered User
    Join Date
    07-04-2014
    Location
    Brussels, Belgium
    MS-Off Ver
    MS Office 2010
    Posts
    3

    Re: Define macro parameters using InputBox ?

    Hi MikeErickson, thanks a lot for your reply !
    It is a clear improvement to have only one adjustment to make instead of every lines of codes.

    Currently there is no way to distinguish the different blocks of data but I could add this information so that the macro runs only for the new ones.
    This way, no need of InputBox, right ?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to make a macro that changes the parameters of a certain macro shortcut/hotkey?
    By kjy1989 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-15-2013, 02:43 AM
  2. error 1004 application define or object define
    By jay11 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-11-2011, 04:09 AM
  3. Define named range where user can define size
    By nahousto in forum Excel General
    Replies: 4
    Last Post: 07-06-2009, 05:09 PM
  4. Inputbox button control + msgbox for empty inputbox
    By D_Rennie in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-30-2009, 12:39 PM
  5. [SOLVED] Define array of sheets by inputbox
    By Herman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2005, 11:06 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1