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 »

gwar3k1: Jan 10, 2005; Someday soon

935 posted:

I think this is an easy question. Say I have a workbook and on sheet1 I have a list of students.

Each row is a separate entry in no particular order.

Now say on sheet2 I have a overhead view of the classroom to make a seating chart.

Is there a way to have excel link a desk to one of the rows such that by clicking on a desk, that row in sheet1 is displayed in the highlighted region of sheet2? If not, is there a way to automatically add comments to each desk cell that reflect data from sheet1 (so that a quick mouseover of the desk will display the student and his/her information)?

You would start by adding a column on sheet 1 to define which desk that student sits at. My code assumes you just put the desk number in column F (i.e. not Desk 1, just: 1)

Press Alt+F11 to open the Visual Basic editor. On the left under VBAProject, double click on "Sheet2 (Sheet2)" to open a white page.

Paste this code:

code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Cells.Count = 1 Then                            ' Prevent error on multiple select
    If UCase(Left(Target.Value, 4)) = "DESK" Then           ' Perform lookup on desk selection
      Set ws = Sheets("Sheet1")
      Set wo = Sheets("Sheet2")
    
      iEnd = ws.Range("A65536").End(xlUp).Row               ' Find last row with data in column A
      For i = 2 To iEnd                                     ' Loop all rows on Sheet1
        If ws.Cells(i, 1) <> "" Then                        ' Ignore blank rows
          If ws.Cells(i, 6) = Int(Right(Target.Value, Len(Target.Value) - 5)) Then
            wo.Cells(2, 1) = ws.Cells(i, 1)                 ' Copy details
            wo.Cells(2, 2) = ws.Cells(i, 2)
            wo.Cells(2, 3) = ws.Cells(i, 3)
            wo.Cells(2, 4) = ws.Cells(i, 4)
            wo.Cells(2, 5) = ws.Cells(i, 5)
            Exit Sub
          End If
        End If
      Next
    End If
  End If
End Sub

Then knock yourself out.

# ? Feb 11, 2012 18:11

Adbot: ADBOT LOVES YOU

# ? Apr 28, 2024 06:02

az jan jananam: Sep 6, 2011; HI, I'M HARDCORE SAX HERE TO DROP A NICE JUICY TURD OF A POST FROM UP ON HIGH

I have a list of birthdays that are in columns like this

What would be a formula I can use in another column to turn this data into YYYY-MM-DD format? The leading zeros would be necessary.

# ? Feb 17, 2012 22:03

gwar3k1: Jan 10, 2005; Someday soon

az jan jananam posted:

I have a list of birthdays that are in columns like this

What would be a formula I can use in another column to turn this data into YYYY-MM-DD format? The leading zeros would be necessary.

Check the help for Text and Date functions. You'd use Date to turn H,I and J into a valid date format, then Text to format it how you want.

# ? Feb 17, 2012 22:30

G-Dub: Dec 28, 2004; The Gonz

Cancer Wad posted:

Still working on this pricing table (original summary of project) and learning a ton about Excel and enjoying wading through this stuff. Couple of (hopefully) straightforward questions:

I've opted to attempt doing this via defined names. There's a separate worksheet with a pricing table, and since Excel 2010 seems to automatically define names for the ranges in that table, I guess I'd like to just reference those defined names to obtain pricing.

My form takes user selections on bandwidth and term and populates the proposal worksheet thus:

Is it possible for the pricing to be done by code that references cell contents for a defined name lookup? For example, for E13 take whatever bandwidth has been populated from C13 and the term from E11 and just leverage the named ranges that Excel already has in place?

I know there are plenty of ways to pull that pricing and have tried Application.Vlookup, but these named ranges seem awfully convenient; this works as a formula (= _10M _24Months), but can VBA reference whatever those cells' values are in the current (active) row? If this is a really dumb way to approach, tell me. God, I hope this question makes sense; I'm even shittier at talking about this stuff than I am at doing it. For the record, they didn't hire me to do this or anything, I'm just working on it as a personal project to make my job easier.

I'm not 100% on what you are getting at but yes, you can use named ranges in VBA. I think you probably have access to ThisWorkbook.Range("NamedRangeName") and there is also the .Names collection where you might be able to use .Names("NamedRangeName") however that may be more about definition of named ranges than using the values. I am currently installing Excel so I can't say for sure.

# ? Feb 18, 2012 10:57

Dignity Van Houten: Jul 28, 2006; abcdefghijk
ELLAMENNO-P

gwar3k1 posted:

You would start by

...

Then knock yourself out.

I forgot to come back and tell you how perfectly this worked. Even better, it introduced me to VBA that until now I only had a passing familiarity with. Thanks a lot! I know Excel isn't ideal for floorplans but I can think of a few situations where this code could be applied.

# ? Feb 18, 2012 17:07

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

A coworker asked me to help her fix a spreadsheet she updates which was created by her predecessor. Looking at it I saw some strange syntax in the formulas that I do not understand and Google was not very helpful.

code:

=SUMPRODUCT(--('Raw Data'!$AU$2:$AU$4820=0),--('Raw Data'!$DL$2:$DL$4820<=4))

From the context of where the formula is used it is supposed to function like a COUNTIFS() but I've never seen the "--" before. Could anyone help explain to me what that does?

# ? Feb 22, 2012 15:36

esquilax: Jan 3, 2003

Old James posted:

A coworker asked me to help her fix a spreadsheet she updates which was created by her predecessor. Looking at it I saw some strange syntax in the formulas that I do not understand and Google was not very helpful.
code:
=SUMPRODUCT(--('Raw Data'!$AU$2:$AU$4820=0),--('Raw Data'!$DL$2:$DL$4820<=4))
From the context of where the formula is used it is supposed to function like a COUNTIFS() but I've never seen the "--" before. Could anyone help explain to me what that does?

The stuff inside each parentheses evaluates to TRUE or FALSE. When you put a -- in front of it it multiplies by -1 twice, which turns the TRUE and FALSE into 1 and 0 respectively, which is the necessary syntax for the sumproduct() function. There are other ways to do the same thing but -- works.

# ? Feb 22, 2012 15:50

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

esquilax posted:

The stuff inside each parentheses evaluates to TRUE or FALSE. When you put a -- in front of it it multiplies by -1 twice, which turns the TRUE and FALSE into 1 and 0 respectively, which is the necessary syntax for the sumproduct() function. There are other ways to do the same thing but -- works.

Thanks, that makes some sense. Now that excel has countifs() I think I will stick with that instead, but good to know in case I run into it again.

# ? Feb 22, 2012 15:57

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

Short question: does anyone have a good resource for learning how to put if then statements into macros, specifically looking for certain strings in a cell and deleting that row?

I am building a vba macro to reformat a .csv dump from our internal project tracking system into a more user friendly format. Its all very simple formatting stuff until this part. I have zero VBA experience and no idea how to get logic in there.

I looked for tutorials online but they all seem too high level for what I'm trying to do.

# ? Feb 22, 2012 22:56

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

Xguard86 posted:

Short question: does anyone have a good resource for learning how to put if then statements into macros, specifically looking for certain strings in a cell and deleting that row?

I am building a vba macro to reformat a .csv dump from our internal project tracking system into a more user friendly format. Its all very simple formatting stuff until this part. I have zero VBA experience and no idea how to get logic in there.

I looked for tutorials online but they all seem too high level for what I'm trying to do.

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

This should do what you want. Just change the range("A1") to the actual column with the cells you are looking and and change "xyz" to the criteria.

code:

sub delete_row()
   dim i as long
   dim last as long
   
   last = activesheet.usedrange.rows.count-1

   For i=last to 1 step -1
      if instr(activesheet.range("A1").offset(i,0).value,"xyz")>0 then
         activesheet.range("A1").offset(i,0).entirerow.delete
      end if
   next i
end sub

# ? Feb 22, 2012 23:04

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

sweet thanks, that looks like it will do the trick.

It iterates through the whole column starting at A, right? So I just need (whatever column)1 and it will step through until it hits a blank cell?

Second newbie question, can I just do the same for loop with a different column and string but not change variables for other fields?

Xguard86 fucked around with this message at 19:42 on Feb 23, 2012

# ? Feb 23, 2012 18:37

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

Xguard86 posted:

sweet thanks, that looks like it will do the trick.

It iterates through the whole column starting at A, right? So I just need (whatever column)1 and it will step through until it hits a blank cell?

It runs till it reaches the last used row on the spreadsheet. So if column A stops at row 5, but column D has 200 rows it will continue to run through 150 blank cells in column A.

Xguard86 posted:

Second newbie question, can I just do the same for loop with a different column and string but not change variables for other fields?

Yes, change the range("A1") lines to reference the column you want to check and change "xyz" to your search sting (this is case sensitive).

This same process can be done with the .Find and .Findnext properties, which can ignore case and will run faster. However, if you are looking to teach yourself VBA wait on that until you feel a little more comfortable.

