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 »

khazar sansculotte: May 14, 2004

Thanks to everyone for all the ideas! I'll be working on this next week, I'll post again if anything interesting happens.

FAN OF NICKELBACK posted:

I didn't get to your $0 thing because I wasn't sure exactly what you meant.

Basically, not all departments use all accounts. If a department uses accounts 1000 and 3000, but not 2000, then on their report, rather than

code:

Account #      Dollars
1000            $500
2000            $0
3000            $600

it shows

code:

Account #      Dollars
1000            $500
3000            $600

I don't think this is actually going to be all that difficult to deal with though. On my template I'll just display all the accounts with $0 to start with, and just overwrite them with amounts for the accounts that each department actually uses.

# ? Jul 12, 2014 16:59

Adbot: ADBOT LOVES YOU

# ? Apr 26, 2024 14:46

khazar sansculotte: May 14, 2004

Okay, after some tinkering I found a lot more problems with the formatting of my exported Excel files (multiple rows in the pdf being exported to the same row in the excel file, etc.). BUT, I've discovered that exporting from a PDF to a Word file, and then copy/pasting the contents of the Word file into Excel, produces a much cleaner result than just exporting straight to an Excel file. I have no idea why this is the case, but whatever.

Now the problem is quickly converting 700 Word documents to Excel files. I'm expecting to make a macro in a blank word document (in the same folder as all my to-be-converted files) that does something like this:

code:

Sub DocxToXlsx()

Dim filename as String

filename = Dir()

Do While filename <> ""

   Document.Open(filename)
   Workbook.New(Name:=filename)
   Document.filename.Range.Select
   Document.filename.Range.Copy
   Workbook.filename.Paste
   Document.Close
   Workbook.Close(SaveChanges:=True)
   filename = Dir()

Loop

End Sub

Sorry for the bullshitty code, I know literally zero things about file I/O in VBA. Does this seem plausible, or make sense at all?

# ? Jul 14, 2014 18:37

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.

Use a 3rd workbook\word doc to control the other two. Then you can loop through every file in a directory, export pdf to word, find the open word document, convert to xls, close the word document, close and save the xls, repeat.

Should make life easier for you.

# ? Jul 14, 2014 18:41

me your dad: Jul 25, 2006

Okay. It's ~~4:01~~ 4:07 p.m. ET. Maybe someone will check this thread soon :ohdear:

I've got a logistical problem to work out.

I have five workbooks. Lets call one of them MASTER workbook. The others are 1-4.

MASTER workbook has 17,296 rows, and three columns for Email Address, First Name, Last Name.

Workbooks 1-4 have varying rows, and three columns for Email Address, First Name, Last Name.

I need to take any row from 1-4 which exists in MASTER workbook and remove them from MASTER workbook.

I can consolidate workbooks 1-4 into one workbook to make the comparison easier.

EDIT - I think I've got it, but I'd appreciate someone telling me I'm right.

I merged Workbooks 1-4 into a single file, and then pasted the contents in columns D, E, F of the MASTER workbook.

The result was:

code:

A		B		C		D		E		F
First Name      Last Name       Email		First Name	Last Name	Email

**I Removed headers before proceeding with the next step**

Then I inserted a column in the middle (which is why I reference column G) and used this formula:

=NOT(ISNA(VLOOKUP(C1,$G:$G,1,FALSE)))

That returned "True" for a bunch of them, and spot checks indicate they were in both columns. I deleted the TRUE rows.

This should do it, right?

me your dad fucked around with this message at 22:17 on Jul 18, 2014

# ? Jul 18, 2014 21:07

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.

Should work just done delete both of them at tge same time. Im also sure excel has some kind of remove duplicates function somewhere.

# ? Jul 19, 2014 16:59

El Grillo: Jan 3, 2008; Fun Shoe

Hmm, this has got me stumped. I have a list of event attendees with two columns that I'm trying to sort in a useful way. The columns are: year, and last name.
The issue is that not all the rows have a value in the 'year' column (i.e. people who are attending as guest of their spouse). I want to sort by year, and then by last name, in order to produce something like this:

code:

Year	        Lastname
1998            Federer
1999            Thompson
2002            Highnam
		Highnam
2005		Johnson
2006		Adams
		Adams
2009		Russel
2010		Prefect
2012		Beeblebrox

