Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »

Cast_No_Shadow: Jun 8, 2010; The Republic of Luna Equestria is a huge, socially progressive nation, notable for its punitive income tax rates. Its compassionate, cynical population of 714m are ruled with an iron fist by the dictatorship government, which ensures that no-one outside the party gets too rich.

Old James posted:

This looks to be the problem

sDate = Format(DateSerial(sYear, sMonth, sYear), "DD-MM-YY")

change it to

sDate = Format(DateSerial(sYear, sMonth, sDay), "DD-MM-YY")

Thats just me being dumb writing it out quickly on here the actual code just has the functions to the pull the data rather than "sYear" etc and I double checked that.

ShimaTetsuo I was thinking I was being just plain dumb for a day. Figures its excel being retarded. I guess I'll just have to do the old work out days since 1st jan 1900 routine and let excel parse it however it wants.

Thanks for the help guys.

# ? Sep 16, 2013 15:52

Adbot: ADBOT LOVES YOU

# ? May 26, 2024 22:46

khazar sansculotte: May 14, 2004

I've got a table with a bunch of columns of numbers (ascending order, if that matters), and a separate number above each table. I am picking a number from each column to multiply that number above the column, and putting the result below the table. I am then adding up all those products. Something like this:

code:

  |   A   |   B   |   C   |   D   |
1 |
2 |  20      15      12      10
3 |
4 | 100      60      25      10
5 | 150     120     100      75 
6 | 200     150     130     100
7 | 250     220     200     165
8 | 300     270     240     210
9 |
10| =A2*A5 =B2*B6  =C2*C6  =D2*D7

What I'd like to do is quickly change the A5, B6, C6, and D7 without having to manually change every formula. Something like bolding or italicizing the cells I actually want A2, B2, C2, and D2 to be multiplied by. Choosing the first cell within a column greater than X would also be helpful. Is this possible without writing a crazy macro?

# ? Sep 16, 2013 15:57

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Ronald McReagan posted:

What I'd like to do is quickly change the A5, B6, C6, and D7 without having to manually change every formula. Something like bolding or italicizing the cells I actually want A2, B2, C2, and D2 to be multiplied by. Choosing the first cell within a column greater than X would also be helpful. Is this possible without writing a crazy macro?

If you want it to multiply by the largest value in the column use
=A2*large(A4:A8,1)
For second largest
=A2*large(A4:A8,2)
For the smallest value
=A2*small(A4:A8,1)

# ? Sep 16, 2013 16:55

docbeard: Jul 19, 2011

Ronald McReagan posted:

I've got a table with a bunch of columns of numbers (ascending order, if that matters), and a separate number above each table. I am picking a number from each column to multiply that number above the column, and putting the result below the table. I am then adding up all those products. Something like this:
code:
  |   A   |   B   |   C   |   D   |
1 |
2 |  20      15      12      10
3 |
4 | 100      60      25      10
5 | 150     120     100      75 
6 | 200     150     130     100
7 | 250     220     200     165
8 | 300     270     240     210
9 |
10| =A2*A5 =B2*B6  =C2*C6  =D2*D7
What I'd like to do is quickly change the A5, B6, C6, and D7 without having to manually change every formula. Something like bolding or italicizing the cells I actually want A2, B2, C2, and D2 to be multiplied by. Choosing the first cell within a column greater than X would also be helpful. Is this possible without writing a crazy macro?

I don't think it's possible for Excel to detect whether a cell is bolded or italicized or whatever without using VBA, no. If you want to designate an arbitrary cell to multiply each value by, you could do something like:

code:

  |   A   |   B   |   C   |   D   |
1 |
2 |  20      15      12      10
3 |
4 | 100      60      25      10
5 | 150     120     100      75 
6 | 200     150     130     100
7 | 250     220     200     165
8 | 300     270     240     210
9 |
10| A5      B6      C6      D7

And then have your multipication formula be (for column A):

=A2 * INDIRECT(A10)

Then you could change the references in row 10 to whatever you like.

If you specifically want to select the first cell in a column greater than X, you can use the MATCH function. You'd need to put your values in descending order, though, so:

code:

Your formula would be (assuming the value in row 10 is X):

=OFFSET(A3,MATCH(A10,A4:A8,-1),0)

Match would return the position of the cell that's greater than or equal to the value in A10 (in this case 3), and the offset function looks for a cell that's that many rows down from A3.

