+ Reply to Thread
Results 1 to 12 of 12

First time coding in VB, no errors but my very simple script isn't working. Help?

  1. 02-16-2012, 09:05 PM #1
    metajellyfish
    metajellyfish is offline
    Registered User
    Join Date
    02-16-2012
    Location
    California (CA)
    MS-Off Ver
    Excel 2011
    Posts
    6

    First time coding in VB, no errors but my very simple script isn't working. Help?

    My boss asked me to pull some data from a big Excel spreadsheet. Basically she has two files, one of them has A, a one-dimensional list of experimental trials, and the other file has a worksheet B where column 1 corresponds to the names of the trials, and the rest of the columns have values for other attributes of the data. Presumably all the names in A are in B as well, but the script should accomodate it if that's not the case.

    I want to take all the data from the columns of B whose value in the first row match the names from A, and paste those rows into a sheet C. Here's what I wrote:



    Please Login or Register  to view this content.
    ----------------

    When I run this, it doesn't actually do anything. I don't get any errors, but I don't get any output either. Obviously I'm missing something. If I'm being unclear about anything, please let me know and I'll try to explain it better.
    Any suggestions would be greatly appreciated!
    Last edited by metajellyfish; 02-16-2012 at 09:41 PM.
    Reply With QuoteRegister To Reply
  2. 02-16-2012, 09:36 PM #2
    JapanDave
    JapanDave is offline
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: First time coding in VB, no errors but my very simple script isn't working. Help?

    Hey, Could put you code in the code tags? Use this icon in advanced edit #

    On to your code,
    You have declared x to be two different values, so that will not work. What is "ContNum"?

    If you can post some dummy workbooks and a brief explanation of what you need, that would help
    Last edited by JapanDave; 02-16-2012 at 09:46 PM.
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files
    Reply With QuoteRegister To Reply
  3. 02-16-2012, 09:44 PM #3
    metajellyfish
    metajellyfish is offline
    Registered User
    Join Date
    02-16-2012
    Location
    California (CA)
    MS-Off Ver
    Excel 2011
    Posts
    6

    Re: First time coding in VB, no errors but my very simple script isn't working. Help?

    Thanks, I was trying to figure out how to do that.

    Re: declaring X to be two separate values, do you mean "From X = 1 to X = 100", "Next X", or both combined?

    Re: ContNum is just a placeholder for the contents of the string from list A that I'm searching for in the first column of B. It means "Control Number" but that's aside the point. I'm horrible at naming variables.
    Reply With QuoteRegister To Reply
  4. 02-16-2012, 10:08 PM #4
    rylo
    rylo is offline
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: First time coding in VB, no errors but my very simple script isn't working. Help?

    Hi

    Where is ContNum filled? Is it a global variable that is filled with another piece of code?

    How about you attach an example workbook that would include all your relevant code.

    My initial though is that this could probably be done using advanced filter.

    rylo
    Reply With QuoteRegister To Reply
  5. 02-16-2012, 10:40 PM #5
    JapanDave
    JapanDave is offline
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: First time coding in VB, no errors but my very simple script isn't working. Help?

    If you are using the for next loop, it would normally go like this.

    Please Login or Register  to view this content.
    You have not assigned "ContNum" a value so it would do anything at present.

    Best post a workbook I think.
    Reply With QuoteRegister To Reply
  6. 02-16-2012, 11:03 PM #6
    metajellyfish
    metajellyfish is offline
    Registered User
    Join Date
    02-16-2012
    Location
    California (CA)
    MS-Off Ver
    Excel 2011
    Posts
    6

    Re: First time coding in VB, no errors but my very simple script isn't working. Help?

    Here is a sample workbook. It's absurdly simple, but it should get the point across. I see the issue with ContNum, I meant to call that instead of DataName. My apologies. I've edited the following code accordingly.

    What I want is a way to populate columns 2-4 in Sheet_A with the data corresponding to those names in Sheet_B. Modifying the code I wrote above a bit to suit this workbook, we have:

    Please Login or Register  to view this content.
    SampleWorkbook.xlsx

    Am I missing anything?
    Reply With QuoteRegister To Reply
  7. 02-16-2012, 11:15 PM #7
    rylo
    rylo is offline
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: First time coding in VB, no errors but my very simple script isn't working. Help?

    Hi

    My first question is why don't you just use a VLOOKUP formula?

    rylo
    Reply With QuoteRegister To Reply
  8. 02-16-2012, 11:19 PM #8
    metajellyfish
    metajellyfish is offline
    Registered User
    Join Date
    02-16-2012
    Location
    California (CA)
    MS-Off Ver
    Excel 2011
    Posts
    6

    Re: First time coding in VB, no errors but my very simple script isn't working. Help?

    I posted the same on the VisualBasic subreddit, and that was the first answer I got. It does seem to solve my problem quite well, but when I tried implementing it, I ran into the following challenge:
    Please Login or Register  to view this content.
    How can I copy-and-paste this (or come up with a script to generate the function) such that {i} changes relative to the row number, and {j} changes relative to the column, but A2:Y1001 remains constant?
    Reply With QuoteRegister To Reply
  9. 02-16-2012, 11:31 PM #9
    rylo
    rylo is offline
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: First time coding in VB, no errors but my very simple script isn't working. Help?

    Hi

    Here goes.
    Please Login or Register  to view this content.
    rylo
    Reply With QuoteRegister To Reply
  10. 02-16-2012, 11:37 PM #10
    metajellyfish
    metajellyfish is offline
    Registered User
    Join Date
    02-16-2012
    Location
    California (CA)
    MS-Off Ver
    Excel 2011
    Posts
    6

    Re: First time coding in VB, no errors but my very simple script isn't working. Help?

    Rylo, what you wrote is super helpful, thank you. Is there a way to generate these commands (the "Range("B2")..." through "Range("D2")..." lines) without typing them by hand? I ask because in reality I have 20 columns to populate.
    Reply With QuoteRegister To Reply
  11. 02-17-2012, 12:11 AM #11
    rylo
    rylo is offline
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: First time coding in VB, no errors but my very simple script isn't working. Help?

    Hi

    Try 

    Please Login or Register  to view this content.
    If you have a variable number of columns, not a fixed number of columns, then the output column number can be determined automatically.

    rylo
    Reply With QuoteRegister To Reply
  12. 02-17-2012, 05:01 PM #12
    metajellyfish
    metajellyfish is offline
    Registered User
    Join Date
    02-16-2012
    Location
    California (CA)
    MS-Off Ver
    Excel 2011
    Posts
    6

    Re: First time coding in VB, no errors but my very simple script isn't working. Help?

    Thanks Rylo, this did the trick. I appreciate your help.
    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)

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