Does anyone know if this is possible in any way? Basically it's sorting by 'year' and then by 'Lastname', ignoring blank values in 'Year'.

# ? Aug 5, 2014 10:07

WhatsInaMojito: Dec 20, 2011

El Grillo posted:

Hmm, this has got me stumped. I have a list of event attendees with two columns that I'm trying to sort in a useful way. The columns are: year, and last name.
The issue is that not all the rows have a value in the 'year' column (i.e. people who are attending as guest of their spouse). I want to sort by year, and then by last name, in order to produce something like this:
code:
Year	        Lastname
1998            Federer
1999            Thompson
2002            Highnam
		Highnam
2005		Johnson
2006		Adams
		Adams
2009		Russel
2010		Prefect
2012		Beeblebrox
Does anyone know if this is possible in any way? Basically it's sorting by 'year' and then by 'Lastname', ignoring blank values in 'Year'.

I suggest a third column "Sort year" that fills in the missing data with an if statement. You can later hide this column.

# ? Aug 6, 2014 04:08

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

I am trying to compile a report to pull numbers for people I manage and I've come across a scenario where my sheet is double counting certain instances of things and I can't figure out how to filter out duplicates. Example date:

code:

Gaining Party | Losing Party | Submitter | Status
 Person A         Person B      Person A   Open
Gaining Party | Losing Party | Submitter | Status
 Person B          N/A          Person B   Open
Gaining Party | Losing Party | Submitter | Status
 Person B         Person A      Person A   Withdrawn
Gaining Party | Losing Party | Submitter | Status
 Person B         Person A      Person A   Open

I need:

# Submitted by A
# Submitted by B
# Withdrawn

My issue is with the withdrawn status - I have a relatively simple COUNTIF counting "Person A" under the "Submitter" column, however of course I don't want the 3rd issue that was withdrawn counted. Is there an easy way to exclude it? I was trying to use =COUNTIFS(C:C,"Person A",D:D,????)

Maybe a Match statement nested in an IF nested inside the ??? That seems way more complicated than it should be considering if I have 5 different people I would need a different match to account for each person, so that sucks. There's probably something ludicrously simple I am overlooking here.

# ? Aug 8, 2014 20:24

ZerodotJander: Dec 29, 2004; Chinaman, explain!

=COUNTIFS(C:C,"Person A",D:D,"<>Withdrawn")

# ? Aug 8, 2014 20:33

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

See, I knew it was something stupid. Thanks a ton.

# ? Aug 8, 2014 20:38

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

Hey everyone. I need a simple formula that a very unskilled excel user can copy/paste into and run easily. Here's how it works:

A query runs that pulls everyone from specific stores with a store code, and their salary. How could you run a formula that would pull the TOP 3 highest paid person from each store, while keeping it functionally simple

Veskit fucked around with this message at 18:51 on Aug 12, 2014

# ? Aug 12, 2014 17:23

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

Veskit posted:

Hey everyone. I need a simple formula that a very unskilled excel user can copy/paste into and run easily. Here's how it works:

A query runs that pulls everyone from specific stores with a store code, and their salary. How could you run a formula that would pull the TOP 3 highest paid person from each store, while keeping it functionally simple

=LARGE([Range],3) will return the 3rd largest value in the specified range. But you will need to sort the ranges by each Store Location Code for the following to work.

CELL E2: =large(offset($B$1,match(1,$B:$B,0),0,countifs($B:$B,1),1),3)
CELL E3: =large(offset($B$1,match(2,$B:$B,0),0,countifs($B:$B,2),1),3)

Then add the following formula next to each row of the salary (example for cell D2): =if($C2>=choose($B2,$E$2,$E$3),$A2,"")

It won't concatenate a list for you, but will flag the people you want to look at.

# ? Aug 12, 2014 20:10

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

Old James posted:

=LARGE([Range],3) will return the 3rd largest value in the specified range. But you will need to sort the ranges by each Store Location Code for the following to work.

CELL E2: =large(offset($B$1,match(1,$B:$B,0),0,countifs($B:$B,1),1),3)
CELL E3: =large(offset($B$1,match(2,$B:$B,0),0,countifs($B:$B,2),1),3)

Then add the following formula next to each row of the salary (example for cell D2): =if($C2>=choose($B2,$E$2,$E$3),$A2,"")