# ? Feb 24, 2012 15:28

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

Here's what I've got, three loops on the same column but different strings. Is there any low hanging fruit style things I can do to optimize this? Its only 1200 or so rows so it can run like a dog and not matter, but I like doing things right.

code:

Sub delete_row()
   Dim i As Long
   Dim last As Long
   
   last = ActiveSheet.UsedRange.Rows.Count - 1

   For i = last To 1 Step -1
      If InStr(ActiveSheet.Range("F1").Offset(i, 0).Value, "CarrierSpecs") > 0 Then
         ActiveSheet.Range("A1").Offset(i, 0).EntireRow.Delete
      End If
   Next i
   last = ActiveSheet.UsedRange.Rows.Count - 1

   For i = last To 1 Step -1
      If InStr(ActiveSheet.Range("F1").Offset(i, 0).Value, "EACopy") > 0 Then
         ActiveSheet.Range("A1").Offset(i, 0).EntireRow.Delete
      End If
   Next i
   For i = last To 1 Step -1
      If InStr(ActiveSheet.Range("F1").Offset(i, 0).Value, "Export") > 0 Then
         ActiveSheet.Range("A1").Offset(i, 0).EntireRow.Delete
      End If
   Next i
End Sub

# ? Feb 24, 2012 17:56

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

You can do all 3 checks on the same pass through the text like so.

code:

Sub delete_row()
   Dim i As Long
   Dim last As Long
   
   last = ActiveSheet.UsedRange.Rows.Count - 1

   For i = last To 1 Step -1
      If InStr(ActiveSheet.Range("F1").Offset(i, 0).Value, "CarrierSpecs") > 0 _
      OR InStr(ActiveSheet.Range("F1").Offset(i, 0).Value, "EACopy") > 0 _
      OR If InStr(ActiveSheet.Range("F1").Offset(i, 0).Value, "Export") > 0 Then
         ActiveSheet.Range("A1").Offset(i, 0).EntireRow.Delete
      End If
   Next i
End Sub

The underscore is there so I don't break tables with this code. It tells VB that there is a breakline and to continue reading the next line as part of the same string of code.

# ? Feb 24, 2012 18:26

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

Excellent, I knew I could close that up.

...and another stumbling block.

My most recent code, I've tried many different versions:

code:

Worksheets("Macro_test").Range("A2").Sort _
        Key1:=Worksheets("Macro_test").Range("G2"), _
        Key2:=Worksheets("Macro_test").Range("H2")
        Header = Range("A1:X1")

I want to sort everything on columns G and H but I have headers in row 1. I can't get it to stop including those rows in the sort. This should be simple, I don't know what I'm putting in wrong. Can you use a specific range for Header or can it only be that xlYes No Guess stuff?

Xguard86 fucked around with this message at 18:36 on Feb 24, 2012

# ? Feb 24, 2012 18:33

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

Looks like you are using older syntax to sort. The way you are doing it limits you to 3 keys, but it is compatible with older versions of Excel.

You need to include the entire range in your sort, not just A2. Also, you were missing the order for each key otherwise Excel doesn't know if you want A->Z or Z->A. Finally, you are limited to the options xlYes/xlNo/xlGuess for the header.

code:

Worksheets("Macro_test").Range("A:H").Sort _
        Key1:=Range("G1"), _
        Order1:=xlAscending, _
        Key2:=Range("H1"), _
        Order2:=xlAscending, _
        Header:= xlYes

The header will always be the first row of the sort range. So in the case below it will make row 1 the header row. If the table is in A5:H500 then you can do either ...

code:

Worksheets("Macro_test").Range("A5:H500").Sort _
        Key1:=Range("G5"), _
        Order1:=xlAscending, _
        Key2:=Range("H5"), _
        Order2:=xlAscending, _
        Header:= xlYes

code:

Worksheets("Macro_test").Range("A6:H500").Sort _
        Key1:=Range("G6"), _
        Order1:=xlAscending, _
        Key2:=Range("H6"), _
        Order2:=xlAscending, _
        Header:= xlNo

Old James fucked around with this message at 20:16 on Feb 24, 2012

# ? Feb 24, 2012 19:26

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

Alright almost got this done, you've been a great help Old James.