# ? Sep 16, 2013 17:28

khazar sansculotte: May 14, 2004

I think using INDIRECT should speed things up pretty nicely here without being too complicated. Thanks to both of you for the variety of ideas!

# ? Sep 17, 2013 22:47

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Ronald McReagan posted:

I think using INDIRECT should speed things up pretty nicely here without being too complicated. Thanks to both of you for the variety of ideas!

INDIRECT() and OFFSET() are volatile functions, meaning they need to be recalculated anytime something in the workbook changes even if it is unrelated to that function. So extensive use of them will slow down your spreadsheet.

http://www.decisionmodels.com/calcsecretsi.htm

With the increased horsepower of PCs it may not be much of an issue, but be aware in case you start experiencing a noticeable slowdown.

# ? Sep 18, 2013 00:15

RICHUNCLEPENNYBAGS: Dec 21, 2010

celestial teapot posted:

Let me know if I should keep posting these.

I dig them.

It's also neat because I've implemented UDFs pretty similar to some of the ones you're posting so it's interesting to compare notes.

# ? Sep 18, 2013 04:40

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

I'd like to see what have too. Maybe I'll just drop all my stuff into a github repo. Probably need to anyhow, based on past experience.

# ? Sep 18, 2013 20:35

AcidRonin: Apr 2, 2012; iM A ROOKiE RiGHT NOW BUT i PROMiSE YOU EVERY SiNGLE FUCKiN BiTCH ASS ARTiST WHO TRiES TO SHADE ME i WiLL VERBALLY DiSMANTLE YOUR ASSHOLE

If i have a giant spreadsheet with 2 columns, employee Name, and Pet's name for instance. Is there a way to script inserting each employees Pet's name into a thus far blank <pets name> filed in Active directory without importing them all as new users/wiping all the other data? Assuming the names match up of course?

# ? Sep 23, 2013 20:06

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

AcidRonin posted:

If i have a giant spreadsheet with 2 columns, employee Name, and Pet's name for instance. Is there a way to script inserting each employees Pet's name into a thus far blank <pets name> filed in Active directory without importing them all as new users/wiping all the other data? Assuming the names match up of course?

I am not sure I understand what you need. You want a macro to insert the pet_name into... <pets name> :confused:

You mean another spreadsheet?

celestial teapot fucked around with this message at 18:11 on Sep 24, 2013

# ? Sep 24, 2013 18:08

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

There's probably an easier way to do this but I just knocked this out so that Excel would stop helpfully loving up my date values before exporting them to my real RDBMS:

code:

Public Function date2text(haystack As range, Optional newformat As String = "YYYY-MM-DD")
 '---------------------------------------------------------------------------------------
 ' Copyright : 2013-09-23
 ' Author    : Ryan Delaney
 ' Purpose   : Replace Excel date cell values with formatted strings
 ' License   : MIT License [url]http://opensource.org/licenses/MIT[/url]
 '---------------------------------------------------------------------------------------
    
    Dim proc_name As String
    proc_name = "date2text"
    On Error GoTo ErrorHandler
    
    '''''''''''''''''''
    'Variables        '
    '''''''''''''''''''
    Dim c As range
    
    '''''''''''''''''''
    'Declarations     '
    '''''''''''''''''''
    
    '''''''''''''''''''
    'Action           '
    '''''''''''''''''''
    For Each c In haystack
            If IsDate(c.Value) Then c.Value = Format(c.Value, newformat)
    Next c
    
Endgame:
    Exit Function       'Exit before error handler
ErrorHandler:
    MsgBox "There was an unhandled exception in function " _
              & proc_name & _
              ". Source: " & Err.Source & _
              "Error number: " & Err.Number & _
              " Description: " & Err.Description
    Resume Endgame
End Function

Edit: only reformat the cell if it's a date

celestial teapot fucked around with this message at 18:13 on Sep 24, 2013

# ? Sep 24, 2013 18:10

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

celestial teapot posted:

There's probably an easier way to do this but I just knocked this out so that Excel would stop helpfully loving up my date values before exporting them to my real RDBMS:

Edit: only reformat the cell if it's a date

code:

Public Function date2text(haystack As Range, Optional newformat As String = "YYYY-MM-DD") As String
 '---------------------------------------------------------------------------------------
 ' Copyright : 2013-09-23
 ' Author    : Ryan Delaney
 ' Purpose   : Replace Excel date cell values with formatted strings
 ' License   : MIT License [url]http://opensource.org/licenses/MIT[/url]
 '---------------------------------------------------------------------------------------
    
    Dim proc_name As String
    Dim date_str As String
    proc_name = "date2text"
    On Error GoTo ErrorHandler
    
    '''''''''''''''''''
    'Variables        '
    '''''''''''''''''''
    Dim c As Range
    
    '''''''''''''''''''
    'Declarations     '
    '''''''''''''''''''
    
    '''''''''''''''''''
    'Action           '
    '''''''''''''''''''
    For Each c In haystack
            If IsDate(c.Value) Then date_str = Format(c.Value, newformat)
    Next c
    
    date2text = date_str
Endgame:
    Exit Function       'Exit before error handler
ErrorHandler:
    MsgBox "There was an unhandled exception in function " _
              & proc_name & _
              ". Source: " & Err.Source & _
              "Error number: " & Err.Number & _
              " Description: " & Err.Description
    Resume Endgame
End Function

It threw an error because you are trying to change the value of another cell, you can't do that in a Function but can in a Sub.

# ? Sep 24, 2013 18:49

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

Old James posted:

It threw an error because you are trying to change the value of another cell, you can't do that in a Function but can in a Sub.

Are you sure? It works fine for me when called from another procedure. Make sure you specify the worksheet too, eg: Call date2text(ActiveSheet.Range("D5:D17"))

I guess there's no reason for it to be a function though since it doesn't return anything.

# ? Sep 24, 2013 19:10

Falcon2001: Oct 10, 2004; Eat your hamburgers, Apollo.; Pillbug

Sooooo, I'm trying to figure out how to figure out, given a series of tickets with start and end times, how many were active on a given hour (in a way that I could graph and report programatically).

For instance, I can pull ticket data like such:

code:

ticketid    start time     end time
-----------------------------------
0001        9/1/13 00:00   9/1/13 00:30
0002        9/1/13 00:01   9/4/13 00:30
0003        9/2/13 06:45   9/5/13 07:00

And I'd preferably like to have something like this come out the other end

code:

date         hour         active count
--------------------------------------
09/1/13      0000         2

And while I logically understand what I'm trying to find, I have no idea how to parse for it in excel. Help me goons!

edit: actually, as written I know how to do that, I think I'm just trying to figure out a way to do it that doesn't make a huge table of static dates.

Falcon2001 fucked around with this message at 21:10 on Sep 24, 2013

# ? Sep 24, 2013 21:04

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

celestial teapot posted:

Are you sure? It works fine for me when called from another procedure. Make sure you specify the worksheet too, eg: Call date2text(ActiveSheet.Range("D5:D17"))

I guess there's no reason for it to be a function though since it doesn't return anything.

It complained at "c.Value = Format(c.Value, newformat)", the changes above got it working for me. Maybe it is something to do with different versions of Excel since it works for you.

# ? Sep 24, 2013 21:31

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Falcon2001 posted:

Sooooo, I'm trying to figure out how to figure out, given a series of tickets with start and end times, how many were active on a given hour (in a way that I could graph and report programatically).

For instance, I can pull ticket data like such:
code:
ticketid    start time     end time
-----------------------------------
0001        9/1/13 00:00   9/1/13 00:30
0002        9/1/13 00:01   9/4/13 00:30
0003        9/2/13 06:45   9/5/13 07:00
And I'd preferably like to have something like this come out the other end
code:
date         hour         active count
--------------------------------------
09/1/13      0000         2
And while I logically understand what I'm trying to find, I have no idea how to parse for it in excel. Help me goons!

edit: actually, as written I know how to do that, I think I'm just trying to figure out a way to do it that doesn't make a huge table of static dates.

First off, in your example there should only be 1 active ticket at 9/1/13 0:00 right? Because the second ticket starts at 0:01.

For readability, I'm assuming you name your ranges from the first table StartTimeRange and EndTimeRange. Then create a list of all the hours you want to report against, let's say in Column F, first value in row 2. Then a formula like

=COUNTIFS(StartTimeRange, "<=F2", EndTimeRange, ">=F2")

Should give you the number of tickets where F2 falls in between those times.

vvv

You'll want to normalize the ticket start and end times to just the closest hour then.

ZerodotJander fucked around with this message at 22:19 on Sep 24, 2013

# ? Sep 24, 2013 21:54

