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

gwar3k1: Jan 10, 2005; Someday soon

Long Wang posted:

Is there any way to do this?

Have a template, that when opened, automatically displays the Open File dialog then execute the code from there. Have the user double click the template rather than the text file.

Are the files sequentially named or somehow named in a way that you could automatically grab the most recent file and open it? That'd minimize input further.

# ¿ Aug 29, 2010 21:54

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 07:44

gwar3k1: Jan 10, 2005; Someday soon

Not the most efficient way, but it will do what you've asked:

code:

Sub CopyData()
  iRow = 1
  Do While Cells(iRow, 1) <> ""
    'Get department name and copy data
    sDept = Cells(iRow, 1)
    Range("A" & iRow & ":C" & iRow).Select
    Selection.Copy
    
    'Paste to department sheet (last row of)
    Sheets(sDept).Select
    Range("A" & NextFreeRow(sDept)).Select
    ActiveSheet.Paste
    
    'Repeat
    Sheets("masterlist").Select
    iRow = iRow + 1
  Loop
End Sub

Function NextFreeRow(sSheet)
  iCur = 1
  Do While Sheets(sSheet).Cells(iCur, 1) <> ""
    iCur = iCur + 1
  Loop
  NextFreeRow = iCur
End Function

# ¿ Oct 9, 2010 22:15

gwar3k1: Jan 10, 2005; Someday soon

serewit posted:

It was failing, but I see what the problem is. This should work okay, thanks!

Ok, no, it's still failing. It's giving 'subscript out of range' after a few rows, and it's skipping the first two sheets. I thought that maybe it was checking the object name instead of what the sheet was named, but it doesn't seem to be the case.

I've made the assumption that your department sheets are named exactly the same as the department code. Also, I have made the assumption that there is a sheet for every department on your master list sheet.

I bet its saying subscript out of range because a sheet name doesn't match / the intended sheet doesn't exist.

Can you provide an example of department code and sheet name, and say which line is causing the exception (highlighted yellow ehen you click debug).

# ¿ Oct 9, 2010 23:26

gwar3k1: Jan 10, 2005; Someday soon

serewit posted:

stuff

It needs to be run from the main sheet. Here's some ammended code:

code:

Sub CopyData()
  iRow = 1
  SHeets("Main").Select
  Do While Cells(iRow, 1) <> ""
    'Get department name and copy data
    sDept = trim(Cells(iRow, 1))
    Range("A" & iRow & ":C" & iRow).Select
    Selection.Copy
    
    'Paste to department sheet (last row of)
    Sheets(sDept).Select
    Range("A" & NextFreeRow(sDept)).Select
    ActiveSheet.Paste
    
    'Repeat
    Sheets("Main").Select
    iRow = iRow + 1
  Loop
End Sub

Function NextFreeRow(sSheet)
  iCur = 1
  Do While Sheets(sSheet).Cells(iCur, 1) <> ""
    iCur = iCur + 1
  Loop
  NextFreeRow = iCur
End Function

# ¿ Oct 10, 2010 00:02

gwar3k1: Jan 10, 2005; Someday soon

edit: ^^^ No thanks, just glad I could help someone for a change. I'm always asking.

Not really a coding question, but I have a template that does multiple functions (Add client, edit client, etc.) and I want the users to be able to select these functions from the ribbon.

Calling the template from the ribbon opens the template and not a regular copy of it. How can I make the ribbon force template functions to open as non-template functions? Or should I just disable saving (they shouldn't require the need to save anything anyway).

This is an example line from the ribbon customizations:

code:

<mso:button idQ="x1:C:_wamp_www_admin_AddClients.xltm_ThisWorkbook.ADD_CLIENTS_0_14E0275B" 
label="1 - Clients" imageMso="CondolatoryEvent" 
onAction="C:\wamp\www\admin\AddClients.xltm!ThisWorkbook.ADD_CLIENTS" visible="true"/>

gwar3k1 fucked around with this message at 21:23 on Oct 12, 2010

# ¿ Oct 12, 2010 21:20

gwar3k1: Jan 10, 2005; Someday soon

IceHawk posted:

For example:
code:
    Selection.Replace What:=ChrW(&H19B), Replacement:=ChrW(&H29F), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True
This works well, until I need to use a sequence of characters as input and/or output. The problem is I have no idea how to get Excel to accept a sequence of two ChrW(&Hxxx).

What I need this code to do, it take something like "tɬ" as input, and output "ʟ̣"

Does simple concatenation work: "ChrW(&Hxxx) & ChrW(&Hxxx)"

code:

    sFind = ChrW(&H19B) & ChrW(&H20B)
    sReplace = ChrW(&H29F) & "t"
    Selection.Replace What:=sFind, Replacement:=sReplace, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True

If it does, setup a sheet with two columns: Lang A, Lang B and enter your translations as needed, then use the following macro (or a modified version) to translate your sheets:

code:

Sub TranslateIt
  For i = 1 to Sheets.Count
    If Sheets(i).Name <> "TransLookUp" Then
      Sheets(i).Select
      iRow = 1
      Do While Sheets("TransLookup").Cells(iRow, 1) <> ""
        sFind = Sheets("TransLookup").Cells(iRow, 1)
        sReplace = Sheets("TransLookup").Cells(iRow, 2)
        Selection.Replace What:=sFind, Replacement:=sReplace, LookAt:=xlPart, _ 
             SearchOrder:=xlByRows, MatchCase:=True
        iRow = iRow + 1
      Loop      
    End if
  Next
End Sub

gwar3k1 fucked around with this message at 19:57 on Nov 3, 2010

# ¿ Nov 3, 2010 19:52

gwar3k1: Jan 10, 2005; Someday soon

IceHawk posted:

Except I don't quite understand what your second macro does (I'm a Linguist and all I have is one year of computer science under my belt), all of my data is formatted so that column C contains the empty fields that I need to put the translation into, and column E contains the source.