It won't concatenate a list for you, but will flag the people you want to look at.

You're the best Old James

# ? Aug 12, 2014 20:20

khazar sansculotte: May 14, 2004

I'm still working here and there on the project I posted about at the top of this page. I've converted the pdfs to docx files (full of tables) and successfully written Word VBA code to strip 98% of the garbage out of them. Now I want to convert them to xlsx files and I'm just having a terrible time with it for some reason. Here is what I have:

code:

Private Sub CommandButton1_Click()
    
    Dim objWord As Word.Application
    Dim objDoc As Word.Document
    Dim xldoc As Workbook

    Set xldoc = Workbooks.Add
    Set objWord = CreateObject("Word.Application")
    Set objDoc = objWord.Documents.Open("C:\Data\myfile.docx") '<-- This is where it hangs up
    objWord.Visible = True
        
    objWord.Selection.WholeStory
    objWord.Selection.Copy
    xldoc.Range("A1").PasteSpecial (xlpasteall)
    
    xldoc.SaveAs "C:\Data\myfile.xlsx")
    objWord.Quit

End Sub

When it gets to the line where it's supposed to open the word doc, it just hangs there forever. All I can do is go into the task manager and kill the WINWORD.exe process, at which point Excel gives me an error saying that the "remote procedure call failed." I've tried doing this all sorts of ways, but no matter what I do, I just can't seem to get it to open the word file.

# ? Aug 15, 2014 23:56

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

Ronald McReagan posted:

I'm still working here and there on the project I posted about at the top of this page. I've converted the pdfs to docx files (full of tables) and successfully written Word VBA code to strip 98% of the garbage out of them. Now I want to convert them to xlsx files and I'm just having a terrible time with it for some reason. Here is what I have:
code:
Private Sub CommandButton1_Click()
    
    Dim objWord As Word.Application
    Dim objDoc As Word.Document
    Dim xldoc As Workbook

    Set xldoc = Workbooks.Add
    Set objWord = CreateObject("Word.Application")
    Set objDoc = objWord.Documents.Open("C:\Data\myfile.docx") '<-- This is where it hangs up
    objWord.Visible = True
        
    objWord.Selection.WholeStory
    objWord.Selection.Copy
    xldoc.Range("A1").PasteSpecial (xlpasteall)
    
    xldoc.SaveAs "C:\Data\myfile.xlsx")
    objWord.Quit

End Sub
When it gets to the line where it's supposed to open the word doc, it just hangs there forever. All I can do is go into the task manager and kill the WINWORD.exe process, at which point Excel gives me an error saying that the "remote procedure call failed." I've tried doing this all sorts of ways, but no matter what I do, I just can't seem to get it to open the word file.

Can you try moving the line "objWord.Visible = True" BEFORE you open the document? Word may be prompting you for some reason or other (document is corrupted, caused an error previously, must be converted somehow, is locked for editing, etc...) but you are not seeing it and it may be waiting for your reply forever.

# ? Aug 16, 2014 01:07

khazar sansculotte: May 14, 2004

ShimaTetsuo posted:

Can you try moving the line "objWord.Visible = True" BEFORE you open the document? Word may be prompting you for some reason or other (document is corrupted, caused an error previously, must be converted somehow, is locked for editing, etc...) but you are not seeing it and it may be waiting for your reply forever.

That's exactly what it was, thanks!

I managed to get a lot of what I want to do working, but now I'm having an issue getting the code to loop through all the files. I have code that looks like this:

code:

    Dim objWord As Object
    Dim objDoc As Object
    Dim vDirectory As String
    Dim vFile As String
    Dim xldoc As Workbook
    
    vDirectory = "C:\Data\"
    vFile = Dir(vDirectory & "*.docx")
    
    'Do While vFile <> ""
        Set xldoc = Workbooks.Add
        Set objWord = CreateObject("Word.Application")
        objWord.Visible = True
        Set objDoc = objWord.Documents.Open(vFile)
        
	[some stuff]
    'Loop

But whenever it tries to open vFile, it gives me the error "This file could not be found (C:\windows\system32\myfile.docx)". This is unsurprising since that file doesn't exist in C:\windows\system32, but I don't get why it is looking in that directory instead of C:\Data ?

edit: nevermind! I solved this myself by changing

code:

vFile = Dir(vDirectory & "*.docx")

code:

vFile = vDirectory & "*.docx"

I'm still kind of curious to hear what's going on here though.

khazar sansculotte fucked around with this message at 02:39 on Aug 16, 2014

# ? Aug 16, 2014 02:36

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

Dir returns file names only ("myfile.docx"), not the full path. If you don't supply a full path to Documents.Open, it will probably guess you mean something like your current working directory or something like that.

# ? Aug 16, 2014 18:25

khazar sansculotte: May 14, 2004

Makes sense, thanks!

I've now got nice excel files which more or less look how I want them to look, but on a few of them, due to bugginess in the pdf-->docx conversion process, things which should be on separate lines have been crammed together on the same line. E.g., this:

code:

Account        Amount
40005000       45000.6056000.70

when I really want this:

code:

Account        Amount
4000           45000.60
5000           56000.70

Is there a 'smart' way to separate cells onto separate rows like this? I've tried googling for a solution for quite a while, but I can't really find anything analogous to this problem. Almost all of the affected lines have two 4-digit accounts, and all have dollars and cents, if that helps at all.

# ? Aug 17, 2014 14:23

khazar sansculotte: May 14, 2004

nevermind, dumb question with a dumb answer

khazar sansculotte fucked around with this message at 05:47 on Aug 18, 2014

# ? Aug 18, 2014 05:05

joepinetree: Apr 5, 2012

I am dealing with some fragments of string in an excel file and I need a command to pull out selected bits of string from a possible 5000 matches. That is, I want to extract some predetermined portions of string based on a long list of possible matches. I can do this easily in other software, but since I am collaborating with a person who uses excel I need to do this within excel.

To help people visualize what I am talking about, here's what my current file would look like:

code:

Column A

I went to the university of arizona...
I got my bachelors at UNC Chapel Hill
BS Harvard

So it is essentially bits of string where people answered open ended survey questions about their academic backgroung. The part of text I am trying to extract is the university part. To do that I have another file with the names of a few hundred universities:

code:

University of Arizona
Arizona State University
etc.

So what I need is a function that would look in the first file if a row contained any of the possible values in the second file, and if it did, generate a new column with just that text, so that the outcome would look like:

code:

Column A                                                             Column B
I went to the university of arizona...                               university of arizona
I got my bachelors at UNC Chapel Hill                                UNC Chapel Hill
BS Harvard                                                           Harvard

Now, playing around with lookup and other similar functions I've been able to do that one value at a time (generating a "true" value for line 1 and university of arizona, for example). What I need is something that would look at all values in file 2 and extract the bits that match at once. Any help on that?

# ? Aug 18, 2014 07:39

KingNastidon: Jun 25, 2004

joepinetree posted:

Any help on that?

I've setup the Excel workbook as follows:

code:

Column A (Input data)	                                             Column B (List of schools)				Column C (Mapping Result)
I went to the university of arizona...                               university of arizona
I got my bachelors at UNC Chapel Hill                                Lafayette
BS Harvard                                                           Lehigh
sdfdsfdsfs							     Bucknell
Lehigh wooo						     	     Harvard
								     UNC Chapel Hill

The following code uses the inStr method to loop through and check whether the string in ColumnA contains a school in the mapping column and returns it to ColumnC. Entries that have no mapping return a blank in ColumnC.

code:

Option Explicit
Option Base 1

Sub LoadData()
Dim avntMapping() As Variant
Dim avntColumnA() As Variant

Dim intNumMappingEntries As Integer
Dim intNumColumnAEntries As Integer

Dim astrMapping() As Variant
Dim astrColumnA() As Variant
Dim astrColumnC() As Variant

Dim intRow As Integer
Dim intACounter As Integer
Dim intMCounter As Integer

    avntMapping = wksInput.Range("B2").Resize(1000, 1).Value2
    avntColumnA = wksInput.Range("A2").Resize(1000, 1).Value2
    
    'Resize arays
    intNumMappingEntries = 0
    For intRow = 1 To 1000
        If avntMapping(intRow, 1) = "" Then
            Exit For
        End If
        intNumMappingEntries = intNumMappingEntries + 1
    Next intRow
    
    intNumColumnAEntries = 0
    For intRow = 1 To 1000
        If avntColumnA(intRow, 1) = "" Then
            Exit For
        End If
        intNumColumnAEntries = intNumColumnAEntries + 1
    Next intRow
    
    'ReDim String Arrays
    ReDim astrMapping(1 To intNumMappingEntries, 1)
    ReDim astrColumnA(1 To intNumColumnAEntries, 1)
    ReDim astrColumnC(1 To intNumColumnAEntries, 1)
    
    'Populate string arrays
    For intACounter = 1 To intNumColumnAEntries
        astrColumnA(intACounter, 1) = CStr(avntColumnA(intACounter, 1))
    Next intACounter
    For intMCounter = 1 To intNumMappingEntries
        astrMapping(intMCounter, 1) = CStr(avntMapping(intMCounter, 1))
    Next intMCounter
    
    'Check inString
    For intACounter = 1 To intNumColumnAEntries
        For intMCounter = 1 To intNumMappingEntries
            If InStr(astrColumnA(intACounter, 1), astrMapping(intMCounter, 1)) Then
                astrColumnC(intACounter, 1) = astrMapping(intMCounter, 1)
            End If
        Next intMCounter
    Next intACounter
    
    'Print to sheet
    wksInput.Range("C2").Resize(UBound(astrColumnC, 1), UBound(astrColumnC, 2)).Value = astrColumnC
    
End Sub

Is this what you were looking for? You can clean up the code a bit and replace hard-coded constants (e.g., 1000) to be flexible to your data set.

# ? Aug 18, 2014 08:53

joepinetree: Apr 5, 2012

Thanks. Seems straightforward enough. I will play around with it and see if my collaborator can also understand this.

# ? Aug 18, 2014 21:48

A Tartan Tory: Mar 26, 2010; You call that a shotgun?!

*Ignore me I am a COMPLETE moron.

A Tartan Tory fucked around with this message at 11:06 on Aug 19, 2014

# ? Aug 19, 2014 10:59

Afterbirth Aftermath: Aug 29, 2002

I'm having a bit of trouble with my macro in Excel 2010 that opens another variable .xlsm file. If I just run the macro, I select the file, click enable macros, and it opens the new workbook up in design mode and quits out of the macro. If I use F8 to go step by step, it runs fine. I thought it might have been .DisplayAlerts but I removed that and it's still happening. Any help would be appreciated.

code:

    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
         Application.ScreenUpdating = False
         Set Destwb = ActiveWorkbook
         filename = Application.GetOpenFilename(FileFilter:="All Files (*.*), *.*", Title:="Please select a file")
         Workbooks.Open Filename:=filename
         Set Sourcewb = ActiveWorkbook

I'm on a network where I cannot lower trust center settings and the source workbook is not mine.

# ? Aug 29, 2014 19:22

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.

Im confused about what exactly you are asking here? Which workbook has the security setting problems? What exactly are you trying to do via code that fails due to security/trust centre?

# ? Aug 29, 2014 19:35

Afterbirth Aftermath: Aug 29, 2002

Sorry, phone posting. The macro posted is the start of my workbook (destwb) macro. The source my workbook opens also has its own macros, but I don't need them. If I run my macro normally, it quits after the workbook.open line, with this new workbook open, but in design mode, even if I push enable macros when prompted. If I go line by line with step into (F8), it will open the source workbook just fine, and I can run my macro the rest of the way through.

Afterbirth Aftermath fucked around with this message at 20:28 on Aug 29, 2014

# ? Aug 29, 2014 19:55

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.

So basically you want the second wb to open not in design mode? Phone posting here so unable to check. I know trust setting stuff can be a pain. First thoughts are, is the target wb saved in design mode? Try resaving in the way you want it to open. Secondly I would try setting default opening settings of excel (may be required to be in the registry remeber to remove when done). Also if the path of the code isnt returning to your initial wb try having the new wb opening be the end of a sub and have the target call a sub in the master wb in on open or somewhere similar.

# ? Aug 29, 2014 20:45

khazar sansculotte: May 14, 2004

In reference to the question above I had a couple of weeks ago, I've found a process that leaves me with line feeds within a cell separating two accounts. Basically, if I can properly "split" a row of cells, each with a line feed, into two rows, I'll be golden.

I have the following code that takes care of this problem nicely:

code:

Dim rng As Range, cell As Range

For Each cell In rng
      celltext = cell.Text

      'Finds a line feed
      If InStr(1, celltext, vbLf) Then   

          'If the cell below is nonempty, insert a row
          If cell.Offset(1, 0).Text <> "" Then   
              cell.Offset(1).EntireRow.Insert
          End If

          'Put stuff after the line feed into the cell below
          cell.Offset(1, 0).Value = Right(celltext, InStr(celltext, vbLf) - 1)   

          'Keep only the stuff before the line feed in the original cell
          cell.Value = Left(celltext, InStr(celltext, vbLf) - 1)    

      End If
Next cell

Trouble is, it only works the way I want it to when there's just one line feed within a cell. There are a few rare instances where I need to handle two line feeds within a cell (i.e., split it into three rows). Now, I would expect the above code to put everything after the first line feed into the new cell (including the second line feed), and then for the "For Each cell In rng" part to later on pick up that new cell and split it up as it does with all the other cells with only one line feed in them, but that doesn't seem to be happening. Instead, cells get cut up rather weirdly, pieces go missing, etc... I can't really figure out what's happening. Is there a simple adjustment I can make to the code to make it do what I would expect it to do, or otherwise easily handle the case of multiple line feeds within a cell?

khazar sansculotte fucked around with this message at 21:41 on Sep 1, 2014

# ? Sep 1, 2014 21:38

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

Ronald McReagan posted:

In reference to the question above I had a couple of weeks ago, I've found a process that leaves me with line feeds within a cell separating two accounts. Basically, if I can properly "split" a row of cells, each with a line feed, into two rows, I'll be golden.

I have the following code that takes care of this problem nicely:
code:
Dim rng As Range, cell As Range

For Each cell In rng
      celltext = cell.Text

      'Finds a line feed
      If InStr(1, celltext, vbLf) Then   

          'If the cell below is nonempty, insert a row
          If cell.Offset(1, 0).Text <> "" Then   
              cell.Offset(1).EntireRow.Insert
          End If

          'Put stuff after the line feed into the cell below
          cell.Offset(1, 0).Value = Right(celltext, InStr(celltext, vbLf) - 1)   

          'Keep only the stuff before the line feed in the original cell
          cell.Value = Left(celltext, InStr(celltext, vbLf) - 1)    

      End If
Next cell
Trouble is, it only works the way I want it to when there's just one line feed within a cell. There are a few rare instances where I need to handle two line feeds within a cell (i.e., split it into three rows). Now, I would expect the above code to put everything after the first line feed into the new cell (including the second line feed), and then for the "For Each cell In rng" part to later on pick up that new cell and split it up as it does with all the other cells with only one line feed in them, but that doesn't seem to be happening. Instead, cells get cut up rather weirdly, pieces go missing, etc... I can't really figure out what's happening. Is there a simple adjustment I can make to the code to make it do what I would expect it to do, or otherwise easily handle the case of multiple line feeds within a cell?

Usually you want to avoid modifying a collection (adding/removing elements) while you are iterating over it. It's just confusing, but it's also because the list of elements that will be iterated over may only be evaluated once at the beginning, so even if you add members to the list inside the loop it will only iterate over the list as it was when you entered the loop. For example, if you had a "For i = 1 to N" loop and you modified N on the inside, it would still iterate up to the original N (compare with "While i <= N", where the condition is evaluated again each iteration, so it would go up to whatever N is at that point).

I'm not sure how the "for each" works in VBA, however (IIRC in VB.NET modifying the collection in a for each will throw an exception), but that may be to blame.

In any case, the key is probably to build up a second list as you go, not modify the original. Something like:

1. Iterate over your range as you are doing.
2. At each cell, use the "Split" function.
3. Add the result (an array with 1, 2, 3,... members depending on how many vbLf's you had) to a NEW list (on a new sheet, or the column next to the original one, whatever).
4. Once you are done, you can replace your starting range with this one.

# ? Sep 2, 2014 00:30

khazar sansculotte: May 14, 2004

As usual the solution was not nearly so interesting. I fixed it by changing

code:

cell.Offset(1, 0).Value = Right(celltext, InStr(celltext, vbLf) - 1)

code:

cell.Offset(1, 0).Value = Right(celltext, Len(celltext) - InStr(celltext, vbLf))

Previously, I was putting the first (say) 10 characters of a 40 character cell on one line and the last 10 characters on the other. This way it actually puts the first 10 on one line and the last 30 on the other.

So the code was producing the behavior I wanted in terms of including inserted cells as part of the "For Each" even after the loop began, I just wasn't telling it the right places to split the strings.

# ? Sep 2, 2014 03:30

Afterbirth Aftermath: Aug 29, 2002

Cast_No_Shadow posted:

So basically you want the second wb to open not in design mode? Phone posting here so unable to check. I know trust setting stuff can be a pain. First thoughts are, is the target wb saved in design mode? Try resaving in the way you want it to open. Secondly I would try setting default opening settings of excel (may be required to be in the registry remeber to remove when done). Also if the path of the code isnt returning to your initial wb try having the new wb opening be the end of a sub and have the target call a sub in the master wb in on open or somewhere similar.

After 4 wonderful days off I came back today to deal with this. Can someone tell me if the following is normal behavior? If I try to run the macro with an assigned hotkey (CTRL+SHIFT+R in this instance), I kept getting the design mode issue I described. When I created a "PUSH ME" button form control, it runs just fine. It works so I'm going with it, so I'm just curious at this point.

# ? Sep 3, 2014 18:22

khazar sansculotte: May 14, 2004

Is there any easy VBA way to split a cell with a delimiter into multiple cells on the same row, that also pushes any data currently residing immediately to the right of the original cell further to the right instead of overwriting it altogether? That is, turn

code:

[cat;dog;ferret] [car] [airplane]

into

code:

[cat] [dog] [ferret] [car] [airplane]

I tried setting up something inserting a bunch of columns and using Range.texttocolumns to split the cells by delimiter, but that gets to be really cumbersome really fast and doesn't work all that well. All the googling I've done seems to lead me to threads where people are doing much more complicated things, and I can't tease out what I need from them.

Edit: I got around this issue by moving cells around in such a way that I only had to do a TextToColumns thing once, which is good enough for my purposes right now. I'm still curious if there's a way to do what I outlined above though.

khazar sansculotte fucked around with this message at 00:58 on Sep 4, 2014

# ? Sep 3, 2014 23:41

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

I would use a combination of the Join function to add ";" between the cells and then the Split function to split it back up again (i.e. both the original delimiters in the cells and the new ones between the cells). Not very efficient but whatever.

# ? Sep 4, 2014 01:14

Nam Taf: Jun 25, 2005; I am Fat Man, hear me roar!

Ronald McReagan posted:

Is there any easy VBA way to split a cell with a delimiter into multiple cells on the same row, that also pushes any data currently residing immediately to the right of the original cell further to the right instead of overwriting it altogether? That is, turn
code:
[cat;dog;ferret] [car] [airplane]
into
code:
[cat] [dog] [ferret] [car] [airplane]
I tried setting up something inserting a bunch of columns and using Range.texttocolumns to split the cells by delimiter, but that gets to be really cumbersome really fast and doesn't work all that well. All the googling I've done seems to lead me to threads where people are doing much more complicated things, and I can't tease out what I need from them.

Edit: I got around this issue by moving cells around in such a way that I only had to do a TextToColumns thing once, which is good enough for my purposes right now. I'm still curious if there's a way to do what I outlined above though.

If the text in the cell is literally [cat;dog;ferret] in one cell, and [car] in another, and they always have square brackets and are always semicolon delimited, then I'd:
1) make an array of cells with the equation =MID(A1,2,LEN(A1)-2)&";" for each original cell. This gets it in the format of item1;item2;item3; and you can click and drag that to get that on everything.
2) Choose a column to the right of everything and do =D1&E1&F1&G1.... to join your big semicolon-delimited string together.
3) RTRIM to len(bigcolumn),1 to remove the trailing semicolon (probably not necessary)
4) text to columns it

That's the bruteforce way I'd have done it.

# ? Sep 4, 2014 11:36

Blowjob Overtime: Apr 6, 2008; Steeeeriiiiiiiiike twooooooo!

Hi Excel geniuses. I'm attempting to edit a macro with a dangerously weak understanding of VBA, and don't even know how to begin Google searching this question.

The goal is to have a macro that will allow the user to select multiple part numbers (presumably they are all listed in the same column) and run the macro to perform a VLookup on the selection, and insert an item description to the right of the selection. So far everything is good with the formatting and inserting, but when the formula copies in it puts in some parenthesis for reasons I don't understand. The randomly inserted parenthesis break the formula.

Here is the specific line that's giving me issues:
Selection.Formula = "=VLOOKUP(RC[-1],'F:\Staff\DrKennethNoisewater\Part Numbers\[PartNumberMasterList.xlsx]Sheet1'!A:B,2,0)"

When it puts the formula into the selection, it shows up as follows:
VLOOKUP(RC[-1],'F:\Staff\DrKennethNoisewater\Part Numbers\[PartNumberMasterList.xlsx]Sheet1'!A:(B),2,FALSE)

With the bold portion being the problem. Why does it insist that there should be parenthesis around that B, but leaves everything else alone?

Thanks.

# ? Sep 4, 2014 22:04

Wandering Orange: Sep 8, 2012

http://stackoverflow.com/questions/8164867/excel-macro-formula-adding-quotes-around-formular-causing-vlookup-to-not-work

TL;DR - You cannot mix RC and A1 style references in the same formula.

# ? Sep 4, 2014 22:23

Blowjob Overtime: Apr 6, 2008; Steeeeriiiiiiiiike twooooooo!

Wandering Orange posted:

http://stackoverflow.com/questions/8164867/excel-macro-formula-adding-quotes-around-formular-causing-vlookup-to-not-work

TL;DR - You cannot mix RC and A1 style references in the same formula.

So I guess the right Google search was "excel macro formula adding quotes around formular causing vlookup to not work". Thanks!

# ? Sep 4, 2014 23:22

Wandering Orange: Sep 8, 2012

Yeah I had to refine the search quite a few times before anything close turned up. Sometimes it is just dumb luck!

# ? Sep 5, 2014 00:30

khazar sansculotte: May 14, 2004

I'm on the home stretch of the thing I've been working on (on and off) for a couple of months. Here is what I hope is the last problem I'll need to solve:

Some of my spreadsheets show only an account (e.g., 5440), others show only its subaccounts (e.g., 5441, 5442, 5443, 5444). I want to take the sheets that only show subaccounts, and add up the values of the subaccounts, and then change its account number to the acccount. In other words, I want this:

code:

Account     Amount
5441        $100
5442        $300
5443        $500
5444        $700

to become this:

code:

Account     Amount
5440        $1600

My strategy is to do a .Find to get the smallest subaccount listed (the sheets may have any subset of the subaccounts listed), insert a row above it, put the account number in, and then add up all the rows of its subaccounts below it. But I'm stuck on step 1 of that. I haven't even started thinking about anything messy involved with adding up the rows. Anyway, here is the code I have:

code:

Dim DataRange As Range, rowfind As Range, newrow As Range

If DataRange.Find("5440", LookIn:=xlValues) Is Nothing Then  'test for main or sub account numbers
        rowfind = DataRange.Find("544?", LookIn:=xlValues)   
        If Not rowfind Is Nothing Then
            rowfind.EntireRow.Insert
            newrow = rowfind.Offset(-1)
            newrow.Cells(1, 1).Value = "5440"
        End If
    End If

When I step through it, apparently "rowfind" is never becoming anything other than "Nothing." Am I doing something wrong with the Find method? Is the ? wildcard inappropriate (really all I want to find is anything from 5441 up to 5449)? Should I try something totally different?

edit: New strategy. I've inserted the number for the main account, sorted the column increasing (so the subaccounts will definitely appear below it). I'm trying to use a Do While loop to add up what I need and delete the rows as I go, but I can't get that working either (again, the "base" variable simply does not seem to be taking a value; I tried xlFormulas instead of xlValues just for the heck of it, but it didn't seem to matter):

code:

        base = DataRange.Find(5440, LookIn:=xlFormulas)
        Do While base.Offset(1, 0).Value < 5450
            base.Offset(0, 1).Value = base.Offset(0, 1).Value + base.Offset(1, 1).Value
            base.Offset(1).EntireRow.Delete
        Loop

khazar sansculotte fucked around with this message at 02:25 on Sep 7, 2014

# ? Sep 6, 2014 23:40

Adbot: ADBOT LOVES YOU

# ? Apr 26, 2024 14:46

Nam Taf: Jun 25, 2005; I am Fat Man, hear me roar!

With way too little detail to understand the problem fully, could you look at using a combination of SUMPRODUCT, and INDEX/MATCH?

# ? Sep 7, 2014 09:05

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

«‹›82 »