Falcon2001: Oct 10, 2004; Eat your hamburgers, Apollo.; Pillbug

ZerodotJander posted:

First off, in your example there should only be 1 active ticket at 9/1/13 0:00 right? Because the second ticket starts at 0:01.

For readability, I'm assuming you name your ranges from the first table StartTimeRange and EndTimeRange. Then create a list of all the hours you want to report against, let's say in Column F, first value in row 2. Then a formula like

=COUNTIFS(StartTimeRange, "<=F2", EndTimeRange, ">=F2")

Should give you the number of tickets where F2 falls in between those times.

Err, meant grouped by hour, rather than by minute. Thanks! I'll check it out.

# ? Sep 24, 2013 22:04

Ragingsheep: Nov 7, 2009

Is there a way to have charts with relative referencing?

# ? Sep 24, 2013 22:44

Falcon2001: Oct 10, 2004; Eat your hamburgers, Apollo.; Pillbug

Falcon2001 posted:

Err, meant grouped by hour, rather than by minute. Thanks! I'll check it out.

I think that Excel is choking on the date portion of this. If I use the following:

=COUNTIF(table[columnname], ">A2") (where A2 is 1/1/2013), I get zero results. But if I do:

=COUNTIF(table[columnname], ">1/1/2013")

I get the results I want. Any idea wtf?

EDIT: =COUNTIF(Table[Column], ">"&A2) works, although if I want to do a datetime by combining a given day and time, I have no idea how to do that.

DOUBLE EDIT: HA HA I figured it out, I was typo'ing that. Thanks!

Falcon2001 fucked around with this message at 22:50 on Sep 24, 2013

# ? Sep 24, 2013 22:44

AcidRonin: Apr 2, 2012; iM A ROOKiE RiGHT NOW BUT i PROMiSE YOU EVERY SiNGLE FUCKiN BiTCH ASS ARTiST WHO TRiES TO SHADE ME i WiLL VERBALLY DiSMANTLE YOUR ASSHOLE

celestial teapot posted:

I am not sure I understand what you need. You want a macro to insert the pet_name into... <pets name> You mean another spreadsheet?

No into Active directory. Basically my IT department finds it hard to belive that you would have to hand-jam 2k some entires into AD for a new data field on the users, so they asked me if i could "I dont know, code something?" but i know next to nothing about AD/Excel.

# ? Sep 25, 2013 12:30

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

AcidRonin posted:

No into Active directory. Basically my IT department finds it hard to belive that you would have to hand-jam 2k some entires into AD for a new data field on the users, so they asked me if i could "I dont know, code something?" but i know next to nothing about AD/Excel.

We could probably help with formatting text in Excel, but it's the process of moving it from there to ActiveDirectory that is probably outside the realms of this thread.

But, a google search found this post about adding ActiveDirectory entries based off of data in a CSV file. So save your Excel as CSV and then try adapting this code.

http://gallery.technet.microsoft.com/office/AD-and-mailbox-from-CSV-96a4713f

# ? Sep 25, 2013 15:24

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

Old James posted:

We could probably help with formatting text in Excel, but it's the process of moving it from there to ActiveDirectory that is probably outside the realms of this thread.

Yeah, this. I don't know jack poo poo about Active Directory. But if you have ugly user-entered data you need to get into a particular format, someone in here will be able to walk you through that part for sure. Post up a few sample rows of what it looks like vs what you want it to look like and we'll help you get it done in Excel.

# ? Sep 25, 2013 17:21

AcidRonin: Apr 2, 2012; iM A ROOKiE RiGHT NOW BUT i PROMiSE YOU EVERY SiNGLE FUCKiN BiTCH ASS ARTiST WHO TRiES TO SHADE ME i WiLL VERBALLY DiSMANTLE YOUR ASSHOLE

celestial teapot posted:

Yeah, this. I don't know jack poo poo about Active Directory. But if you have ugly user-entered data you need to get into a particular format, someone in here will be able to walk you through that part for sure. Post up a few sample rows of what it looks like vs what you want it to look like and we'll help you get it done in Excel.

Nah the spreadsheet is fine, but i appreciate the direction, I should really take the time to learn excel at somepoint.

# ? Sep 25, 2013 19:38

Zorak of Michigan: Jun 10, 2006

There are Powershell functions for both Excel and AD, so you could probably write the thing in Powershell. I just don't know what those functions are.

# ? Sep 25, 2013 20:59