Sorry, the macro assumes you have a sheet named "TransLookup". On that sheet you have a column (A) which contains your original characters, and a second column (B) which contains the characters that col A should be changed to. I'm assuming your task is as simple as "change xx to yy" without any conditions (i.e. not accounting for "change xx to yy but if xx preceeds xxz then change it to ccc").

The macro loops all the worksheets in your book that aren't TransLookup (which it is using as reference) then it is making the replacement for every replacement you have instructed it to do (all rows on TransLookup).

Does that make sense?

# ¿ Nov 3, 2010 20:49

gwar3k1: Jan 10, 2005; Someday soon

Here's a sub that'll do what you want (identify duplicate positive dates):

code:

Sub DupCheck()
  bDuplicate = False
  sDates = ""
  For Each xCell In Range("B:B")
    If xCell.Value = "yes" Then
      If InStr(1, sDates, Cells(xCell.Row, 1)) > 0 Then
        bDuplicate = True
      Else
        sDates = sDates & Cells(xCell.Row, 1) & ","
      End If
    End If
  Next

  'bDuplicate is then the variable you want to check
End Sub

If you're using Excel >= 2007, there is a duplicate removal feature, if that's what this is hoping to do?

# ¿ Nov 4, 2010 18:46

gwar3k1: Jan 10, 2005; Someday soon

IT Guy posted:

Actually, I made a mistake, I need to check array2 to see if every value is the same or unique.

Will that still work?

After Googling around, it appears the FREQUENCY() function may do what I need, I just don't know how to use it properly.

No, the sub I posted will store a list of unique values and return true if there are any duplicates. You can validate by doing:

code:

if len(sDates) > 0 and bDuplicate = false then
  msgbox("All yes dates are unique")
else
  msgbox("At least one yes date is repeated")
end if

To use worksheet functions in VB code:

code:

  'Application.WorksheetFunction.<functionname>([params])
  Application.WorksheetFunction.Frequency(Range("B:B"),Array("yes"))

Reading the help for Frequency doesn't really help me understand what it does or how it does it exactly.

# ¿ Nov 4, 2010 19:14

gwar3k1: Jan 10, 2005; Someday soon

I would have posted a modified version of my sub, but Zhentar's is much better.

# ¿ Nov 5, 2010 19:00

gwar3k1: Jan 10, 2005; Someday soon

Meme Emulator posted:

So long as people can interpret a boolean value as working/not working, you surely don't have an issue, other than using the wrong chart type. Use a bar graph, y-axis is your condition, x-axis is the day and you plot every machine on every day.

In the image, 1 is working, 2 is not. You could also make it obvious which machine wasn't working on which day by using 0 for working (will not display on the chart) and 1 for not working.

~~If you must have objective labels, use a textbox placed over the number.~~ ZerodotJander's solution to labels is better.

gwar3k1 fucked around with this message at 18:45 on Jan 7, 2011

# ¿ Jan 7, 2011 18:40

gwar3k1: Jan 10, 2005; Someday soon

Or not even a loop:

iRow = Range("U1:U60000").End(xlDown).Row + 1 'Will find the first blank cell from U1 counting up 1,2,3...
'Replace xlDown with xlUp to find the last cell with data then add 1 to suggest the first blank cell at the end of the column
'60000 because I don't remember the 97-03 max row number, 65366?