One more question: I want to insert a new row and then number it sequentially(so row 1 has cell A1 with a '1' row two's A2 has a '2' etc.)

I can do the insert and I've figured out how to get it to count sequentially but I only want it to assign a number to rows with occupied cells, not just count down until it hits the page limit.

I'm guessing some kind of 'If' statement will do it, but I don't know how to write it where it will check for anything at all in the adjacent cell.

# ? Feb 24, 2012 22:36

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

It sounds like you want to number the rows instead of actually inserting new blank rows. So...

code:

sub rowvalue()
     dim i as long
     dim last as long
     
     For i = 0 to last
          if application.worksheetfunction.countA(range("1:1").offset(i,0))>0 then
               if iserror(application.worksheetfunction.large(range("A:A"),1)) then
                    range("A1").offset(i,0).value = 1
               else
                    range("A1").offset(i,0).value = _
                    application.worksheetfunction.large(range("A:A"),1) + 1
               end if
          end if
     next i
end sub

# ? Feb 25, 2012 00:02

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

running that returns:

quote:

Unable to get the large property of the WorksheetFunction class

I'm not sure why, I've been googling around but I don't see anything to explain why it would error out. Maybe something to do with my insert for column A?

code:

Sub rowvalueinsert()
     Dim i As Long
     Dim last As Long
     
     Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
     For i = 0 To last
          If Application.WorksheetFunction.CountA(Range("1:1").Offset(i, 0)) > 0 Then
               If IsError(Application.WorksheetFunction.Large(Columns("A:A"), 1)) Then
                    Range("A1").Offset(i, 0).Value = 1
               Else
                    Range("A1").Offset(i, 0).Value = _
                    Application.WorksheetFunction.Large(Columns("A:A"), 1) + 1
               End If
          End If
     Next i
End Sub

# ? Feb 25, 2012 01:19

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

It looks like the error is occurring when it tries to find the largest existing value in column A while column A is blank. Which is odd, because I told it if the large function returned an error to make the value 1. The version above would have picked up numbering after any existing value in column A. But the version below is simpler and just starts at 1.

code:

Sub rowvalueinsert()
     Dim i As Long
     Dim last As Long
     Dim counter as long
     
     Columns("A:A").Select
     Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
     counter = 1
    
     For i = 0 To last
          If Application.WorksheetFunction.CountA(Range("1:1").Offset(i, 0)) > 0 Then
               Range("A1").Offset(i, 0).Value = counter
               counter = counter + 1
          End If
     Next i
End Sub

# ? Feb 27, 2012 15:51

Fists Up: Apr 9, 2007

I'm trying to generate a number of reports for our customers at work.

Right now I have a list of 81 customers. I also have a workbook which has 81 sheets that each have a blank sales template in them.

What I need to do is place one name from the list of customers into cell A1 of each of the 81 sheets which will then use a VLookup to generate the sales data in each sheet.

At the end I will then have a workbook that has an individual sheet for each customer. As a bonus it would also be good if it could also rename the sheet to whatever the customers name is.

I'm pretty bad at macros so I'm not too sure how to automate this process rather than doing it individually but I dont think its too hard to write up.

# ? Mar 13, 2012 06:45

gwar3k1: Jan 10, 2005; Someday soon

Do you need to add the VLookup using code as well?

Excel has a macro recorder which you might be interested in to learn how macros are constructed for the tasks you want. For example, you could record a macro where you rename a sheet and enter a value in to cell A1 of that sheet. You use the code generated and put it into a loop and add any extra code you need to perform.

Assuming you use a list thats comma delimited ("Steve, Graham, Ken,...") then this will do what you need, or is at least a starting point if you need to add any more code in. Untested.

code:

Sub ResetSheetName
  For i = 1 to Sheets.Count
    Sheets(i).Name = format(now, "yymmddhhmmss") & "_" & i
  Next
End Sub

Sub RenameFromArray
  ' Turn your comma delimited list into an array  
  clients = "clientA, clientB, clientC, clients D E F, client G"
  clientName = split(clients, ",")

  ' Rename sheets to prevent duplicating client names
  Call ResetSheetName

  ' Loop as many Names as possible but don't exceed number of sheets or number of clients
  iMax = iif(Sheets.Count > ubound(clientName), ubound(ClientName), Sheets.Count-1)
  For i = 0 to iMax
    ' Each loop is a new sheet
    ' i+1 here represents i being out by one when referencing the sheet number
    Sheets(i+1).Name = clientName(i)        ' Renames the sheet the client name
    Sheets(i+1).Cells(1, 1) = clientName(i) ' Puts the value in A1 (row1, col1)
  Next
End Sub

# ? Mar 13, 2012 19:55

Fists Up: Apr 9, 2007

The Vlookup stuff is already in there.

Thanks for that. I'll give it a go now.

# ? Mar 14, 2012 01:12

me your dad: Jul 25, 2006

When saving a file as .csv, why does it ask if you want to save the changes to file you just saved?

For example, I open a new workbook and populate a worksheet. I save it as csv. First, it tells me the format doesn't accept multiple sheets. I say ok. Next it tells me some features may not be compatible with csv. I say ok. Then, after the file has saved and I try to close the window, I get "Do you want to save the changes you made to [filename]?" I always click no, because otherwise it leads to a loop, and I've confirmed the changes have been made after saving.

Just curious.

# ? Mar 14, 2012 20:47

G-Dub: Dec 28, 2004; The Gonz

I have noticed this but it has worn me down enough to always Ctrl-S, close, 'No'. It would be interesting to find out why. I might investigate tomorrow.

# ? Mar 14, 2012 22:57

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

Old James posted:

It looks like the error is occurring when it tries to find the largest existing value in column A while column A is blank. Which is odd, because I told it if the large function returned an error to make the value 1. The version above would have picked up numbering after any existing value in column A. But the version below is simpler and just starts at 1.
code:
Sub rowvalueinsert()
     Dim i As Long
     Dim last As Long
     Dim counter as long
     
     Columns("A:A").Select
     Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
     counter = 1
    
     For i = 0 To last
          If Application.WorksheetFunction.CountA(Range("1:1").Offset(i, 0)) > 0 Then
               Range("A1").Offset(i, 0).Value = counter
               counter = counter + 1
          End If
     Next i
End Sub

I know its been like 3 weeks but thanks, I actually ended up with pretty much that after playing with it over the weekend. VBA seems so simple and then things just... don't work. Very frustrating.

# ? Mar 14, 2012 23:11

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

Xguard86 posted:

I know its been like 3 weeks but thanks, I actually ended up with pretty much that after playing with it over the weekend. VBA seems so simple and then things just... don't work. Very frustrating.

You're welcome.

# ? Mar 15, 2012 14:26

Bulls Hit: Dec 30, 2011; Legit questions always get stupid responses. Perhaps your brain will grow and you will gain intelligence and you might be able to provide an intelligent response someday! I don't pray, so I'm just going to hope you do get that intelligent brain some day.

I'm really bored, and just playing around with Excel. My current goal is to make out a spreadsheet for the NHL, where I have every team listed. I have 82 columns wide, 1 for each game. At the beginning of the season, it just says their opponent, and I type in the result of the game for that team in the field:

W, L, SOW, SOL, OTW, OTL

I already have the code set up so that my standings page links to the "results" page, and the Wins, Losses, Overtime Losses, Points, ROW, and Games Played columns all update themselves.

I don't know how to do macros. I did use the sort function to sort the divisions by points, highest first. Second criteria is games played, with lowest games played being at the top, and the 3rd criteria being ROW, with the highest. So it sorts out just like the standings are supposed to.

Here is where I have some quesitons:

1) Is there a way to set it up to auto sort, so when I enter in a change in the "results" tab, it will automatically update the standings tab, without me having to manually go in and tell it to sort itself(it saves the sorting criteria, but I would like it to update any time a value is changed).