Total Meatlove: Jan 28, 2007; Rangers died, shoujo Hitler cried ;_;

My colleague has somehow managed to get his copy of Excel 03 to insert a ^ at the start of every cell in any spreadsheet he opens. What has he done and can it be switched off?

# ? Sep 26, 2013 10:32

Knot My President!: Jan 10, 2005

Hey everyone! When using Excel 2010, I'm looking to do a specific data entry that won't make me want to kill myself after: (I'm new to the program so please don't hate me if this is common knowledge :ohdear:

)

- I have a column of zip codes in one data sheet as part of a 300-respondent survey
- I want to add columns next to this column of zip codes that references the USDA's 2013 Rural-Urban continuum codes from the spreadsheet listed on this page

Basically, I want the USDA's data, based on whatever zip code respondents entered, to be put alongside their response. In other words, if somebody says they're "01001" in my data sheet, next to their answer is AL, Autauga County, 54,571, 2, and Metro (all in their own respective columns)

Is this possible to do? Thanks in advance! :sun:

# ? Sep 30, 2013 23:13

Ragingsheep: Nov 7, 2009

On my phone but: use vlookup or the more flexible index and match to match the value from one column to data from another.

# ? Sep 30, 2013 23:23

Knot My President!: Jan 10, 2005

I'm kind of an Excel idiot.

One spreadsheet has FIPS, county, and state. Another spreadsheet has ZIP, city, county, and state. I wish to create a column for ZIP in the FIPS spreadsheet that lists all the ZIP codes located within each county. Also, the issue here is that there are similarly named counties in various states, so I need it to match state to county as in its search as well.

I hope this makes sense :goleft:

# ? Oct 1, 2013 02:03

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Xovaan posted:

I'm kind of an Excel idiot.

One spreadsheet has FIPS, county, and state. Another spreadsheet has ZIP, city, county, and state. I wish to create a column for ZIP in the FIPS spreadsheet that lists all the ZIP codes located within each county. Also, the issue here is that there are similarly named counties in various states, so I need it to match state to county as in its search as well.

I hope this makes sense

To make things simple we'll stick with the VLookup() function. This function has four parameters: The value you want to match, the array (rectangle of cells) you are comparing it against, the column from the array you want to return, and finally if you want it to return values when it finds an exact match to your search or a close match.

VLookup() always looks for a match in the first column of the array you ask it to search. So on both worksheets insert a new column before column A. This blank column will now be column A and everything else shifted over to the right. Assuming City and State are now in columns C & D, we will add the following formula in column A to concatenate (join) them into a single cell.

code:

=C1&", "&D1

Now in the first blank cell of column E (or the first empty column) use the formula

code:

=vlookup(A1,Sheet2!A:D,2,false)

This will look on Sheet2 for the first exact match to your city and state and return the value in column B. If there is no exact match it will return an error.

http://www.techonthenet.com/excel/formulas/vlookup.php

# ? Oct 1, 2013 02:59

Knot My President!: Jan 10, 2005

Worked like a charm! Thanks!

I assume I just copy the worksheets into my respondent worksheet then copy over the formulas to report on pop/density once respondents give me their zip codes? Man I'll be an Excel master at this rate. :madmax:

# ? Oct 1, 2013 18:34

Knot My President!: Jan 10, 2005

For some reason my work computer is not showing any forum buttons or images so I cannot find a way to edit. Apologies on the double post here.

Next thing I need to do is split some names that are conjoined. They look like RootWalla or MaynardKeynes and I need them to be Root Walla/Maynard Keynes. I found a post here that explains exactly what I need to do. Problem? I've never, ever used VBA or macros before. They give a code to use, but what's the process to get it to run in the workbook for, say, column A?

Thanks in advance! This is all very helpful. :sun:

# ? Oct 1, 2013 22:35

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

Xovaan posted:

For some reason my work computer is not showing any forum buttons or images so I cannot find a way to edit. Apologies on the double post here.

Next thing I need to do is split some names that are conjoined. They look like RootWalla or MaynardKeynes and I need them to be Root Walla/Maynard Keynes. I found a post here that explains exactly what I need to do. Problem? I've never, ever used VBA or macros before. They give a code to use, but what's the process to get it to run in the workbook for, say, column A?

Thanks in advance! This is all very helpful.