gwar3k1 fucked around with this message at 19:39 on May 24, 2011

# ¿ May 24, 2011 19:37

gwar3k1: Jan 10, 2005; Someday soon

This is untested but this should copy all your sheets onto one sheet TempData, sort it, then create new sheets for each location and copy the data from TempData to Location one row at a time.

code:

Sub LocationTabs()
  Sheets.Add
  ActiveSheet.Name = "TempData"
  
  iSheets = Sheets.Count
  iTempEnd = 1
  For i = 1 To iSheets
    If Sheets(i).Name <> "TempData" Then
      Sheets(i).Select
      iEnd = Range("A1:A65336").End(xlUp).Row
      
      Range("A1:Z" & iEnd).Select
      Selection.Copy
      
      Sheets("TempData").Select
      Range("A" & iTempEnd).Select
      ActiveSheet.Paste
      iTempEnd = Range("A1:A65336").End(xlUp) + 1
    End If
  Next
  
  'Sort the data. This is >=2007 code, if you're using <=03 I don't think this'll work
  Sheets("TempData").Select
  ActiveSheet.Sort.SortFields.Clear
  ActiveSheet.Sort.SortFields.Add Key:=Range("A1:A" & iTempEnd), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  With ActiveSheet.Sort
    .SetRange Range("A1:Z" & iTempEnd)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
  
  sPrevious = ""
  iEnd = 1
  For i = 1 To iTempEnd
    Sheets("TempData").Select
    sLocation = Cells(i, 1)
    
    If sLocation <> sPrevious Then
      Sheets.Add
      ActiveSheet.Name = sLocation
      iEnd = 1
    End If
    
    Sheets("TempData").Select
    Range("A" & i & ":Z" & i).Select
    Selection.Copy
    
    Sheets(sLocation).Select
    Range("A" & iEnd).Select
    ActiveSheet.Paste
    iEnd = iEnd + 1

    sPrevious = sLocation
  Next
  
  MsgBox ("Complete")
End Sub

I don't know how you're working, but would Excel's built in filter be of any use to you? Have a centralised data sheet and apply a filter as you see necessary? Or if you need the data on individual sheets, store it on an aggregate sheet that has buttons to run similar code to what I've posted - have a button to create sheets by location, WOF, Drill and Alarms. Push the button you need to view the data in the format you want.

# ¿ May 26, 2011 02:00

gwar3k1: Jan 10, 2005; Someday soon

Sounds like a few nested ifs really but I thought I'd write some VB for you. You could probably work out an excel function from this.

Untested and I think the loop might be insufficient.

code:

Sub VectorToMatrix
  iDRow = 3
  iORow = 2
  iOCol = 2
  iCount = 1

  Do While iCount <= (420*420)
    iResult = 0

    'Column code = [col]1
    If Sheets("data").Cells(iDRow, 1) = Sheets("Output").Cells(1, iOCol) Then
      'RowCode = A[row]
      If Sheets("data").Cells(iDRow, 2) = Sheets("Output").Cells(iORow, 1) Then
        iResult = Sheets("data").Cells(iDRow, iDCol)
      End if
    End if
    Sheets("output").Cells(iORow, iOCol) = iResult
    
    'Loops by column then row
    iOCol = iOCol + 1
    If iOCol = 442 Then
      iOCol = 2
      iORow = iORow + 1
    End if
 
    iDRow = iDRow + 1
    iCount = iCount + 1
  Loop
End Sub

Which is this right?

code:

=if(CC=B$1,if(RC=$A1,RESULTCELL,0),0)

# ¿ Jun 13, 2011 09:06

gwar3k1: Jan 10, 2005; Someday soon

As you've got the best solution, this is just to answer questions if you're curious.

spregalia posted:

When I go to debug, I get the error at:
code:
Do While iCount <= (420 * 420)

As Sub Par said, the loop should have been literal 176400 but I was in a rush and admittedly didn't think it would have been a limitation in VBA.

spregalia posted:

Should this be RC=$A2?

The Excel function I wrote was pseudo code where you should replace RC with the actual cell reference from your data sheet.

code:

=if('data'!$A3 = B$1, <NEST ROW IF HERE>, 0)

You are comparing the value of cell A[row] on the data sheet, to the value of [col]1 on your output sheet. [row] begins at 3 because that is your first data row on that sheet.

The formula would go in all the cells of your matrix starting at B2 based on your image.

spregalia posted:

I'm not sure if I follow, are you saying the VB code is identical to this if function (in other words, the VB is unnecessary)?