2) NHL is a bit different than other sports, in that each division winner is ranked 1/2/3 between the 3 division winners, then 4-8 for playoff spots are ranked by points between everyone else in the conference. I'm trying to have it rank each division based on the sorted criteria, so I can link from the divisions over to the conference standings I want to set up.

I hope this all makes sense. Basically, auto sort and a way to rank using more than 1 criteria. I can't use just points as the rank because of the other criteria involved(Games Played and Regulation/Overtime wins).

# ? Mar 17, 2012 01:23

Sub Par: Jul 18, 2001; Dinosaur Gum

Since your standings tab probably has fixed ranges that don't change, you'll be able to get by this way:

1) Open the VBA editor and double-click the "results" tab in the tree nav on the left.

2) There are two drop-down boxes above the right pane. On the left, select "Worksheet". On the right, select "Change".

3) This will create two Subroutines. Delete the one called Worksheet_SelectionChange. In the other (Worksheet_Change) enter this code (modifying for your purposes:

code:

    ActiveWorkbook.Worksheets("Standings").Sort.SortFields.Clear
    'Instead of B2:B14, put the range that contains the range for the column you want to sort by (without headers)
    ActiveWorkbook.Worksheets("Standings").Sort.SortFields.Add Key:=Range( _
        "B2:B14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    'Instead of A1:B14, put the range that contains the range you want to sort INCLUDING headers.
    With ActiveWorkbook.Worksheets("Standings").Sort
        .SetRange Range("A1:B14")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

As far as sorting by multiple criteria, when you sort this way, those sorts cascade. So you can take that second statement there (..Sort.SortFields.Add...) and add a bunch of them like this to sort (for example by columns B, then C, then D):

code:

    ActiveWorkbook.Worksheets("Standings").Sort.SortFields.Clear
    'Instead of B2:B14, put the range that contains the range for the column you want to sort by (without headers)
    ActiveWorkbook.Worksheets("Standings").Sort.SortFields.Add Key:=Range( _
        "B2:B14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    'Instead of C2:C14, put the range that contains the range for the column you want to sort by (without headers)
    ActiveWorkbook.Worksheets("Standings").Sort.SortFields.Add Key:=Range( _
        "C2:C14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    'Instead of D2:D14, put the range that contains the range for the column you want to sort by (without headers)
    ActiveWorkbook.Worksheets("Standings").Sort.SortFields.Add Key:=Range( _
        "D2:D14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    'Instead of A1:D14, put the range that contains the range you want to sort INCLUDING headers.
    With ActiveWorkbook.Worksheets("Standings").Sort
        .SetRange Range("A1:D14")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Hope this at least helps you.

# ? Mar 17, 2012 04:12

Tortilla Maker: Dec 13, 2005; Un Desmadre A Toda Madre

Let's pretend that I contract with various authors to write short articles for a weekly magazine. My bookkeeper has been great about creating electronic copies of our contracts and maintaining a spreadsheet detailing basic information. The spreadsheet consists of three columns titled: Author, Subject, Contract Number

The same author may have drafted multiple articles on the same subject but has a unique contract number for each of those projects. The contract number itself consists of "#####-####" such as "12345-6789".

Unfortunately, the bookkeeper failed to have a consistent method of naming the electronic copies of the contracts but each filename does have the contract number in it.

For example:
02-20-2010_Smith_Puppies - Contract_12345-9989.pdf
Feb_20_2010 - John Smith - Puppies - Contract 12300-5555.pdf
John Smith - Cats - 12299-2211.pdf
12112-7890.pdf
13333-0012 - Obama_Barak - June 2011.pdf

Let's throw in that there are over 400 of these files and the file names really have no rhyme or reason to their formatting.

I'm essentially trying to create hyperlinks to these PDF documents using the information found in the Contract Number cells.

In attempting to find an answer online, I found the following but it would only work if the text in the cells was the full file name of the document without the pdf extension:

code:

 Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
ActiveWorkbook.FollowHyperlink Address:= _
"C:\folder\records\" & Target.Value & ".pdf"
End Sub

The above formula(?) or code(?) would have correctly linked the fourth example cited above but none of the others.

I also found something similar to the following but it doesn't work either (I don't have my work computer infront of me so it may be off a bit, but it was essentially pieced together using the above code and that suggested at http://www.mrexcel.com/forum/showthread.php?t=502374:

code:

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

    Dim fName As String
    Dim fPath As String

fPath = "C:\Test Folder\"
    
fName = ActiveCell.Value
fName = "*" & fName & "*" & ".pdf"
     
ActiveWorkbook.FollowHyperlink Address:= _
fPath & fName
End Sub

But this works even less as it won't link to any of the examples cited above.

I hope my scenario is easy to follow.

Any advice would be great appreciated.

# ? Mar 17, 2012 04:27

Bulls Hit: Dec 30, 2011; Legit questions always get stupid responses. Perhaps your brain will grow and you will gain intelligence and you might be able to provide an intelligent response someday! I don't pray, so I'm just going to hope you do get that intelligent brain some day.

Sub Par posted:

A bunch of excel code I didn't want to quote to take up a bunch of page space.

Wow, you really know your stuff. I was hoping there was a way I could set it up to do it without using macro codes, like a function I could type in that would be able to do it. I really don't want to be using something that I don't know how to code, but it works. Testing it up, changing my results tab, if a team would be passing up another team based on points, it automatically sorts it in the standings tab.

Couple questions:

1) I'm doing each division seperately, can I use that code multiple times, where it will change according to the specified fields? Like, I have the results tab, with every team, or would I have to make 2 different sheets for each division? No big deal if I would have to do that, would just be easier to have them all on 1 tab.

2) Is there a way to rank a list according to multiple criteria? I know there is a rank that if there is a tie(If you go by points, let's say 2 teams have 70 points each), it will come back with the same rank. I ask this because of the way standings are formulated with the NHL. I'm trying to rank the 3 division leaders, to place into the top 1-3 spots, then fill in the rest with the remaining teams. I figure a ranking system would be easiest to pull the data?

3) Maybe I'm doing it the wrong way, and should just try to make the conference standings without the division standings? But is there a way to copy over an entire row of data(Like A1:A5), and pull it into another table. For example:

Pacific Division standings, 4 teams. The top team would be either 1,2,3 in the conference. Then of the other 3 teams, they are below 1,2,3. I want to be able to have the conference standings seperate of the division standings, and if possible for all of them to update just by me entering in the Win/Loss information in the results tab.

My whole goal out of all of this is just to be able to toy around with possible scenarios, if x team wins what happens, etc. Extra stats, and a bunch of other "worthless" information that you have to do research for.

# ? Mar 17, 2012 20:58

Bulls Hit: Dec 30, 2011; Legit questions always get stupid responses. Perhaps your brain will grow and you will gain intelligence and you might be able to provide an intelligent response someday! I don't pray, so I'm just going to hope you do get that intelligent brain some day.

Sub Par, and anyone else. Here is a link to the excel file I have been working on.

http://www.mediafire.com/?73uau3rcqn72fc8

I have the Pacific Division lined up with the results tab to auto update, thanks to Sub Par's code, it sorts automatically.

I just typed in the numbers manually for the other division's. I then just copied the cells over from the divisions to the conference. I guess I need to add the $ sign for each cell from the results tab so that the numbers are showing correctly(the Pacific shows 0's since the cells referenced changed in the results tab).

Or I could always do =A2,=B2, etc. for the Conference standing, and then use the sort function to sort it accordingly?

Hope this makes sense, I just have to make the divisions, then sort out which team gets the #1-#3 seed(each division leader sorted accordingly), then fill in the rest.

# ? Mar 17, 2012 21:43

Bulls Hit: Dec 30, 2011; Legit questions always get stupid responses. Perhaps your brain will grow and you will gain intelligence and you might be able to provide an intelligent response someday! I don't pray, so I'm just going to hope you do get that intelligent brain some day.

BTW, if it's just functions that I can enter to do something, just point me in the right direction and I can toy with it to figure it out. I want to be able to do this on my own. I just wish I was more knowledgable with all of this.

# ? Mar 17, 2012 21:57

Bulls Hit: Dec 30, 2011; Legit questions always get stupid responses. Perhaps your brain will grow and you will gain intelligence and you might be able to provide an intelligent response someday! I don't pray, so I'm just going to hope you do get that intelligent brain some day.

Edit: I found the power of the record macro function. I now have 2 different macros sorting 2 different divisions on the same worksheet. I think I can figure this out slowly but surely!

Bulls Hit fucked around with this message at 23:02 on Mar 17, 2012

# ? Mar 17, 2012 22:53

G-Dub: Dec 28, 2004; The Gonz

Tortilla Maker posted:

Words about contracts

The problem here isn't with Excel but with the naming convention (or lack thereof) within your contract files. I would personally be renaming the files to contract number only.pdf. Excel can be used to do this. If you don't want to rename the files then there is still a solution using the same principal.

You want to look in to a function called Dir(). If you do something like the below you will get the full name of the file - it's up to you if you then just want to launch it or you want to run a one-time script to rename them all.

This is done direct in the reply so excuse any syntax errors.

code:

Dim strFilename as string
    
    strFilename = Dir("C:\Path\to\contract\files\*" & ActiveCell.Value & "*")

It should then be easy to either launch the file or even better have your script rename the file then launch the renamed version (or even better just loop this script and rename them all the first time around).

# ? Mar 18, 2012 14:46

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

Tortilla Maker, this should parse the file names for contract numbers and then if it finds a match will add the file name and a hyperlink to the file in a column to the right on the sheet with the contract numbers. I haven't tested it out, so it might need some tweaking.

code:

sub TortillaMaker()
	dim RNG as range
	dim CELL as range
	dim Contracts as range
	dim Files as range
	const fpath = "C:\folder\records\"
	dim strLEFT as string
	dim strRIGHT as string
	dim strContract as string
	dim last as long
	
	'Set these ranges to the respective sheets and ranges containing the file names and contract numbers
	set Contracts = thisworkbook.sheets("Sheet1").range("C1:C400")
	set Files = thisworkbook.sheets("Sheet2").range("A1:A400")
	last = Contracts.usedrange.rows.count
	
	For each CELL in Files
		strRIGHT = Cell.value
		Do While instr(strRIGHT,"-")>0
			strLEFT = left(strRIGHT,instr(strRIGHT,"-")-1)
			strRIGHT = right(strRIGHT,len(strRIGHT)-instr(strRIGHT,"-"))
			if len(strLEFT)>= 5 AND len(strRIGHT)>=5 then
				if isnumeric(right(strLEFT,5)+0) AND isnumeric(left(strRIGHT,5)+0) then
					strContract = format(right(strLEFT,5)+0,"00000") & "-" & format(left(strRIGHT,5)+0,"00000")
					With Contracts
						set RNG = .find(what:=strContract, after:=.cells(1,last), lookin:=xlvalues, lookat:=xlwhole)
						If not RNG is nothing then
							RNG.offset(0,1).Hyperlinks.Add anchor:=RNG.offset(0,1), address:=fpath & CELL.value, TextToDisplay:=CELL.value
						end if
					End With
				end if
			end if
		Loop
	Next Cell
end sub

# ? Mar 19, 2012 02:44

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

what is wrong with this vba? I put a comment next to what it is flagging as an error. It says "object required". I'm sure its simple but I am bad at this. Can I not do " " for blank cell? Do I need something else?

code:

Sub delete_outside_offices()

Dim rng1 As Range
Dim rng2 As Range
Dim i As Integer


Set rng1 = Workbooks("MONTH_END_MACRO.xlsm").Sheets("usr").Range("C1")
Set rng2 = Workbooks("MONTH_END_MACRO.xlsm").Sheets("qa").Range("C1")
i = 0

Do
    If rng1.Value <> "Dallas Office" Or rng1.Value <> "Other" Then
    rng1.EntireRow.Delete
    i = i + 1
    End If

##highlights this as the error
Loop Until rng1.Offset(i, 0).Value = ""


Do
    If rng2.Value <> "Dallas Office" Or rng2.Value <> "Other" Then
    rng2.EntireRow.Delete
    i = i + 1
    End If

Loop Until rng2.Offset(i, 0).Value = ""



End Sub

# ? Mar 19, 2012 16:09

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

Xguard86 posted:

what is wrong with this vba? I put a comment next to what it is flagging as an error. It says "object required". I'm sure its simple but I am bad at this. Can I not do " " for blank cell? Do I need something else?
code:
code...

You are setting rng1 to cell C1 and then deleting that cell, so I am guessing it is complaining because rng1 is now no longer set to a range value.

But there are other things in your code that make me think it will not work the way you intended. First, because you never reset rng1 to any other cell (looks like you are intending to with the i value, but you don't quite get there) each time it loops it is always checking the exact same value. But since you are deleting rows in the process your loop will end up skipping cells you want to check. Example, cell C3 = "Dallas Office", on the first loop it checks C1 it leaves the row and sets i = 1. On the second loop it checks C2, leaves the row and sets i = 2. Third pass it deletes row 3 and sets i=3. However, row 4 is now row 3 and when it loops again it looks at the value which was in C5 but now is in C4. So every time the loop deletes a row it misses out on checking the next row's value.

Here's how I'd do it, though someone else might have a better solution.

code:

sub delete_outside_offices()
   dim i as long
   dim last as long

   With thisworkbook.sheets("usr")
      last = .usedrange.rows.count - 1
      For i = last to 0 step -1
         if .range("C1").offset(i,0).value = "Dallas Office" then
            .range("C1").offset(i,0).entirerow.delete
         end if
      Next i
   End With

   With thisworkbook.sheets("qa")
      last = .usedrange.rows.count - 1
      For i = last to 0 step -1
         if .range("C1").offset(i,0).value = "Dallas Office" then
            .range("C1").offset(i,0).entirerow.delete
         end if
      Next i
   End With
End Sub

# ? Mar 19, 2012 17:06

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

ah I see. I never even thought about how deleting rows would screw up the loop.

# ? Mar 19, 2012 17:10

Adbot: ADBOT LOVES YOU

# ? Apr 28, 2024 06:02

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

Xguard86 posted:

ah I see. I never even thought about how deleting rows would screw up the loop.

Yeah, I learned that the hard way. With the code above I am checking the values from the bottom of the list going up. That way when I delete a row, the values below it which are shifted up have already passed the test while the untested rows above remain unchanged.

Old James fucked around with this message at 17:15 on Mar 19, 2012

# ? Mar 19, 2012 17:13

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

«‹›82 »