It would be better to separate firstname and surname into separate columns. It's always easier to join data back together than to break it apart, as you're discovering here. Your URL is broken though: it just links back to this thread. Something like this might work for you, assuming there are only two uppercase characters in every cell:

code:

Public Function rowsCount(ws As worksheet) As Long
'
' Returns the count of rows in the worksheet
'

    Dim proc_name As String
    proc_name = "rowsCount"
    On Error GoTo ErrorHandler
    
    rowsCount = ws.Cells(rows.count, 1).End(xlUp).Row
    
Endgame:
    Exit Function       'Exit before error handler
ErrorHandler:
    MsgBox "There was an unhandled exception in function " _
              & proc_name & _
              ". Source: " & Err.Source & _
              "Error number: " & Err.Number & _
              " Description: " & Err.Description
    Resume Endgame
End Function

Function unfucknames(Optional inputCol As Integer = 1, _
                     Optional firstnameCol As Integer = 2, _
                     Optional lastnameCol As Integer = 3)
 
    Dim proc_name As String
    proc_name = "unfucknames"
    On Error GoTo ErrorHandler
    
    '''''''''''''''''''
    'Variables        '
    '''''''''''''''''''
    Dim ws As worksheet
    Dim stringy As String
    Dim chr As String
    Dim breakpoint As Integer
    Dim c As Long
    Dim r As Long
    Dim rows As Long
    
    '''''''''''''''''''
    'Declarations     '
    '''''''''''''''''''
    Set ws = ActiveSheet
    rows = rowsCount(ws)
    
    '''''''''''''''''''
    'Action           '
    '''''''''''''''''''
    For r = 1 To rows
        stringy = Cells(r, inputCol).Value
        breakpoint = 0
        
        For c = 2 To Len(stringy)
            chr = Mid(stringy, c, 1)
            If UCase(chr) = chr Then breakpoint = c
        Next c
        
        Cells(r, firstnameCol).Value = Mid(stringy, 1, breakpoint - 1)
        Cells(r, lastnameCol).Value = Mid(stringy, breakpoint, Len(stringy))
    Next r
    
    
Endgame:
    Exit Function       'Exit before error handler
ErrorHandler:
    MsgBox "There was an unhandled exception in function " _
              & proc_name & _
              ". Source: " & Err.Source & _
              "Error number: " & Err.Number & _
              " Description: " & Err.Description
    Resume Endgame
End Function

celestial teapot fucked around with this message at 01:36 on Oct 2, 2013

# ? Oct 2, 2013 01:33

Knot My President!: Jan 10, 2005

celestial teapot posted:

code:

Public Function rowsCount(ws As worksheet) As Long
'
' Returns the count of rows in the worksheet
'

    Dim proc_name As String
    proc_name = "rowsCount"
    On Error GoTo ErrorHandler
    
    rowsCount = ws.Cells(rows.count, 1).End(xlUp).Row
    
Endgame:
    Exit Function       'Exit before error handler
ErrorHandler:
    MsgBox "There was an unhandled exception in function " _
              & proc_name & _
              ". Source: " & Err.Source & _
              "Error number: " & Err.Number & _
              " Description: " & Err.Description
    Resume Endgame
End Function

Function unfucknames(Optional inputCol As Integer = 1, _
                     Optional firstnameCol As Integer = 2, _
                     Optional lastnameCol As Integer = 3)
 
    Dim proc_name As String
    proc_name = "unfucknames"
    On Error GoTo ErrorHandler
    
    '''''''''''''''''''
    'Variables        '
    '''''''''''''''''''
    Dim ws As worksheet
    Dim stringy As String
    Dim chr As String
    Dim breakpoint As Integer
    Dim c As Long
    Dim r As Long
    Dim rows As Long
    
    '''''''''''''''''''
    'Declarations     '
    '''''''''''''''''''
    Set ws = ActiveSheet
    rows = rowsCount(ws)
    
    '''''''''''''''''''
    'Action           '
    '''''''''''''''''''
    For r = 1 To rows
        stringy = Cells(r, inputCol).Value
        breakpoint = 0
        
        For c = 2 To Len(stringy)
            chr = Mid(stringy, c, 1)
            If UCase(chr) = chr Then breakpoint = c
        Next c
        
        Cells(r, firstnameCol).Value = Mid(stringy, 1, breakpoint - 1)
        Cells(r, lastnameCol).Value = Mid(stringy, breakpoint, Len(stringy))
    Next r
    
    