For a transposition excersize like this, I always pick VBA because I fear pivot tables (lack of understanding), which I why I wrote the macro. I provided the Excel function because I figured it would be a simpler way of doing what you wanted, albeit excessive and I don't even know if it was fit for purpose - I wrote it while I was eating my breakfast. They should have been use exclusively, so you were right.

# ¿ Jun 13, 2011 20:38

gwar3k1: Jan 10, 2005; Someday soon

ActiveSheet.Paste should be sufficient with you selecting Sheet11.Cells(3,1) before hand. I've never had to use Destination:=... before. Its possibly that.

Should you expect Excel to split vbTab and vbNewLines into columns and rows? You're making a formatted string which I assume would just paste into a single cell as the string you defined (but I've not used clipboard either).

Consider your first method, but put Application.ScreenUpdating = false at the start of your routine and true at the end. Screen updating is often the slowest part of a massive loop, I've found.

# ¿ Aug 2, 2011 08:55

gwar3k1: Jan 10, 2005; Someday soon

Baxta posted:

Activesheet.paste just puts it all into A3 however vbnewline enables it to actually go down the right number of rows just all the row data ends up in the one column ( it won't paste as recognizing the vbtab should be forcing it to spam the rows like in my output for the for loop.

Unfortunately, it is 1 billion too slow to populate the entire thing using the for loop as the populator even with screen updating off ( this has to go through thousands of records ). Before you tell me this shouldnt be done in excel I know but I do what I'm contracted to

Desperately need help with this one

How about this:

Paste it to a text file, save.
Open as tab delimited, copy
Paste in required workbook, close & delete text file

# ¿ Aug 2, 2011 18:13

gwar3k1: Jan 10, 2005; Someday soon

Microsoft is inclined to agree. Try vbCr instead of vbNewline. Maybe even trim the last vbTab before each newline?

# ¿ Aug 2, 2011 22:28

gwar3k1: Jan 10, 2005; Someday soon

Baxta posted:

EDIT: Just in case anyone was wondering, ActiveX controls dont work with Office for Mac 2011.

Update: Apparently opening a workbook with macros on office for mac then saving it on the mac makes it hosed for windows office. Havent found out exactly why yet.

Have you moved from 2003 (or the Mac equivalent) to 2011? Do you know that .xlsx files don't allow macros where as .xlsm do? Sorry if you already know this, but its the first thing that I'd check. Same goes for templates: .xltm allows macro use and isn't the default.

# ¿ Aug 9, 2011 21:30

gwar3k1: Jan 10, 2005; Someday soon

Start by rationalising (normalising?) your data. Know what fields/layout you need on your master spreadsheet and design the interface that will feed into that. Then if necessary, create the tools required to migrate from the multiple documents into the single document.

When you're done making your life easier, move the originals out of their current locations so your users cannot use them. Force them to use the single workbook or they never will.

I've recently moved into a job where we use Spiceworks which covers all the stuff your boss couldn't keep track of, as well as toner use and a whole treasure chest of amazing stuff. We primarily use it for the helpdesk system though. Perhaps you could look into that as a useful piece of software.

# ¿ Aug 11, 2011 18:33

gwar3k1: Jan 10, 2005; Someday soon

I Love Topanga posted:

I would like to have Excel take a look at Column A, run a google search, and spit the first result into Column B.

My Excel skills are beginner at best (I have a degree in econ so I can run regressions, and I can successfully make a pivot table... sometimes)

Seems like a simple enough script, but I don't even know where to start.

I'm using Excel 2007.

edit. Upon further research I realized this is way harder than I thought. Any input is appreciated though.

First, do a google search and figure out where your query goes in the URL.
Then record a macro that:
Uses Web Data (Data > Get External Data > From Web)
[Paste your query url in the address bar]
Select the page (click the yellow box with an arrow in it)
Saves the web query to a new sheet
Copy the cell with the link you want (first result)
Paste it into your sheet B1
Delete the web query sheet

When you stop recording, you can see the VBA that Excel produced by going into the IDE (Alt+F11). If you're comfortable with code, mess about with it.

# ¿ Aug 19, 2011 19:40

gwar3k1: Jan 10, 2005; Someday soon

uG posted:

I need to create some rather large excel files (100k+ rows, 200ish columns, 10 worksheets). I tried using Perl, but it doesn't free memory and eventually runs out. Do any scripting languages (the more *nix friendly the better) have the ability to create such large excel files?

Of random data? Can you just use VBA to do this? I assume you're using 2007+ as 2003 and below cap at 65536 rows or there abouts.

Is the excersize to have it create an xls within Excel, or could you create 10x csv files and import those as sheets into an Excel workbook?

# ¿ Aug 30, 2011 21:28

gwar3k1: Jan 10, 2005; Someday soon

Use a group panel to show/hide multiple elements with minimal code. Have two groups, one over lapping the other and your radio buttons outside of them both (underneath?).

You can add code to the radio change event in the VBE (double click the radio button on the designer, then top right of the code window is a drop down menu with the different events for the element in the menu to the left). If true then group1.visible = true else .visible = false and the opposite for the other radio button.

You could use the scroll button tool for a 0 - 1 scale.

# ¿ Sep 20, 2011 23:16

gwar3k1: Jan 10, 2005; Someday soon

Setup some pivot tables, or a single pivot table and make yourself a dashboard. I've recently got into them and they've made my life so much easier.

# ¿ Oct 9, 2011 18:56

gwar3k1: Jan 10, 2005; Someday soon

Set up an interface using vba and forms which allows users to edit rows and commit them to the database rather than allowing them to change them directly on the sheet.

An alternative, or parallel solution, depending on just how large your data set is copy the data sheet to a hidden sheet then when the user changes a cell on their visible sheet, you have a flag on the hidden sheet to say changed. Update that row safe in the knowledge any uniquely identifiing data hasn't been modified.

Rather than using a second sheet, you could prevent changes by modifying the cell handling. On cell entry, create a string variable with the original content. On cell change, check to see if it's changed - if so and it shouldn't, use the variable to set it back or if it should be changed, set your flag to alert your code to update that record.

I'd say the interface method is the way to go though. How much data do they need to see in the first place? Can they query what they need rather than have it all on show?

# ¿ Oct 18, 2011 18:25

gwar3k1: Jan 10, 2005; Someday soon

Do you know visual basic at all? You could experiment with the following code (making it more flexible for number of lines per group, reading column names and using the column number as a variable rather than fixed numbers in code...

Assuming your list of addresses is on a sheet called data and there is a sheet called formatted; and that on data, the column order is firstname, lastname, add 1, city, state, zip, tel 1, tel 2, tel 3, email

code:

  Set wdata = Sheets("data")
  Set wnice = Sheets("formatted")

  iGroup = 1
  iRows = 4
  iEnd = wdata.Range("A65536").End(xlUp).Row ' This finds the last row with data on the data sheet
  For iRow = 1 to iEnd ' Loop all rows from 1 to the last row with data
    If wdata.Cells(iRow, 1) <> "" Then ' Ignore blank rows if you have any
      ' First row of formatted output
      wnice.Cells(((iGroup-1) * iRows), 1) = wdata.Cells(iRow, 1) & ", " & wdata.Cells(iRow, 2) ' First name, Last name
      wnice.Cells(((iGroup-1) * iRows), 2) = wdata.Cells(iRow, 7) ' Tel 1
      ' Second row
      wnice.Cells(((iGroup-1) * iRows)+1, 1) = wdata.Cells(iRow, 3) ' Add 1
      wnice.Cells(((iGroup-1) * iRows)+1, 2) = wdata.Cells(iRow, 8) ' Tel 2
      ' third row
      wnice.Cells(((iGroup-1) * iRows)+2, 1) = wdata.Cells(iRow, 4 & ", " & wdata.Cells(iRow, 5) & " " & wdata.Cells(iRow, 6)) ' City, State Zip
      wnice.Cells(((iGroup-1) * iRows)+2, 2) = wdata.Cells(iRow, 8) ' Tel 3
      ' etc.

      iGroup = iGroup + 1 ' Increases the group, wouldn't work for a flexible number of rows
    End if
  Next

# ¿ Dec 5, 2011 00:07

gwar3k1: Jan 10, 2005; Someday soon

Have you tried changing the security level to low? You should be able to run a query without having to change that, but perhaps its a function of 2003 -> 2007 security zealotry? Developer Tab > Macro security.

If you have any work involving the analysis tool-pak, I know that is different between versions of Excel, maybe that's halting execution. If you look in the VB editor in 2007, missing references (for the workbook, not the VB references) will be highlighted in blue in the project window (Ctrl+R).

e: does the remote PC map to the database in the same way that the server pc does? We have a mapped drive L:\ to allow users access to the database stuff, maybe the connection string is to C: rather than the share?

gwar3k1 fucked around with this message at 00:13 on Jan 17, 2012

# ¿ Jan 17, 2012 00:09

gwar3k1: Jan 10, 2005; Someday soon

Put the checkbox values in to an array then loop the array for evaluation if it makes you feel better. You don't really need to optimise something this small.

Alternatively, you could use a function which runs through a nested if but uses Exit Function to break out before it traverses the full statement?

# ¿ Jan 19, 2012 23:28

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

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

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 07:44

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

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