Roger’s Excel Thread - Page 9 - Antsmarching.org Forums - Dave Matthews Band Discussion
Old 07-26-2017, 10:40 AM   #241
DaveHead36
YNWA
 
DaveHead36's Avatar
 
Join Date: Jul 2003
Location: Texas
Posts: 6,693

Shows Seen: 4

DMB Hub Stubs: 4

My Tour Central Stats

Re: Excel Help

Quote:
Originally Posted by rconverse View Post
I just saw the whole hard and soft target dates. You'll need to use something more like this for that...
You just made my whole month! Thanks! How on earth do you know when to use the "" and , and whatnot? Insanity!
__________________
Time flies like an arrow, fruit flies like a banana
DaveHead36 is offline   Reply With Quote

  • Want to hide all ads on Ants? Click here
  • Old 07-26-2017, 10:59 AM   #242
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by DaveHead36 View Post
    You just made my whole month! Thanks! How on earth do you know when to use the "" and , and whatnot? Insanity!
    You're welcome!
    rconverse is offline   Reply With Quote
    Old 11-29-2017, 08:21 PM   #243
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Just wanted to document some code. More than likely, this won't make sense to anyone else.

    Code:
        'build formula
        strFormula = "=" & ws.Range("E" & rowNum).Text
        'substitute the old criteria with the new criteria
        strFormula = Application.WorksheetFunction.Substitute(strFormula, "C$1", r.Address)
        
        'enter formula into cell 3 of the column to be updated, copy down, and paste as values
        ws1.Activate
        ws1.Range(r.Address).Offset(2, 0).Formula = strFormula
        FinalRow = ws1.Range("B" & Rows.Count).End(xlUp).Row
        ws1.Range(r.Address).Offset(2, 0).Copy
        ws1.Range(Cells(3, r.Column), Cells(FinalRow, r.Column)).PasteSpecial xlPasteFormulasAndNumberFormats
        ws1.Range(Cells(3, r.Column), Cells(FinalRow, r.Column)).Copy
        ws1.Range(Cells(3, r.Column), Cells(FinalRow, r.Column)).PasteSpecial xlPasteValuesAndNumberFormats
        ws1.Range("A1").Select
        
        Set ws = Nothing
        Set ws1 = Nothing
        wb1.Close
    Bold = coding equivalent to gold...or bitcoin. Literally shaking because I'm so happy I finally figured that out.
    rconverse is offline   Reply With Quote
    Old 11-29-2017, 09:06 PM   #244
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    Just wanted to document some code. More than likely, this won't make sense to anyone else.

    Code:
        'build formula
        strFormula = "=" & ws.Range("E" & rowNum).Text
        'substitute the old criteria with the new criteria
        strFormula = Application.WorksheetFunction.Substitute(strFormula, "C$1", r.Address)
        
        'enter formula into cell 3 of the column to be updated, copy down, and paste as values
        ws1.Activate
        ws1.Range(r.Address).Offset(2, 0).Formula = strFormula
        FinalRow = ws1.Range("B" & Rows.Count).End(xlUp).Row
        ws1.Range(r.Address).Offset(2, 0).Copy
        ws1.Range(Cells(3, r.Column), Cells(FinalRow, r.Column)).PasteSpecial xlPasteFormulasAndNumberFormats
        ws1.Range(Cells(3, r.Column), Cells(FinalRow, r.Column)).Copy
        ws1.Range(Cells(3, r.Column), Cells(FinalRow, r.Column)).PasteSpecial xlPasteValuesAndNumberFormats
        ws1.Range("A1").Select
        
        Set ws = Nothing
        Set ws1 = Nothing
        wb1.Close
    Bold = coding equivalent to gold...or bitcoin. Literally shaking because I'm so happy I finally figured that out.
    Been fucking there before. Got an offset formula to do something I've been looking for for over a week on Sunday. Literally texted it (as in, typed out the entire formula on my phone) to my friend just because I was that pumped it worked. I'm still excited it work

    ps: rog are you on the excel subreddit? Super fun time reading through there.
    __________________
    Tape Cover Artwork

    Last edited by daveshookme; 11-29-2017 at 09:09 PM.
    daveshookme is offline   Reply With Quote
    Old 11-29-2017, 09:41 PM   #245
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by daveshookme View Post
    Been fucking there before. Got an offset formula to do something I've been looking for for over a week on Sunday. Literally texted it (as in, typed out the entire formula on my phone) to my friend just because I was that pumped it worked. I'm still excited it work

    ps: rog are you on the excel subreddit? Super fun time reading through there.
    Oh, for sure. Like I mentioned last time I worked on your workbook (I think I created a macro that isolated rows or something), anyways, I remember seeing all of your functions and named ranges and saying that you're certainly no novice.

    It's kind of like golf...Lots of bad shots but just that one good shot will bring you back.

    #fuckingexcel
    rconverse is offline   Reply With Quote
    Old 11-29-2017, 09:50 PM   #246
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by daveshookme View Post
    ps: rog are you on the excel subreddit? Super fun time reading through there.
    No, I'm not. I just don't like the look/feel of reddit. Gotta link to the subthread?
    rconverse is offline   Reply With Quote
    Old 11-30-2017, 05:55 AM   #247
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    No, I'm not. I just don't like the look/feel of reddit. Gotta link to the subthread?
    http://www.reddit.com/r/excel

    This one's stupid amounts of impressive. long story short: dude was given an extremely locked down computer for work. Couldn't do fuck-all anything except work, which he didn't like. He ended up writing code in Excel to play Wall-E. Play/Pause/Rewind buttons, player screen.... the works.

    https://www.reddit.com/r/excel/comme..._machine_time/
    __________________
    Tape Cover Artwork
    daveshookme is offline   Reply With Quote
    Old 11-30-2017, 05:58 AM   #248
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    Oh, for sure. Like I mentioned last time I worked on your workbook (I think I created a macro that isolated rows or something), anyways, I remember seeing all of your functions and named ranges and saying that you're certainly no novice.

    It's kind of like golf...Lots of bad shots but just that one good shot will bring you back.

    #fuckingexcel
    Yeah this one is a Dynamic Named Range called GoTo which, in my timesheet worksheet, goes to the date specified in cell A1. Spent a day or two trying to get that to work with a Choose function, but then choose can only handle up to 254 arguments which is obviously less than a full year so I'd be missing dates, was trying to think of ways to shorten the choose function to get it to work but got stuck there... finally got an offset function to subtract the target date from Jan1, move down however many weeks after Jan1 the target date is, then move over the correct number of days. Took a while to get it exact but it works now and god that feeling never gets old
    __________________
    Tape Cover Artwork
    daveshookme is offline   Reply With Quote
    Old 11-30-2017, 06:28 AM   #249
    AJF_41
     
    AJF_41's Avatar
     
    Join Date: Mar 2005
    Posts: 64,250

    Shows Seen: 27

    DMB Hub Stubs: 10

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    Just wanted to document some code. More than likely, this won't make sense to anyone else.

    Code:
        'build formula
        strFormula = "=" & ws.Range("E" & rowNum).Text
        'substitute the old criteria with the new criteria
        strFormula = Application.WorksheetFunction.Substitute(strFormula, "C$1", r.Address)
        
        'enter formula into cell 3 of the column to be updated, copy down, and paste as values
        ws1.Activate
        ws1.Range(r.Address).Offset(2, 0).Formula = strFormula
        FinalRow = ws1.Range("B" & Rows.Count).End(xlUp).Row
        ws1.Range(r.Address).Offset(2, 0).Copy
        ws1.Range(Cells(3, r.Column), Cells(FinalRow, r.Column)).PasteSpecial xlPasteFormulasAndNumberFormats
        ws1.Range(Cells(3, r.Column), Cells(FinalRow, r.Column)).Copy
        ws1.Range(Cells(3, r.Column), Cells(FinalRow, r.Column)).PasteSpecial xlPasteValuesAndNumberFormats
        ws1.Range("A1").Select
        
        Set ws = Nothing
        Set ws1 = Nothing
        wb1.Close
    Bold = coding equivalent to gold...or bitcoin. Literally shaking because I'm so happy I finally figured that out.
    can i sig this?
    __________________
    the feeling returns, whenever we close our eyes...

    growing vinyl collection: https://www.discogs.com/user/AJF_41/collection/covers
    AJF_41 is offline   Reply With Quote
    Old 11-30-2017, 06:45 AM   #250
    Beefsteak1138
     
    Beefsteak1138's Avatar
     
    Join Date: May 2003
    Posts: 82,549

    Shows Seen: 10

    DMB Hub Stubs: 7

    My Tour Central Stats

    Re: Excel Help

    Using Ants as a code repository. Unreal.
    __________________
    Quote:
    Originally Posted by Rebecca De Mornay View Post
    i wish i lived in a time where it was acceptable to have sex with kids.
    Beefsteak1138 is offline   Reply With Quote
    Old 11-30-2017, 07:04 AM   #251
    AJF_41
     
    AJF_41's Avatar
     
    Join Date: Mar 2005
    Posts: 64,250

    Shows Seen: 27

    DMB Hub Stubs: 10

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by Beefsteak1138 View Post
    Using Ants as a code repository. Unreal.
    there he is!!

    __________________
    the feeling returns, whenever we close our eyes...

    growing vinyl collection: https://www.discogs.com/user/AJF_41/collection/covers
    AJF_41 is offline   Reply With Quote
    Old 11-30-2017, 09:08 AM   #252
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by daveshookme View Post
    http://www.reddit.com/r/excel

    This one's stupid amounts of impressive. long story short: dude was given an extremely locked down computer for work. Couldn't do fuck-all anything except work, which he didn't like. He ended up writing code in Excel to play Wall-E. Play/Pause/Rewind buttons, player screen.... the works.

    https://www.reddit.com/r/excel/comme..._machine_time/
    That is awesome! I once built "Wheel of Fortune" in Excel. I wonder if I can find it.

    Quote:
    Originally Posted by daveshookme View Post
    Yeah this one is a Dynamic Named Range called GoTo which, in my timesheet worksheet, goes to the date specified in cell A1. Spent a day or two trying to get that to work with a Choose function, but then choose can only handle up to 254 arguments which is obviously less than a full year so I'd be missing dates, was trying to think of ways to shorten the choose function to get it to work but got stuck there... finally got an offset function to subtract the target date from Jan1, move down however many weeks after Jan1 the target date is, then move over the correct number of days. Took a while to get it exact but it works now and god that feeling never gets old
    It's so nerdy and dumb but still a good feeling. It's basically like:

    This is odd.
    What's happening here?
    Okay, now I'm getting upset.
    God dammit! This fucking thing doesn't work!!
    Keep calm. Don't lose your temper. Stay focused.
    FOCUSED MY ASS!!! FUCK YOU, EXCEL!! FUCK YOU BILL GATES!!!
    Oh, I need a comma there. How about tha....YEEEESSSSSSSSSSSS!!!!!!!!!!!!

    Quote:
    Originally Posted by AJF_41 View Post
    can i sig this?
    Sure.

    Quote:
    Originally Posted by Beefsteak1138 View Post
    Using Ants as a code repository. Unreal.
    My only computer is my work computer, so I don't have anywhere to store something like that digitally. Since I created this app for work, I can't take it with me, so wanted this somewhere in case I ever needed it again.
    rconverse is offline   Reply With Quote
    Old 11-30-2017, 11:30 AM   #253
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    That is awesome! I once built "Wheel of Fortune" in Excel. I wonder if I can find it.
    yeah go find it! I'd love to check it out.

    Somebody else in the excel subreddit made that app 2048 for excel and disguised it to look like a financial spreadsheet to avoid detection at work

    It's pretty fucking rad.
    __________________
    Tape Cover Artwork
    daveshookme is offline   Reply With Quote
    Old 11-30-2017, 11:54 AM   #254
    Climb2safety
    Free your mind
     
    Join Date: Feb 2014
    Posts: 32,897

    Shows Seen: 0

    DMB Hub Stubs: 1

    My Tour Central Stats

    Re: Excel Help

    The beef I am fighting with now is that when you open two spreadsheets and they are in the same excel program on your desktop there is no way to take one and drag it away on its own and then have them side by side.
    __________________

    This town is nuts, my kind of place. I don't never ever want to leave....
    Climb2safety is offline   Reply With Quote
    Old 11-30-2017, 11:55 AM   #255
    mr. testaverde
    King of Prussia
     
    Join Date: Aug 2003
    Location: location location
    Posts: 15,576

    Shows Seen: 58

    DMB Hub Stubs: 25

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    That is awesome! I once built "Wheel of Fortune" in Excel. I wonder if I can find it
    i once had mini-golf in excel and a few other great games. if i searched long and hard i could probably find them
    __________________
    Mmm boy that Hennigans goes down smooth. And afterwards you don't even smell.
    mr. testaverde is offline   Reply With Quote
    Old 11-30-2017, 12:01 PM   #256
    BTBaboon
     
    BTBaboon's Avatar
     
    Join Date: Jul 2015
    Posts: 51,340

    Shows Seen: 31

    DMB Hub Stubs: 10

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by Climb2safety View Post
    The beef I am fighting with now is that when you open two spreadsheets and they are in the same excel program on your desktop there is no way to take one and drag it away on its own and then have them side by side.
    Do you have 2 monitors?

    If so, un-maximize the window, stretch the application to fit across the two screens, then un-maximize the workbook and you should be able to line them up.

    Like this: https://i.imgur.com/CwughOY.jpg

    You can also open multiple instances of excel, but it can get wonky when trying to copy/paste values between them
    __________________
    We lead not only by example of our power, but by the power of our example.

    We are America, second to none, and we own the finish line! Don't forget it!
    BTBaboon is offline   Reply With Quote
    Old 11-30-2017, 12:01 PM   #257
    Beefsteak1138
     
    Beefsteak1138's Avatar
     
    Join Date: May 2003
    Posts: 82,549

    Shows Seen: 10

    DMB Hub Stubs: 7

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by Climb2safety View Post
    The beef I am fighting with now is that when you open two spreadsheets and they are in the same excel program on your desktop there is no way to take one and drag it away on its own and then have them side by side.
    You should be able to open another instance of excel (which should open in a separate window), then open your file from there.
    __________________
    Quote:
    Originally Posted by Rebecca De Mornay View Post
    i wish i lived in a time where it was acceptable to have sex with kids.
    Beefsteak1138 is offline   Reply With Quote
    Old 11-30-2017, 12:04 PM   #258
    Climb2safety
    Free your mind
     
    Join Date: Feb 2014
    Posts: 32,897

    Shows Seen: 0

    DMB Hub Stubs: 1

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by Beefsteak1138 View Post
    You should be able to open another instance of excel (which should open in a separate window), then open your file from there.
    Yeah, that’s what I do but it’s a pain to open a blank excel just to force new documents to use that version. Inevitably I end up with three in one.

    Thanks!
    __________________

    This town is nuts, my kind of place. I don't never ever want to leave....
    Climb2safety is offline   Reply With Quote
    Old 11-30-2017, 01:11 PM   #259
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by Climb2safety View Post
    The beef I am fighting with now is that when you open two spreadsheets and they are in the same excel program on your desktop there is no way to take one and drag it away on its own and then have them side by side.
    You can do this with just one instance of Excel open.

    - Go to the View tab
    - In just about the middle of that ribbon, will be a button that says "Arrange All" - Click on it
    - Click on the radio button for vertical (the default is horizontal, I believe)
    - Click on okay
    rconverse is offline   Reply With Quote
    Old 12-01-2017, 01:34 PM   #260
    drop2d
     
    drop2d's Avatar
     
    Join Date: Dec 2003
    Location: NH
    Posts: 34,074

    Shows Seen: 32

    DMB Hub Stubs: 17

    My Tour Central Stats

    Re: Excel Help

    Bat signal for rconverse....

    Any idea how I can format a number in vba to have leading 0's? For example, I have employee Id numbers that go from:

    1
    99999999

    I want all of them to be formatted at 8 characters so they would show as:

    00000001
    00000002
    00000003
    etc.

    I'm going blind searching the interwebs and trying different shit that just isn't working. It's probably something simple but after 12 hours of work, I'm spent. Any help would be appreciated!!
    __________________
    Quote:
    Originally Posted by drakan View Post
    I am not a big Cuomo guy.
    Quote:
    Originally Posted by drakan View Post
    Cuomo for president.
    drop2d is offline   Reply With Quote
    Old 12-01-2017, 02:12 PM   #261
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Excel Help

    Quote:
    Originally Posted by drop2d View Post
    Bat signal for rconverse....



    Any idea how I can format a number in vba to have leading 0's? For example, I have employee Id numbers that go from:



    1

    99999999



    I want all of them to be formatted at 8 characters so they would show as:



    00000001

    00000002

    00000003

    etc.



    I'm going blind searching the interwebs and trying different shit that just isn't working. It's probably something simple but after 12 hours of work, I'm spent. Any help would be appreciated!!


    Format cell
    Custom
    00000000

    Should work for ya

    Edit: get rid of the octothorpe I had on the end. The custom format should just be 8 0’s
    __________________
    Tape Cover Artwork

    Last edited by daveshookme; 12-01-2017 at 02:15 PM.
    daveshookme is offline   Reply With Quote
    Old 12-01-2017, 02:20 PM   #262
    Climb2safety
    Free your mind
     
    Join Date: Feb 2014
    Posts: 32,897

    Shows Seen: 0

    DMB Hub Stubs: 1

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    You can do this with just one instance of Excel open.

    - Go to the View tab
    - In just about the middle of that ribbon, will be a button that says "Arrange All" - Click on it
    - Click on the radio button for vertical (the default is horizontal, I believe)
    - Click on okay
    Does this result in two distinct versions of excel now running, independently one with each spreadsheet? Or just a way to manipulate both inside one version?
    __________________

    This town is nuts, my kind of place. I don't never ever want to leave....
    Climb2safety is offline   Reply With Quote
    Old 12-01-2017, 03:48 PM   #263
    drop2d
     
    drop2d's Avatar
     
    Join Date: Dec 2003
    Location: NH
    Posts: 34,074

    Shows Seen: 32

    DMB Hub Stubs: 17

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by daveshookme View Post
    Format cell
    Custom
    00000000

    Should work for ya

    Edit: get rid of the octothorpe I had on the end. The custom format should just be 8 0’s
    Thanks... I do know how to do that. I’m looking for a way to do it in vba so I can process it in the giant macro I’m already running on the data. I have a customer changing systems and the data they are sending isn’t formatted the same (old system had 8 characters). I’m sure it’s something relatively easy... my brain is just fried for today
    __________________
    Quote:
    Originally Posted by drakan View Post
    I am not a big Cuomo guy.
    Quote:
    Originally Posted by drakan View Post
    Cuomo for president.
    drop2d is offline   Reply With Quote
    Old 12-01-2017, 09:27 PM   #264
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by drop2d View Post
    Bat signal for rconverse....

    Any idea how I can format a number in vba to have leading 0's? For example, I have employee Id numbers that go from:

    1
    99999999

    I want all of them to be formatted at 8 characters so they would show as:

    00000001
    00000002
    00000003
    etc.

    I'm going blind searching the interwebs and trying different shit that just isn't working. It's probably something simple but after 12 hours of work, I'm spent. Any help would be appreciated!!
    Not for nothing but please don't research for that much time unless completely necessary. The bold portion of your post is like nails on a chalkboard in my "Excel World."

    I have a lot of instances where I need to maintain leading zeroes and the REPT function always seems to do the trick.

    I"m not exactly sure how your macro is setup but here is how the REPT function works:

    Assuming your data is in column B and you have a header row of 1, meaning your data starts in row 2. You could insert this column in column A.

    =REPT(0, 8-LEN(B2)) & B2

    This formula will ensure that all IDs have a length of 8, regardless of leading zeroes or not. If the ID is a length of 8 characters, there will be no zeroes added.

    So let's say B2 = 1, this will return 00000001
    So let's say B3 = 111, this will return 00000111
    So let's say B4 = 1111111, this will return 1111111

    If you need help inserting that into VBA, please let me know. Happy to help.

    Quote:
    Originally Posted by Climb2safety View Post
    Does this result in two distinct versions of excel now running, independently one with each spreadsheet? Or just a way to manipulate both inside one version?
    It will literally take how many instance of Excel you have open and align them all vertically. If you have four different spreadsheets open, you'll see "squished" versions of all 4 spreadsheets. There's also the option to sync scrolling if you go this route, which is nice if you're comparing two spreadsheets (like this week to last week or test to prod). Literally just depends on what you're trying to accomplish.

    Quote:
    Originally Posted by drop2d View Post
    Thanks... I do know how to do that. I’m looking for a way to do it in vba so I can process it in the giant macro I’m already running on the data. I have a customer changing systems and the data they are sending isn’t formatted the same (old system had 8 characters). I’m sure it’s something relatively easy... my brain is just fried for today
    Not "relatively" easy, buddy. Just easy, easy.

    Give your brain a rest and enjoy your weekend!!
    rconverse is offline   Reply With Quote
    Old 12-01-2017, 09:57 PM   #265
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by drop2d View Post
    Thanks... I do know how to do that. I’m looking for a way to do it in vba so I can process it in the giant macro I’m already running on the data. I have a customer changing systems and the data they are sending isn’t formatted the same (old system had 8 characters). I’m sure it’s something relatively easy... my brain is just fried for today
    This is how you access that function via VBA:

    Code:
    application.WorksheetFunction.Rept
    But again, if you need help inserting this into VBA, please let me know. Happy to help.
    rconverse is offline   Reply With Quote
    Old 12-02-2017, 03:31 AM   #266
    drop2d
     
    drop2d's Avatar
     
    Join Date: Dec 2003
    Location: NH
    Posts: 34,074

    Shows Seen: 32

    DMB Hub Stubs: 17

    My Tour Central Stats

    Re: Excel Help

    Thanks man! Definitely was NOT working on that 1 thing for that long. It was just the last thing of a long week that was tripping me up. I had just solved another stupid simple thing with this macro so I should’ve just quit for the day. I kept getting an ‘overflow’ error and it was bc I had variables declared as integers and since the sum of those went way the fuck up with the new file, they had to be changed to Long.

    I’m relatively new to vba coding and I end up getting stuck on really easy shit sometimes. The only upside to that is that I’m not going to forget in the future!
    __________________
    Quote:
    Originally Posted by drakan View Post
    I am not a big Cuomo guy.
    Quote:
    Originally Posted by drakan View Post
    Cuomo for president.
    drop2d is offline   Reply With Quote
    Old 12-02-2017, 08:10 AM   #267
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    I apologize if that post came off as super douchey. That's not how it was intended. I've just beaten my head against the wall so many times wrestling with Excel stuff that I truly like to help others avoid the headache(s). I've dealt with declaring variables wrong before as well and you'll be testing and everything is fine. All of a sudden, the numbers get bigger, you get your error, and you're like WTF? Everything was working a second ago!?!?!
    rconverse is offline   Reply With Quote
    Old 12-02-2017, 09:14 AM   #268
    Climb2safety
    Free your mind
     
    Join Date: Feb 2014
    Posts: 32,897

    Shows Seen: 0

    DMB Hub Stubs: 1

    My Tour Central Stats

    Re: Excel Help

    Thanks Roger. What I'm looking for though is when I open a new spreadsheet to have them all in their unique version of excel. Not all open in one version.

    I get an email with a spreadsheet, I open it. Then I look for my last one and it's hidden behind the one I just opened.

    I'm really surprised this can't be done.
    __________________

    This town is nuts, my kind of place. I don't never ever want to leave....
    Climb2safety is offline   Reply With Quote
    Old 12-02-2017, 10:26 AM   #269
    drop2d
     
    drop2d's Avatar
     
    Join Date: Dec 2003
    Location: NH
    Posts: 34,074

    Shows Seen: 32

    DMB Hub Stubs: 17

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    I apologize if that post came off as super douchey. That's not how it was intended. I've just beaten my head against the wall so many times wrestling with Excel stuff that I truly like to help others avoid the headache(s). I've dealt with declaring variables wrong before as well and you'll be testing and everything is fine. All of a sudden, the numbers get bigger, you get your error, and you're like WTF? Everything was working a second ago!?!?!
    No worries man... did not take it as douchey. I’ve chased stuff for hours before. The older I get though, the more I realize I just need to get away from it for a while, clear my head and try again later
    __________________
    Quote:
    Originally Posted by drakan View Post
    I am not a big Cuomo guy.
    Quote:
    Originally Posted by drakan View Post
    Cuomo for president.
    drop2d is offline   Reply With Quote
    Old 12-03-2017, 06:31 PM   #270
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by drop2d View Post
    No worries man... did not take it as douchey. I’ve chased stuff for hours before. The older I get though, the more I realize I just need to get away from it for a while, clear my head and try again later
    So much this. Sometimes it really can be one of those things where you're trying to remember something and can't, so you stop trying and then it instantly comes to you. So odd but whatever works!
    rconverse is offline   Reply With Quote
    Reply

    Tags
    excel

    Thread Tools
    Display Modes

    Posting Rules
    You may not post new threads
    You may not post replies
    You may not post attachments
    You may not edit your posts

    BB code is On
    Smilies are On
    [IMG] code is Off
    HTML code is Off

    Forum Jump


    Want to hide all ads on Ants? Click here

    All times are GMT -7. The time now is 01:56 AM.


    Powered by vBulletin® Version 3.8.14
    Copyright ©2000 - 2024, vBulletin Solutions Inc.


       
    Site LinksAbout AntsAnts MobileTweet Tweet
    Home
    Ants+
    Tour Central
    Search bar
    RSS Feeds
    About Us
    Contact Us
    The Ants Blog
    Advertise on Ants
    Privacy Policy
    Ants on your cell phone
    iAnts
    mobile news
    mobile setlists
    antslive!
    Ants' Twitter
    DMBLive Twitter
    Ants Facebook
    Ants Instagram