Endgame:
    Exit Function       'Exit before error handler
ErrorHandler:
    MsgBox "There was an unhandled exception in function " _
              & proc_name & _
              ". Source: " & Err.Source & _
              "Error number: " & Err.Number & _
              " Description: " & Err.Description
    Resume Endgame
End Function

Nice, thanks for this. I ended up figuring out how to do it via a stack overflow macro I found but I'll try this one out as well.

Yeah, sorry about the link; my work computer doesn't load any graphics from websites for some reason so I couldn't actually get to the edit button to link the macro I was trying to in the first place. :v:

# ? Oct 2, 2013 04:12

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

Total Meatlove posted:

My colleague has somehow managed to get his copy of Excel 03 to insert a ^ at the start of every cell in any spreadsheet he opens. What has he done and can it be switched off?

He's not saving them as Lotus 1-2-3 files or something like that is he?

# ? Oct 4, 2013 20:28

borodino: Jul 31, 2012

I am working with a spreadsheet that has irregular data in a column like this.

code:

1oz 100ct
48ct
18ct
CANDY TUBE 24CT
2.12OZ 18CT
SWIRL 24CT


       24CT
       60CT
24ct
Drink 8.4oz 24ct
Strawberry 0.6oz 12ct
6OZ  12CT
5oz 12ct
Sour Gummies 5oz 12ct
Liquid Filled 5oz 12ct
4.5oz 12ct

I want to make a column with just the "[x]ct" portion when an entry has one.
so get:

code:

100ct
48ct
18ct
24ct
etc

from the above example.

is there anyway to do this?

Only registered members can see post attachments!

borodino fucked around with this message at 09:22 on Oct 7, 2013

# ? Oct 7, 2013 09:07

Raven31: Feb 4, 2006

Put the formula below in column C, starting in row 2.
=IF(RIGHT(B2,2)="ct",TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",99)),99)),"")

This will get you what you wrote in the Excel file picture. It will not get the count for the entries (Laser Pop, Lemonhead) with blanks in column B. The formula also assumes that column B will always have never have non-space characters before the count and that entries you want to keep will end in 'ct' or 'CT'.

# ? Oct 7, 2013 13:37

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

The regexreplace functions posted in the last couple pages are also worth looking at.

# ? Oct 7, 2013 19:11

borodino: Jul 31, 2012

Perfect thanks.

# ? Oct 7, 2013 23:42

gandlethorpe: Aug 16, 2008

A few months ago, I wrote a bunch of javascript bookmarklets to read the DOM of an IE form page and manipulate elements. Currently, I use a bookmarklet to parse the DOM, export it as text to Excel, assign my data to the element IDs, then create another bookmarklet that applies that data (for repeatedly entering identical data). I want to cut out the middleman and have Excel interact directly with the IE page. I'm trying to translate my javascript code to VBA, but it's not as straightforward as I had hoped, and my fluency in VBA is currently not up to par.

Anyway, as a starting example, how would I properly translate this javascript code to mark the first option in a series of radio buttons?

code:

window.frames['C'].document.getElementsByName('27019')[0].checked=true

I tried something like this, but no dice:

code:

Set element = IE.document.frames("C").document.getElementsByName("27019")(0)
element.Checked = True

# ? Oct 8, 2013 01:53

Adbot: ADBOT LOVES YOU

# ? May 26, 2024 22:46

SaxMaverick: Jun 9, 2005; The stuff of nightmares

I'm completely new to VBA, and I've been tasked with updating a set of macros we use at my company.

In a nutshell, we get an output report as a csv file, then start a macro that processes it into something we send out. The macros are within a separate file (we'll call this "formatter"). The goal is to make the output multilingual, based on a userform that comes up. My thought is to have an array in the "formatter" sheet, with languages in cells e2:j2, then from e3:e37 etc will be translated strings that will be put in the output report.

I would like to have a listbox/combobox in the userform that contains the languages (keep in mind, the csv file is the ActiveWorkbook), so that selection of the language will correspond to the column that contains the translated strings. My issue is I can't get the RowSource property of the listbox/combobox to correctly reference the "formatter" workbook and sheet, and I'm not sure how to then define an array (e3 through j37) containing the strings so that the text output can be referenced in the rest of the code.

Sorry if that's not clear, I've powered about halfway through an excel 2007 macro programming book in a day, and I'm still getting used to it.

# ? Oct 8, 2013 17:35

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »